升鲜宝生鲜配送供应链管理系统Mysql表结构数据字典的生成小工具V0.01
最近最近要交付升鲜宝生鲜配送供应链管理系统源代码给上海的客户，需要将蓝湖UI设计图及数据字典交接给别人。在网上找了半天没有找到合适的根据Mysql生成Word数据字典，自己就写了几行代码，记录一下.后面可能会继续改造。主要的代码如下：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using static System.Net.Mime.MediaTypeNames;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.Tab;
using System.Xml.Linq;
using MySql.Data.MySqlClient;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using Body = DocumentFormat.OpenXml.Wordprocessing.Body;
using Text = DocumentFormat.OpenXml.Wordprocessing.Text;
using DocumentFormat.OpenXml;
namespace DotNet.WinForm
{
public partial class FrmCreateDBHtml : Form
{
public FrmCreateDBHtml()
{
InitializeComponent();
}
private void btnSave_Click(object sender, EventArgs e)
{
string connectionString = $"server={txtServer.Text};user={txtUser.Text};password={txtPassword.Text};database={txtDatabase.Text};";
string wordFilePath = "升鲜宝数据字典.docx";
try
{
GenerateDataDictionary(connectionString, wordFilePath);
MessageBox.Show("Data dictionary has been generated successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show($"An error occurred: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void BtnSave_Click(object sender, EventArgs e)
{
string connectionString = $"server={txtServer.Text};user={txtUser.Text};password={txtPassword.Text};database={txtDatabase.Text};";
string wordFilePath = "DataDictionary.docx";
try
{
GenerateDataDictionary(connectionString, wordFilePath);
MessageBox.Show("Data dictionary has been generated successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show($"An error occurred: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
static void GenerateDataDictionary(string connectionString, string wordFilePath)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
DataTable tables = connection.GetSchema("Tables");
using (WordprocessingDocument wordDocument = WordprocessingDocument.Create(wordFilePath, DocumentFormat.OpenXml.WordprocessingDocumentType.Document))
{
MainDocumentPart mainPart = wordDocument.AddMainDocumentPart();
mainPart.Document = new Document();
Body body = new Body();
foreach (DataRow row in tables.Rows)
{
string tableName = row["TABLE_NAME"].ToString();
string tableComment = "";
// Get table comment
using (MySqlConnection commentConnection = new MySqlConnection(connectionString))
{
commentConnection.Open();
string commentQuery = $"SELECT TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{connection.Database}' AND TABLE_NAME = '{tableName}';";
MySqlCommand commentCommand = new MySqlCommand(commentQuery, commentConnection);
tableComment = commentCommand.ExecuteScalar()?.ToString() ?? "";
}
Paragraph tableTitle = new Paragraph(new Run(new Text($"Table: {tableName} - {tableComment}")));
body.Append(tableTitle);
// Add a gap between tables
body.Append(new Paragraph(new Run(new Text(" "))));
string query = $"DESCRIBE {tableName}";
MySqlCommand command = new MySqlCommand(query, connection);
using (MySqlDataReader reader = command.ExecuteReader())
{
Table wordTable = new Table();
// Set table properties for A4 page size and fixed width
TableProperties tblProperties = new TableProperties(
new TableWidth { Type = TableWidthUnitValues.Pct, Width = "5000" },
new TableBorders(
new TopBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 6 },
new BottomBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 6 },
new LeftBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 6 },
new RightBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 6 },
new InsideHorizontalBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 6 },
new InsideVerticalBorder { Val = new EnumValue<BorderValues>(BorderValues.Single), Size = 6 }
));
wordTable.AppendChild(tblProperties);
// Add table headers with bold text
TableRow headerRow = new TableRow();
headerRow.Append(CreateTableCell("列名", "1500", true)); // 150 px
headerRow.Append(CreateTableCell("字段类型", "1200", true)); // 120 px
headerRow.Append(CreateTableCell("是否为空", "800", true)); // 80 px
headerRow.Append(CreateTableCell("主键", "500", true)); // 50 px
headerRow.Append(CreateTableCell("说明", "3000", true)); // Remaining width
wordTable.Append(headerRow);
// Add table rows
while (reader.Read())
{
TableRow dataRow = new TableRow();
dataRow.Append(CreateTableCell(reader["Field"].ToString(), "1500"));
dataRow.Append(CreateTableCell(reader["Type"].ToString(), "1200"));
dataRow.Append(CreateTableCell(reader["Null"].ToString(), "800"));
dataRow.Append(CreateTableCell(reader["Key"].ToString(), "500"));
// Get column comment
string comment = "";
using (MySqlConnection commentConnection = new MySqlConnection(connectionString))
{
commentConnection.Open();
string commentQuery = $"SELECT COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '{connection.Database}' AND TABLE_NAME = '{tableName}' AND COLUMN_NAME = '{reader["Field"].ToString()}';";
MySqlCommand commentCommand = new MySqlCommand(commentQuery, commentConnection);
comment = commentCommand.ExecuteScalar()?.ToString() ?? "";
}
dataRow.Append(CreateTableCell(comment, "3000"));
wordTable.Append(dataRow);
}
body.Append(wordTable);
}
}
mainPart.Document.Append(body);
}
}
}
static TableCell CreateTableCell(string text, string width, bool isBold = false)
{
TableCell tableCell = new TableCell();
TableCellProperties tableCellProperties = new TableCellProperties(
new TableCellWidth { Type = TableWidthUnitValues.Pct, Width = width }
);
tableCell.Append(tableCellProperties);
Run run = new Run(new Text(text));
if (isBold)
{
run.RunProperties = new RunProperties(new Bold());
}
Paragraph paragraph = new Paragraph(run);
tableCell.Append(paragraph);
return tableCell;
}
}
}
生成的数据字典效果如下：
遇到的问题如下：
1.生成的时候，需要注意列宽，使之适应竖版A4的显示
2.表名后面，需要加上中文的显示。
3.每个表格之间，需要间隙，解决显示拥挤。
