导语

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

正文

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

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

工具的界面大体如下：

图1
图1

投影未脱机的数据库列表（使用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();
}

最终效果

图2
图2

任务完成，这里是源代码，晚安！