先看一张效果图:
如果您觉得您会了,请绕道...
说说怎么实现的
第一步:添加引用
Microsoft.Office.Interop.Word
第二步:说说精髓部分,并非完整代码,末尾 附上 源码
Microsoft.Office.Interop.Word.Application app = null;
Microsoft.Office.Interop.Word.Document doc = null;
object oMissing = System.Reflection.Missing.Value;
//**创建Word应用程序
app = new Microsoft.Office.Interop.Word.Application();
//**添加一个word文档
doc = app.Documents.Add();
//**输出大标题
app.Selection.Font.Bold = 500;
app.Selection.Font.Size = 16;
//**水平居中
app.Selection.Range.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;
//**垂直居中
app.Selection.ParagraphFormat.Alignment = Microsoft.Office.Interop.Word.WdParagraphAlignment.wdAlignParagraphCenter;
//**设置页边距
app.ActiveDocument.PageSetup.LeftMargin = app.CentimetersToPoints(float.Parse("1"));
app.ActiveDocument.PageSetup.RightMargin = app.CentimetersToPoints(float.Parse("1"));
//**可自己更改
app.Selection.Text = "数据库:LifeNote";
//**换行添加表格
object line = Microsoft.Office.Interop.Word.WdUnits.wdLine;
app.Selection.MoveDown(ref line, oMissing, oMissing);
//**换三次行
app.Selection.TypeParagraph();
app.Selection.TypeParagraph();
app.Selection.TypeParagraph();
Microsoft.Office.Interop.Word.Table table = null;
//**dtList 为 一个CheckedListBox 用于列出 对应数据库的所有表,此处是用于遍历选中的表
foreach (var item in dtList.CheckedItems)
{
#region 添加多个表格
//**此处获取对应表的字段属性
//**getTableInfoCmdText方法获取sql 语句
DataSet ds = SqlHelper.ExecuteDataset(conn, System.Data.CommandType.Text, string.Format(getTableInfoCmdText(item.ToString()), item.ToString()));
int rows = ds.Tables[0].Rows.Count + 3;
//**固定12列
int cols = 12;
//**创建表格
//table = app.Selection.Tables.Add(range, rows, cols, ref oMissing, ref oMissing);
table = doc.Tables.Add(app.Selection.Range, rows, cols, ref oMissing, ref oMissing);
//**表格边框样式
table.Borders.OutsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
table.Borders.InsideLineStyle = Microsoft.Office.Interop.Word.WdLineStyle.wdLineStyleSingle;
//**设置表格字体大小粗细
table.Range.Font.Size = 10;
table.Range.Font.Bold = 0;
//**设置表格标题
int rowIndex = 1;
table.Cell(rowIndex, 1).Range.Text = "表名";
table.Cell(rowIndex, 3).Range.Text = item.ToString() + "( )";
table.Rows[1].Range.Font.Bold = 2;
//**合并2个单元格
table.Cell(1, 1).Merge(table.Cell(1, 2));
//**合并10个单元格
table.Cell(1, 2).Merge(table.Cell(1, 11));
rowIndex++;
#region 表头
//**设置单元格背景色
//table.Rows[1].Shading.ForegroundPatternColor
table.Rows[1].Range.Shading.ForegroundPatternColor = Microsoft.Office.Interop.Word.WdColor.wdColorPaleBlue;
table.Rows[2].Range.Shading.ForegroundPatternColor = Microsoft.Office.Interop.Word.WdColor.wdColorPaleBlue;
table.Cell(rowIndex, 1).Range.Text = "字段名";
table.Cell(rowIndex, 2).Range.Text = "中文名";
table.Cell(rowIndex, 3).Range.Text = "类型";
table.Cell(rowIndex, 4).Range.Text = "长度";
table.Cell(rowIndex, 5).Range.Text = "占用位元组数";
table.Cell(rowIndex, 6).Range.Text = "主键";
table.Cell(rowIndex, 7).Range.Text = "外键";
table.Cell(rowIndex, 8).Range.Text = "唯一键";
table.Cell(rowIndex, 9).Range.Text = "标识";
table.Cell(rowIndex, 10).Range.Text = "小数位";
table.Cell(rowIndex, 11).Range.Text = "允许空";
table.Cell(rowIndex, 12).Range.Text = "默认值";
table.Rows[2].Range.Font.Bold = 2;
#endregion
rowIndex++;
//**ds.Tables[0] 指遍历到的表
foreach (DataRow dr in ds.Tables[0].Rows)
{
#region 行数据
table.Cell(rowIndex, 1).Range.Text = dr["field"].ToString();
//table.Cell(rowIndex, 1).Width = 60;
table.Cell(rowIndex, 2).Range.Text = dr["chins"].ToString();
table.Cell(rowIndex, 3).Range.Text = dr["types"].ToString();
//**此处是由于 字段长度为max 获取到的数据为-1 故转为max
table.Cell(rowIndex, 4).Range.Text = dr["length"].ToString().Equals("-1") ? "max" : dr["length"].ToString();
table.Cell(rowIndex, 5).Range.Text = dr["lengthdel"].ToString().Equals("-1") ? "max" : dr["lengthdel"].ToString();
table.Cell(rowIndex, 6).Range.Text = dr["pk"].ToString();
table.Cell(rowIndex, 7).Range.Text = dr["fk"].ToString();
table.Cell(rowIndex, 8).Range.Text = dr["uq"].ToString();
table.Cell(rowIndex, 9).Range.Text = dr["bs"].ToString();
table.Cell(rowIndex, 10).Range.Text = dr["decl"].ToString();
table.Cell(rowIndex, 11).Range.Text = dr["nuls"].ToString();
table.Cell(rowIndex, 12).Range.Text = dr["defuat"].ToString();
#endregion
rowIndex++;
}
table.Cell(rowIndex, 1).Range.Text = "备注:";
//**合并12个单元格
table.Cell(rowIndex, 1).Merge(table.Cell(rowIndex, 12));
//**不加此处容易出现第二个表格嵌入在第一个表格的第一个单元格内
//**防止多个表格出现重合情况
object WdStory = Microsoft.Office.Interop.Word.WdUnits.wdStory;
app.Selection.EndKey(ref WdStory, ref oMissing);
object count = 14;
object WdLine = Microsoft.Office.Interop.Word.WdUnits.wdLine;
app.Selection.MoveDown(ref WdLine, ref count, ref oMissing);
app.Selection.TypeParagraph();
}
//导出到文件
string newFile = "数据字典 LifeNote.doc";
//**此处暂未写死路径
string physicNewFile = @"C:\" + newFile;
//**创建成功
doc.SaveAs(physicNewFile,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
注意关闭
if (doc != null)
{
doc.Close();//关闭文档
}
if (app != null)
{
app.Quit();//退出应用程序
}
getTableInfoCmdText方法
/// <summary>
/// 获取指定表的结构信息
/// </summary>
private string getTableInfoCmdText(string tableName)
{
return string.Format(@"
SELECT
num = A.COLORDER,
field = A.NAME,
chins = ISNULL(G.[VALUE], ' ') ,
types = B.NAME,
length = COLUMNPROPERTY(A.ID,A.NAME, 'PRECISION '),
lengthdel = A.LENGTH,
pk = CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE= 'PK ' AND PARENT_OBJ=A.ID AND NAME IN (
SELECT NAME FROM SYSINDEXES WHERE INDID IN(
SELECT INDID FROM SYSINDEXKEYS WHERE ID = A.ID AND COLID=A.COLID))) THEN '是' ELSE ' ' END, --主键结束
fk = (case when(select count(*) from (select OBJECT_NAME(f.fkeyid) as fname, col.name, f.constid as temp from syscolumns col,sysforeignkeys f where f.fkeyid=col.id and f.fkey=col.colid) ft where ft.fname = d.name and ft.name=a.name)>0 then '是' else '' end), --外键结束
uq =(case when(select count(COLUMN_NAME) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME in
(SELECT name FROM sys.key_constraints where object_name(parent_object_id)=d.name AND type='UQ') and COLUMN_NAME=a.name)>0 then '是' else '' end) ,--唯一键结束
bs = CASE WHEN COLUMNPROPERTY( A.ID,A.NAME, 'ISIDENTITY ')=1 THEN '是'ELSE ' ' END,
decl = ISNULL(COLUMNPROPERTY(A.ID,A.NAME, 'SCALE '),0),
nuls = CASE WHEN A.ISNULLABLE=1 THEN '允许'ELSE ' ' END,
defuat = ISNULL(E.TEXT, ' ')
FROM SYSCOLUMNS A
LEFT JOIN SYSTYPES B
ON A.XUSERTYPE=B.XUSERTYPE
INNER JOIN SYSOBJECTS D
ON A.ID=D.ID AND D.XTYPE= 'U ' AND D.NAME <> 'DTPROPERTIES '
LEFT JOIN SYSCOMMENTS E
ON A.CDEFAULT=E.ID
LEFT JOIN sys.extended_properties G
ON A.ID=G.major_id AND A.COLID=G.minor_id
LEFT JOIN sys.extended_properties F
ON D.ID=F.major_id AND F.minor_id=0
WHERE D.NAME='{0}'
ORDER BY A.ID,A.COLORDER
", tableName);
}
最后,谢谢您耐心的阅读,
此处附上源码 数据字典生成器