代码生成Mysql数据库表设计到Word表格
1.引用 <PackageReference Include="NPOI" Version="2.5.4" />，操作word。
引用<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="2.1.4" /> 操作MySql
引用<PackageReference Include="Pomelo.EntityFrameworkCore.MySql.Design" Version="1.1.2" />
2.表结构导入到word表格
using Microsoft.AspNetCore.Hosting; using NPOI.OpenXmlFormats.Wordprocessing; using NPOI.XWPF.UserModel; using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Text; using static NPOI.XWPF.UserModel.XWPFTableCell; namespace CQJSC.DemoAPI.Application.TableDesign { public class TableDesignAppService: DemoAPIAppServiceBase { private readonly IHostingEnvironment _hostingEnvironment; public TableDesignAppService(IHostingEnvironment env) { _hostingEnvironment = env; } public string ScheduleOne() { //创建生成word文档 string fileName = "数据库表设计.doc"; var path = Path.Combine(_hostingEnvironment.WebRootPath, "doc"); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } XWPFDocument doc = new XWPFDocument(); var tableNameSql = $"select table_name from information_schema.tables where table_schema = 'WaterConservancy'"; DataTable tableNameResult = DBComm_MySQL.ExecuteDataTable(tableNameSql); for (int i = 0; i < tableNameResult.Rows.Count; i++) { var sql = ""; var tableName = tableNameResult.Rows[i]["table_name"].ToString(); sql = $"set @序号=0;SELECT @序号:= @序号 + 1 as 序号, COLUMN_NAME 列名,COLUMN_TYPE 数据类型,DATA_TYPE 字段类型,CHARACTER_MAXIMUM_LENGTH 长度,IS_NULLABLE 是否为空,COLUMN_DEFAULT 默认值,COLUMN_COMMENT 备注 FROM INFORMATION_SCHEMA.COLUMNS where table_schema = 'WaterConservancy' AND table_name = '{tableName}'"; var tableDesignInfoList = new List<TableDesignInfo>(); DataTable rainResult = DBComm_MySQL.ExecuteDataTable(sql); for (int j = 0; j < rainResult.Rows.Count; j++) { var tableDesignInfo = new TableDesignInfo { SerialNumber = rainResult.Rows[j]["序号"].ToString(), Listing = rainResult.Rows[j]["列名"].ToString(), DataType = rainResult.Rows[j]["数据类型"].ToString(), Fieldtype = rainResult.Rows[j]["字段类型"].ToString(), Length = rainResult.Rows[j]["长度"].ToString(), IsNull = rainResult.Rows[j]["是否为空"].ToString(), DefaultValue = rainResult.Rows[j]["默认值"].ToString(), Remark = rainResult.Rows[j]["备注"].ToString(), }; tableDesignInfoList.Add(tableDesignInfo); } RegionMaxRain(doc, tableDesignInfoList, tableName); } using (FileStream fs = new FileStream(Path.Combine(path, fileName), FileMode.OpenOrCreate, FileAccess.Write)) { doc.Write(fs); } return fileName; } private void RegionMaxRain(XWPFDocument doc, List<TableDesignInfo> rainfallStatisticsDtoList,string tableName) { XWPFParagraph gp1 = doc.CreateParagraph(); gp1.Alignment = ParagraphAlignment.CENTER;//水平居中 XWPFRun gr1 = gp1.CreateRun(); gr1.SetText(""); gr1.AddCarriageReturn(); //gr1.AddCarriageReturn(); //gr1.AddCarriageReturn(); // 添加段落 XWPFParagraph gp = doc.CreateParagraph(); gp.Alignment = ParagraphAlignment.LEFT;//水平居中 XWPFRun gr = gp.CreateRun(); gr.GetCTR().AddNewRPr().AddNewRFonts().ascii = "黑体"; gr.GetCTR().AddNewRPr().AddNewRFonts().eastAsia = "黑体"; gr.GetCTR().AddNewRPr().AddNewRFonts().hint = ST_Hint.eastAsia; gr.GetCTR().AddNewRPr().AddNewSz().val = (ulong)24;//2号字体 gr.GetCTR().AddNewRPr().AddNewSzCs().val = (ulong)34; gr.GetCTR().AddNewRPr().AddNewB().val = true; //加粗 //gr.GetCTR().AddNewRPr().AddNewColor().val = "red";//字体颜色 gr.SetText($"{tableName}"); gr.AddCarriageReturn(); //添加表格 XWPFTable table = doc.CreateTable(rainfallStatisticsDtoList.Count + 1, 8);//创建一行4列表 table.Width = 5000; for (var c = 0; c < 8; c++) { for (var w = 0; w < table.Rows.Count; w++) { table.GetRow(w).GetCell(c).SetVerticalAlignment(XWPFVertAlign.CENTER); } } table.GetRow(0).GetCell(0).SetParagraph(SetTableParagraphInstanceSetting(doc, table, "序号", ParagraphAlignment.CENTER, 24, true)); table.GetRow(0).GetCell(1).SetParagraph(SetTableParagraphInstanceSetting(doc, table, "列名", ParagraphAlignment.CENTER, 24, true)); table.GetRow(0).GetCell(2).SetParagraph(SetTableParagraphInstanceSetting(doc, table, "数据类型", ParagraphAlignment.CENTER, 24, true)); table.GetRow(0).GetCell(3).SetParagraph(SetTableParagraphInstanceSetting(doc, table, "字段类型", ParagraphAlignment.CENTER, 24, true)); table.GetRow(0).GetCell(4).SetParagraph(SetTableParagraphInstanceSetting(doc, table, "长度", ParagraphAlignment.CENTER, 24, true)); table.GetRow(0).GetCell(5).SetParagraph(SetTableParagraphInstanceSetting(doc, table, "是否为空", ParagraphAlignment.CENTER, 24, true)); table.GetRow(0).GetCell(6).SetParagraph(SetTableParagraphInstanceSetting(doc, table, "默认值", ParagraphAlignment.CENTER, 24, true)); table.GetRow(0).GetCell(7).SetParagraph(SetTableParagraphInstanceSetting(doc, table, "备注", ParagraphAlignment.CENTER, 24, true)); //MergeCellsCustom(table, 0, 0, 0, 1); //MergeCellsCustom(table, 1, 1, 0, 1); //MergeCellsCustom(table, 2, 2, 0, 1); //MergeCellsCustom(table, 3, 3, 0, 1); //MergeCellsCustom(table, 4, 4, 0, 1); //MergeCellsCustom(table, 5, 5, 0, 1); //MergeCellsCustom(table, 6, 6, 0, 1); //MergeCellsCustom(table, 7, 9, 0, 0); //MergeCellsCustom(table, 8, 11, 0, 0); var i = 0; //内容数据 rainfallStatisticsDtoList.ForEach(t => { table.GetRow(i + 1).GetCell(0).SetParagraph(SetTableParagraphInstanceSetting(doc, table, t.SerialNumber, ParagraphAlignment.CENTER, 24, false)); table.GetRow(i + 1).GetCell(1).SetParagraph(SetTableParagraphInstanceSetting(doc, table, t.Listing, ParagraphAlignment.CENTER, 24, false)); table.GetRow(i + 1).GetCell(2).SetParagraph(SetTableParagraphInstanceSetting(doc, table, t.DataType, ParagraphAlignment.CENTER, 24, false)); table.GetRow(i + 1).GetCell(3).SetParagraph(SetTableParagraphInstanceSetting(doc, table, t.Fieldtype, ParagraphAlignment.CENTER, 24, false)); table.GetRow(i + 1).GetCell(4).SetParagraph(SetTableParagraphInstanceSetting(doc, table, t.Length.ToString(), ParagraphAlignment.CENTER, 24, false)); table.GetRow(i + 1).GetCell(5).SetParagraph(SetTableParagraphInstanceSetting(doc, table, t.IsNull, ParagraphAlignment.CENTER, 24, false)); table.GetRow(i + 1).GetCell(6).SetParagraph(SetTableParagraphInstanceSetting(doc, table, t.DefaultValue, ParagraphAlignment.CENTER, 24, false)); table.GetRow(i + 1).GetCell(7).SetParagraph(SetTableParagraphInstanceSetting(doc, table, t.Remark, ParagraphAlignment.CENTER, 24, false)); i++; }); } private void MergeCellsCustom(XWPFTable table, int startColumn, int endColumn, int startRow, int endRow) { for (int rowIndex = startRow; rowIndex <= endRow; rowIndex++) { if (startColumn < endColumn) { table.GetRow(rowIndex).MergeCells(startColumn, endColumn); } XWPFTableCell rowcell = table.GetRow(rowIndex).GetCell(startColumn); CT_Tc cTTC = rowcell.GetCTTc(); if (cTTC.tcPr == null) { cTTC.AddNewTcPr(); } if (rowIndex == startRow) { rowcell.GetCTTc().tcPr.AddNewVMerge().val = ST_Merge.restart; } else { rowcell.GetCTTc().tcPr.AddNewVMerge().val = ST_Merge.@continue; } } } private XWPFParagraph SetTableParagraphInstanceSetting(XWPFDocument document, XWPFTable table, string fillContent, ParagraphAlignment paragraphAlign, int textPosition = 24, bool isBold = false, int fontSize = 10, string fontColor = "000000", bool isItalic = false) { var para = new CT_P(); //设置单元格文本对齐 para.AddNewPPr().AddNewTextAlignment(); XWPFParagraph paragraph = new XWPFParagraph(para, table.Body);//创建表格中的段落对象 paragraph.Alignment = paragraphAlign;//文字显示位置,段落排列（左对齐，居中，右对齐） //paragraph.FontAlignment =Convert.ToInt32(ParagraphAlignment.CENTER);//字体在单元格内显示位置与 paragraph.Alignment效果相似 XWPFRun xwpfRun = paragraph.CreateRun();//创建段落文本对象 xwpfRun.SetText(fillContent); xwpfRun.FontSize = fontSize;//字体大小 xwpfRun.SetColor(fontColor);//设置字体颜色--十六进制 xwpfRun.IsItalic = isItalic;//是否设置斜体（字体倾斜） xwpfRun.IsBold = isBold;//是否加粗 xwpfRun.SetFontFamily("宋体", FontCharRange.None);//设置字体（如：微软雅黑,华文楷体,宋体） xwpfRun.TextPosition = textPosition;//设置文本位置（设置两行之间的行间），从而实现table的高度设置效果 return paragraph; } public class TableDesignInfo { public string SerialNumber; public string Listing; public string DataType; public string Fieldtype; public string Length; public string IsNull; public string DefaultValue; public string Remark; } } }
3.添加MqSqlHelper
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; using System.Data.SqlClient; using MySql.Data; using MySql.Data.MySqlClient; /// <summary> ///DBComm_MySQL 的摘要说明 /// </summary> public class DBComm_MySQL { public static string ConStr = GetConStr(); public DBComm_MySQL() { // //TODO: 在此处添加构造函数逻辑 // } public static string GetConStr() { return "User ID=root;Password=123;Host=192.168.1.1;Port=20000;Database=WaterConservancy;Pooling=true;Charset=utf8;"; //return System.Configuration.ConfigurationManager.ConnectionStrings["HKConnectionString"].ConnectionString.Trim(); } public static string GetConStr2() { return "User ID=root;Password=123;Host=192.1.1.141;Port=20000;Database=WaterConservancy;Pooling=true;Charset=utf8;"; //return System.Configuration.ConfigurationManager.ConnectionStrings["HKConnectionString2"].ConnectionString.Trim(); } public static DataTable ExecuteDataTable(string ASQL) { DataTable dt_return = new DataTable(); using (MySqlDataAdapter da = new MySqlDataAdapter(ASQL, GetConStr())) { da.Fill(dt_return); } return dt_return; } public static DataTable ExecuteDataTable2(string ASQL) { DataTable dt_return = new DataTable(); using (MySqlDataAdapter da = new MySqlDataAdapter(ASQL, GetConStr2())) { da.Fill(dt_return); } return dt_return; } /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteNonQuery(string SQLString, params MySqlParameter[] cmdParms) { using (MySqlConnection connection = new MySqlConnection(GetConStr())) { using (MySqlCommand cmd = new MySqlCommand()) { try { PrepareCommand(cmd, connection, null, SQLString, cmdParms); int rows = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return rows; } catch (MySql.Data.MySqlClient.MySqlException e) { throw e; } } } } private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms) { if (conn.State != ConnectionState.Open) conn.Open(); cmd.Connection = conn; cmd.CommandText = cmdText; if (trans != null) cmd.Transaction = trans; cmd.CommandType = CommandType.Text;//cmdType; if (cmdParms != null) { foreach (MySqlParameter parameter in cmdParms) { if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } cmd.Parameters.Add(parameter); } } } }
4.调用方法
5.导出结果
6.备注
若要替换其他数据库，只需更改MySqlHelper类即可
sql语句1：查询所有表名 SELECT Name FROM YapaiBridge..SysObjects Where XType = 'U' ORDER BY Name
sql语句2：查询所有表和字段
SELECT d.name as 表名,--如果表名相同就返回空 a.colorder as 字段序号, a.name as 字段名, (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) as 标识, (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) as 主键,--查询主键END b.name as 类型, a.length as 占用字节数, COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度, isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数, (case when a.isnullable=1 then '√'else '' end) as 允许空, isnull(e.text,'') as 默认值, isnull(g.[value],'') 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 order by a.id,a.colorder
浙公网安备 33010602011771号