你的位置:首页 > ASP.net教程

[ASP.net教程]使用OpenXml实现生成数据字典文档(beta)


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

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

  工具的界面大体如下:

  

  需要完成的任务:

  1. 投影未脱机的数据库列表(使用T-SQL)
  2. 投影当前数据库的表列表(使用T-SQL)
  3. 投影某表的字段信息列表(使用T-SQL)
  4. 使用Open
  5. 使用Open,替换书签
  6. 使用Open添加表格,完成单张表的数据字典docx文档
  7. 使用Open合并成到总文档中

一、投影未脱机的数据库列表(使用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;SELECTa.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.nameEND ) 类型 ,( 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 = 0WHERE  b.name IS NOT NULLand d.name='Account'ORDER BY a.id ,    a.colorder;

四、使用Open

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("数据字典"))); }

五、使用Open,替换书签

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(Openstring, 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;}

六、使用Open添加表格,完成单张表的数据字典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)));

七、使用Open

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();}

任务完成,这里是源代码,晚安!