转载

使用OpenXml实现生成数据字典文档(beta)

最近项目在走验收流程，之前没有仔细看 SOW 文档，发现需要补好多份文档，其中就有数据字典，项目组不愿意花时间太多的时间弄这些文档，也不希望以后还要重复劳动力，最终决定做一个工具，方便自己生成数据字典文档。

这里只是分享一下开发的思路和经历（如果发现有问题，请及时告诉我，我会即时纠正，以免误导他人，欢迎大家多多指点）

工具的界面大体如下：

需要完成的任务：

投影未脱机的数据库列表（使用 T-SQL ）
投影当前数据库的表列表（使用 T-SQL ）
投影某表的字段信息列表（使用 T-SQL ）
使用OpenXml新建总文档
使用 OpenXml开始生成单张表的数据字典，替换书签
使用 OpenXml 添加表格，完成单张表的数据字典docx文档
使用 OpenXml将当前文档合并成到总文档中

一、投影未脱机的数据库列表（使用 T-SQL ）

SELECT Name FROM Master..SysDatabases WHERE STATUS = 65536 ORDER BY Name

二、投影当前数据库的表列表（使用 T-SQL ）

USE TestDB;SELECT Name FROM SysObjects Where XType='U' ORDER BY Name DESC

三、投影某表的字段信息列表（使用 T-SQL ）

USE TestDB; SELECT a.name 字段名 , ( CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END ) 标识 , ( CASE WHEN ( SELECT COUNT(*)    FROM   sysobjects    WHERE  ( name IN (       SELECT name       FROM   sysindexes       WHERE  ( id = a.id )         AND ( indid IN (           SELECT indid           FROM   sysindexkeys           WHERE  ( id = a.id )             AND ( colid IN (              SELECT              colid              FROM              syscolumns              WHERE              ( id = a.id )              AND ( name = a.name ) ) ) ) ) ) )      AND ( xtype = 'PK' )   ) > 0 THEN '√'  ELSE '' END ) 主键 , ( CASE b.name  WHEN 'varchar' THEN b.name + '(' + cast(a.length as varchar(8)) + ')'  WHEN 'nvarchar' THEN b.name + '(' + cast(a.length as varchar(8)) + ')'  ELSE b.name END ) 类型 , ( CASE WHEN a.isnullable = 1 THEN '√' ELSE ''END ) 允许空 , ( CASE a.name WHEN 'Id' THEN 'Id'  WHEN 'AddTime' THEN '添加时间'  WHEN 'UpdateTime' THEN '修改时间'    WHEN 'IsValid' THEN '删除标记'    WHEN 'AddIp' THEN '添加IP地址'        WHEN 'UpdateIp' THEN '修改IP地址'      WHEN 'State' THEN '状态'      WHEN 'PicUrl' THEN '图片地址'       WHEN 'Image' THEN '图片地址'      WHEN 'Description' THEN '介绍'      WHEN 'Title' THEN '标题'      WHEN 'Note' THEN '备注'     WHEN 'Type' THEN '类型'     WHEN 'Content' THEN '内容'   END ) 字段中文名, ISNULL(e.text, '') 默认值, '' as 备注 FROM syscolumns a   LEFT JOIN systypes b ON a.xtype = 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.class               AND f.minor_id = 0 WHERE   b.name IS NOT NULL and d.name='Account' ORDER BY a.id ,   a.colorder;

四、使用OpenXml新建总文档

tmplFilePath是临时存放各个表数据字典的文件夹

string path = string.Format(@"{0}{1}.docx", tmplFilePath, DateTime.Now.Ticks.ToString()); using (WordprocessingDocument doc = WordprocessingDocument.Create(path, WordprocessingDocumentType.Document)) {  MainDocumentPart mainPart = doc.AddMainDocumentPart();  mainPart.Document = new Document();  Body body = mainPart.Document.AppendChild(new Body());  Paragraph p = mainPart.Document.Body.AppendChild(new Paragraph());  p.AppendChild(new Run(new Text("数据字典")));   }

五、使用 OpenXml开始生成单张表的数据字典，替换书签

tmplFileName是单张表的数据字典文档路径

using (WordprocessingDocument tmplDoc = WordprocessingDocument.Open(tmplFileName, true)) {  //修改文档类型 (dotx->docx)  tmplDoc.ChangeDocumentType(WordprocessingDocumentType.Document);  var bookMarks = FindBookmarks(tmplDoc.MainDocumentPart.Document);  //替换书签  foreach (var end in bookMarks)  {   if (end.Key == "TableName")   {    var textElement = new Text(tableName);    var runElement = new Run(textElement);    end.Value.InsertAfterSelf(runElement);   }  } }

下面是FindBookmarks方法

private Dictionary<string, BookmarkEnd> FindBookmarks(OpenXmlElement documentPart, Dictionary<string, BookmarkEnd> results = null, Dictionary<string, string> unmatched = null) {  results = results ?? new Dictionary<string, BookmarkEnd>();  unmatched = unmatched ?? new Dictionary<string, string>();  foreach (var child in documentPart.Elements())  {   if (child is BookmarkStart)   {    var bStart = child as BookmarkStart;    unmatched.Add(bStart.Id, bStart.Name);   }   if (child is BookmarkEnd)   {    var bEnd = child as BookmarkEnd;    foreach (var orphanName in unmatched)    {     if (bEnd.Id == orphanName.Key)      results.Add(orphanName.Value, bEnd);    }   }   FindBookmarks(child, results, unmatched);  }  return results; }

六、使用 OpenXml 添加表格，完成单张表的数据字典docx文档

//设置表格属性 TableProperties tblProp = new TableProperties(  new TableBorders(  new TopBorder() { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 1 },  new BottomBorder() { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 1 },  new LeftBorder() { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 1 },  new RightBorder() { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 1 },  new InsideHorizontalBorder() { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 1 },  new InsideVerticalBorder() { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 1 }  ) ); Table table = new Table(); table.Append(tblProp); //添加表头 TableRow headerRow = new TableRow(); foreach (DataColumn column in dataBaseTable.Columns) {  TableCell cell = new TableCell();  cell.Append(new Paragraph(new Run(new Text(column.ColumnName))));  headerRow.Append(cell); } table.Append(headerRow); //添加数据 foreach (DataRow row in dataBaseTable.Rows) {  TableRow tableRow = new TableRow();  foreach (object strCell in row.ItemArray)  {   TableCell cell = new TableCell();   cell.Append(new Paragraph(new Run(new Text(strCell.ToString()))));   tableRow.Append(cell);  }  table.Append(tableRow); } //添加表格 tmplDoc.MainDocumentPart.Document.Body.Append(new Paragraph(new Run(table)));

七、使用OpenXml将当前文档合并到总文档中

path是总文档的路径

tmplFileName是当前文档的路径

//将当前word和总文档的合并 using (WordprocessingDocument template = WordprocessingDocument.Open(path, true)) {  MainDocumentPart mainPart = template.MainDocumentPart;  string altChunkId = tableName;  AlternativeFormatImportPart chunk =  mainPart.AddAlternativeFormatImportPart(  AlternativeFormatImportPartType.WordprocessingML, altChunkId);  using (FileStream fileStream = File.Open(tmplFileName, FileMode.Open))   chunk.FeedData(fileStream);  AltChunk altChunk = new AltChunk();  altChunk.Id = altChunkId;  mainPart.Document   .Body   .InsertAfter(altChunk, mainPart.Document.Body   .Elements<Paragraph>().Last());  mainPart.Document.Save(); }