生鲜配送ERP系统_升鲜宝生鲜配送供应链管理系统Mysql表结构数据字典的生成小工具V0.01

生鲜配送ERP系统_升鲜宝生鲜配送供应链管理系统Mysql表结构数据字典的生成小工具V0.01_SaaS全链路生鲜供应链管理系统_升鲜宝_15382353715

 

最近要交付升鲜宝生鲜配送供应链管理系统源代码给上海的客户，需要将蓝湖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.每个表格之间，需要间隙，解决显示拥挤。

 

简易Mysql数据库生存docx数据字典工具下载：

       

运行界面