public ReturnMessage GetInfo(string cnnString) { ReturnMessage retMsg = new ReturnMessage(string.Empty, true); dtInfo.Rows.Clear(); string strQry = @"SELECT TOP (100) PERCENT CASE WHEN a.colorder = 1 THEN d .name ELSE '' END AS 表名, CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明, a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS (SELECT 1 FROM dbo.sysindexes si INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN dbo.sysobjects so ON so.name = so.name AND so.xtype = 'PK' WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键, b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '') AS 默认值, ISNULL(g.value, '') AS 字段说明, d.crdate AS 创建时间, CASE WHEN a.colorder = 1 THEN d .refdate ELSE NULL END AS 更改时间 FROM dbo.syscolumns AS a LEFT OUTER JOIN dbo.systypes AS b ON a.xtype = b.xusertype INNER JOIN dbo.sysobjects AS d ON a.id = d.id AND d.xtype = 'U' AND d.status >= 0 LEFT OUTER JOIN dbo.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN sys.extended_properties AS g ON a.id = g.major_id AND a.colid = g.minor_id LEFT OUTER JOIN sys.extended_properties AS f ON d.id = f.major_id AND f.minor_id = 0 ORDER BY d.name, 字段序号"; try { SqlDataAdapter da = new SqlDataAdapter(strQry, cnnString); da.Fill(dtInfo); dgvData.DataSource = dtInfo; return(retMsg); } catch (Exception ex) { retMsg.isSuccess = false; retMsg.Messages = ex.Message; return(retMsg); } }
private void btnBulid_Click(object sender, EventArgs e) { Cursor currCursor = this.Cursor; this.Cursor = Cursors.WaitCursor; ReturnMessage retMsg = CheckCnnString(txtCnnString.Text.Trim()); if (!retMsg.isSuccess) { MessageBoxEx.Show("数据库连接字符串错误,信息为:" + retMsg.Messages, "", MessageBoxButtons.OK); this.Cursor = currCursor; return; } retMsg = GetInfo(txtCnnString.Text.Trim()); if (!retMsg.isSuccess) { MessageBoxEx.Show("读取数据库表结构错误,信息为:" + retMsg.Messages, "", MessageBoxButtons.OK); this.Cursor = currCursor; return; } retMsg = WriteDoc(); if (retMsg.isSuccess) { MessageBoxEx.Show("文档生成成功!\n请在程序根目录查找文档!", "", MessageBoxButtons.OK); } else { MessageBoxEx.Show("文档生成失败,信息为:" + retMsg.Messages, "", MessageBoxButtons.OK); } this.Cursor = currCursor; }
private ReturnMessage WriteDoc() { ReturnMessage retMsg = new ReturnMessage(string.Empty, true); FileStream fs = null; try { XWPFDocument doc = new XWPFDocument(); XWPFTable table = null; int index = 1; foreach (DataRow dr in dtInfo.Rows) { if (dr["表名"] != DBNull.Value && !string.IsNullOrEmpty(dr["表名"].ToString())) { //表名,以段落表示 CT_P ctp = doc.Document.body.AddNewP(); //XWPFParagraph p = doc.CreateParagraph(); XWPFParagraph p = new XWPFParagraph(ctp, doc); XWPFRun r = p.CreateRun(); //设置字体 r.GetCTR().AddNewRPr().AddNewRFonts().ascii = "宋体"; r.GetCTR().AddNewRPr().AddNewRFonts().eastAsia = "宋体"; r.GetCTR().AddNewRPr().AddNewRFonts().hint = ST_Hint.eastAsia; r.GetCTR().AddNewRPr().AddNewSz().val = (ulong)32;//3号字体; r.GetCTR().AddNewRPr().AddNewSzCs().val = (ulong)32; //设置行间距 //单倍为默认值(240twip)不需设置,1.5倍=240X1.5=360twip,2倍=240X2=480twip ctp.AddNewPPr().AddNewSpacing().line = "720"; //ctp.AddNewPPr().AddNewSpacing().lineRule = ST_LineSpacingRule.exact; //设置段落文本 r.SetText(index.ToString() + "." + dr["表名"].ToString()); //表结构,以表格显示 CT_Tbl m_CTTbl = doc.Document.body.AddNewTbl(); table = doc.CreateTable(1, 9); //标题行(固定) //列宽 CT_TcPr mPr = table.GetRow(0).GetCell(0).GetCTTc().AddNewTcPr(); mPr.tcW = new CT_TblWidth(); mPr.tcW.w = "900"; mPr.tcW.type = ST_TblWidth.dxa; mPr = table.GetRow(0).GetCell(1).GetCTTc().AddNewTcPr(); mPr.tcW = new CT_TblWidth(); mPr.tcW.w = "1500"; mPr.tcW.type = ST_TblWidth.dxa; mPr = table.GetRow(0).GetCell(2).GetCTTc().AddNewTcPr(); mPr.tcW = new CT_TblWidth(); mPr.tcW.w = "500"; mPr.tcW.type = ST_TblWidth.dxa; mPr = table.GetRow(0).GetCell(3).GetCTTc().AddNewTcPr(); mPr.tcW = new CT_TblWidth(); mPr.tcW.w = "1000"; mPr.tcW.type = ST_TblWidth.dxa; mPr = table.GetRow(0).GetCell(4).GetCTTc().AddNewTcPr(); mPr.tcW = new CT_TblWidth(); mPr.tcW.w = "500"; mPr.tcW.type = ST_TblWidth.dxa; mPr = table.GetRow(0).GetCell(6).GetCTTc().AddNewTcPr(); mPr.tcW = new CT_TblWidth(); mPr.tcW.w = "900"; mPr.tcW.type = ST_TblWidth.dxa; mPr = table.GetRow(0).GetCell(7).GetCTTc().AddNewTcPr(); mPr.tcW = new CT_TblWidth(); mPr.tcW.w = "800"; mPr.tcW.type = ST_TblWidth.dxa; mPr = table.GetRow(0).GetCell(8).GetCTTc().AddNewTcPr(); mPr.tcW = new CT_TblWidth(); mPr.tcW.w = "1500"; mPr.tcW.type = ST_TblWidth.dxa; //填充文字 table.GetRow(0).GetCell(0).SetText("字段序号"); table.GetRow(0).GetCell(1).SetText("字段名"); table.GetRow(0).GetCell(2).SetText("主键"); table.GetRow(0).GetCell(3).SetText("类型"); table.GetRow(0).GetCell(4).SetText("长度"); table.GetRow(0).GetCell(5).SetText("精度"); table.GetRow(0).GetCell(6).SetText("小数位数"); table.GetRow(0).GetCell(7).SetText("允许空"); table.GetRow(0).GetCell(8).SetText("字段说明"); //内容行 XWPFTableRow row = table.CreateRow(); row.GetCell(0).SetText(dr["字段序号"].ToString()); row.GetCell(1).SetText(dr["字段名"].ToString()); row.GetCell(2).SetText(dr["主键"].ToString()); row.GetCell(3).SetText(dr["类型"].ToString()); row.GetCell(4).SetText(dr["长度"].ToString()); row.GetCell(5).SetText(dr["精度"].ToString()); row.GetCell(6).SetText(dr["小数位数"].ToString()); row.GetCell(7).SetText(dr["允许空"].ToString()); row.GetCell(8).SetText(""); // index++; } else { if (table != null) { //内容行 XWPFTableRow row = table.CreateRow(); row.GetCell(0).SetText(dr["字段序号"].ToString()); row.GetCell(1).SetText(dr["字段名"].ToString()); row.GetCell(2).SetText(dr["主键"].ToString()); row.GetCell(3).SetText(dr["类型"].ToString()); row.GetCell(4).SetText(dr["长度"].ToString()); row.GetCell(5).SetText(dr["精度"].ToString()); row.GetCell(6).SetText(dr["小数位数"].ToString()); row.GetCell(7).SetText(dr["允许空"].ToString()); row.GetCell(8).SetText(""); } } } //输出保存 string docAllPath = Application.StartupPath + "\\SqlDBDicFile.docx"; if (File.Exists(docAllPath)) { File.Delete(docAllPath); } fs = File.OpenWrite(docAllPath); doc.Write(fs); doc.Close(); return(retMsg); } catch (Exception ex) { retMsg.isSuccess = false; retMsg.Messages = ex.Message; return(retMsg); } finally { fs.Close(); fs.Dispose(); } }