Use OpenXml to generate data dictionary documentation (beta) and openxmlbeta

Use OpenXml to generate data dictionary documentation (beta) and openxmlbeta

Recently, the project has gone through the acceptance process. I did not carefully read the SOW documents and found that many documents need to be supplemented, including data dictionaries. The project team is reluctant to spend too much time on these documents, I do not want to repeat the labor force in the future. I finally decided to create a tool to generate data dictionary documents by myself.

Here, I will share my thoughts and experiences on development. (If any problem is found, please let me know in time and I will correct it in real time to avoid misleading others. Thank you for your advice)

The tool interface is as follows:

　　

Tasks to be completed:

1. List of projected databases not offline (using T-SQL)

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

2. Project the list of tables in the current database (using T-SQL)

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

3. Project A table's Field Information List (using T-SQL)

USE TestDB; SELECTa. name field name, (case when columnproperty (. id,. name, 'isidentity ') = 1 then' √ 'else' END) identifier, (case when (select count (*) FROM sysobjects WHERE (name IN (SELECT name FROM sysindexes WHERE (id =. id) AND (indid IN (SELECT indid FROM sysindexkeys WHERE (id =. id) AND (colid IN (SELECT colid FROM syscolumns WHERE (id =. id) AND (name =. name) AND (xtype = 'pk')> 0 then' √ 'else' END) primary key, (CASE B. name WHEN 'varchar 'THEN B. name + '(' + cast (. length as varchar (8) + ') 'when' nvarchar 'then B. name + '(' + cast (. length as varchar (8) + ') 'else B. nameEND) type, (case when. isnullable = 1 then' √ 'else' END) Allow null (CASE. name WHEN 'id' THEN 'id' WHEN 'addtime' then' add time 'when' UpdateTime 'then' modify time 'when' isvalid' then' Delete tag 'when' AddIp' then' add IP address 'when' updateip' then' modify IP address 'when' State 'then' status 'when' picurl' then' Image address 'when' Image 'then' Image address' WHEN 'description' introduces 'when' Title 'then' Title 'when' Note 'then' remarks 'when' Type 'when' Content 'then' Content' END) the Chinese name of the field, ISNULL (e. text, '') default value,'' as remarks FROM syscolumns a left join policypes B ON. xtype = B. xusertype inner join sysobjects d ON. id = d. id AND d. xtype = 'U' AND d. name <> 'dtproperties' left join syscomments e ON. cdefault = e. id left join sys. extended_properties g ON. id = g. major_id AND. 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 NULLand d. name = 'account' order by. id,. colorder;

IV,Use OpenXml to create a new general document

TmplFilePath is a folder that temporarily stores data dictionaries for each table.

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

5. Use OpenXml to generate a data dictionary for a single table and replace bookmarks

TmplFileName is the data dictionary document path of a single table.

Using (WordprocessingDocument tmplDoc = WordprocessingDocument. open (tmplFileName, true) {// modify the document type (dotx-> docx) tmplDoc. changeDocumentType (WordprocessingDocumentType. document); var bookMarks = FindBookmarks (tmplDoc. mainDocumentPart. document); // Replace the bookMarks foreach (var end in bookMarks) {if (end. key = "TableName") {var textElement = new Text (tableName); var runElement = new Run (textElement); end. value. insertAfterSelf (runElement );}}}

The FindBookmarks method is as follows:

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

VI,Use OpenXmlAdd a table to complete the data dictionary docx document for a single table

// Set table properties 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); // Add the header 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); // Add data 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);} // Add Table tmplDoc. mainDocumentPart. document. body. append (new Paragraph (new Run (table )));

7. Use OpenXml to merge the current document into the total document

Path is the path of the total document

TmplFileName is the path of the current document

// Merge the current word and the total document 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 ();}

Partial effects:

Task completed. Here is the source code. Good night!