根据Excel配置表单自动生成mysql创建表sql语句

开发了一个小工具，根据Excel表单来自动生成创建mysql的语句

一、新建Windows窗体应用程序AutoGenerateTool。添加对NPOI.dll的引用

使用NPOI来操作Excel.（NPOI在没有安装Excel的电脑上也可读、写Excel）。示例Excel如下：

二、NopiExcelOpretaUtil.cs用于一个Excel文件转化为DataTable，源代码如下：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HansCommon.Excel
{
    public static class NopiExcelOpretaUtil
    {

        #region Excel操作
        /// <summary>
        /// Excel导入成Datable
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string file)
        {
            DataTable dt = new DataTable();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
                if (workbook == null) { return null; }
                ISheet sheet = workbook.GetSheetAt(0);
                //表头  
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueType(header.GetCell(i));
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据  
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }
        /// <summary>
        /// Excel导入成Datable
        /// </summary>
        /// <param name="file">路径</param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string file,string sheetName)
        {
            DataTable dt = new DataTable();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
                if (workbook == null) { return null; }
                //ISheet sheet = workbook.GetSheetAt(0);
                ISheet sheet = workbook.GetSheet(sheetName);
                if (sheet == null)
                {
                    return null;
                }
                //表头  
                IRow header = sheet.GetRow(sheet.FirstRowNum);
                List<int> columns = new List<int>();
                for (int i = 0; i < header.LastCellNum; i++)
                {
                    object obj = GetValueType(header.GetCell(i));
                    if (obj == null || obj.ToString() == string.Empty)
                    {
                        dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                    }
                    else
                        dt.Columns.Add(new DataColumn(obj.ToString()));
                    columns.Add(i);
                }
                //数据  
                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    DataRow dr = dt.NewRow();
                    bool hasValue = false;
                    foreach (int j in columns)
                    {
                        dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                        if (dr[j] != null && dr[j].ToString() != string.Empty)
                        {
                            hasValue = true;
                        }
                    }
                    if (hasValue)
                    {
                        dt.Rows.Add(dr);
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// Excel导入成Datable集合
        /// </summary>
        /// <param name="file">路径</param>
        /// <param name="list_SheetName">sheet名称集合</param>
        /// <returns></returns>
        public static DataTable[] ExcelToTable(string file, List< string>  list_SheetName)
        {
            int count = list_SheetName.Count;
            DataTable[] dtS =new  DataTable[list_SheetName.Count];
            //===============================//
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
                if (workbook == null) { return null; }
                //=========================================//
                ISheet[] sheetS = new ISheet[count];
                for (int k = 0; k < count; k++)
                {
                    dtS[k] = new DataTable(list_SheetName[k]);
                    sheetS[k] = workbook.GetSheet(list_SheetName[k]);
                    ISheet sheet = sheetS[k];
                    //==================================//
                    if (sheet == null)
                    {
                        continue;                       
                    }
                    DataTable dt = new DataTable(list_SheetName[k]);
                    //表头  
                    IRow header = sheet.GetRow(sheet.FirstRowNum);
                    List<int> columns = new List<int>();
                    for (int i = 0; i < header.LastCellNum; i++)
                    {
                        object obj = GetValueType(header.GetCell(i));
                        if (obj == null || obj.ToString() == string.Empty)
                        {
                            dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        }
                        else
                            dt.Columns.Add(new DataColumn(obj.ToString()));
                        columns.Add(i);
                    }
                    //数据  
                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                    {
                        DataRow dr = dt.NewRow();
                        bool hasValue = false;
                        foreach (int j in columns)
                        {
                            dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                            if (dr[j] != null && dr[j].ToString() != string.Empty)
                            {
                                hasValue = true;
                            }
                        }
                        if (hasValue)
                        {
                            dt.Rows.Add(dr);
                        }
                    }
                    dtS[k] = dt;
                }
            }
            return dtS;
        }
        /// <summary>
        /// Datable导出成Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="file"></param>
        public static void TableToExcel(DataTable dt, string file)
        {
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            if (fileExt == ".xlsx")
            {
                //workbook = new XSSFWorkbook();
                workbook = new HSSFWorkbook();
            }
            else if (fileExt == ".xls")
            {
                workbook = new HSSFWorkbook();
            }
            else
            {
                workbook = null;
            }
            if (workbook == null)
            {
                return;
            }
            ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
            //表头  
            IRow row = sheet.CreateRow(0);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }

            //数据  
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row1 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转为字节数组  
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件  
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:  
                    return null;
                case CellType.Boolean: //BOOLEAN:  
                    return cell.BooleanCellValue;
                case CellType.Numeric: //NUMERIC:  
                    return cell.NumericCellValue;
                case CellType.String: //STRING:  
                    return cell.StringCellValue;
                case CellType.Error: //ERROR:  
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:  
                default:
                    return "=" + cell.CellFormula;
            }
        }
        /// <summary>
        /// 重命名列
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="oldName"></param>
        /// <param name="newName"></param>
        public static void ReNameColumnName(DataTable dt, string oldName, string newName)
        {
            if (dt.Columns.Contains(oldName))
            {
                dt.Columns[oldName].ColumnName = newName;
            }
        }
        #endregion
    }
}

三、GenerateUtil.cs用于根据excel文件来生成一个创建mysql的语句。源代码如下：

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AutoGenerateTool
{
    public  class GenerateUtil
    {
        /// <summary>
        /// 根据excel文件来生成一个创建mysql的语句
        /// </summary>
        /// <param name="xlsfilePath"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string GenerateSql(string xlsfilePath, string tableName)
        {
            try
            {
                DataTable dt = HansCommon.Excel.NopiExcelOpretaUtil.ExcelToTable(xlsfilePath, tableName);
                if (dt == null || dt.Rows.Count == 0)
                {
                    MessageBox.Show($"{tableName}没有配置表的任何数据");
                    return "";
                }

                if (!dt.Columns.Contains("TABLE_NAME"))
                {
                    MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--不存在TABLE_NAME列");
                    return "";
                }
                if (!dt.Columns.Contains("En_Name"))
                {
                    MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--不存在En_Name列");
                    return "";
                }
                if (!dt.Columns.Contains("Ch_Name"))
                {
                    MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--不存在Ch_Name列");
                    return "";
                }
                if (!dt.Columns.Contains("Data_Type"))
                {
                    MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--不存在Data_Type列");
                    return "";
                }
                if (!dt.Columns.Contains("Data_Length"))
                {
                    MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--不存在Data_Length列");
                    return "";
                }
                if (!dt.Columns.Contains("Not_Null"))
                {
                    MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--不存在Not_Null列");
                    return "";
                }
                if (!dt.Columns.Contains("Default_Value"))
                {
                    MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--不存在Default_Value列");
                    return "";
                }

                int columnCount = dt.Rows.Count;
                if (dt.Rows[0]["TABLE_NAME"] == null || tableName != dt.Rows[0]["TABLE_NAME"].ToString().Trim())
                {
                    MessageBox.Show($"{tableName}表的UI配置文件错误，请检查--表名与表中TABLE_NAME不匹配");
                    return "";
                }

                StringBuilder sb = new StringBuilder("CREATE TABLE  IF NOT EXISTS `"+ tableName + @"` (
`CoreId` int NOT NULL AUTO_INCREMENT comment '主键自增编号',");
                sb.AppendLine();
                for (int i = 0; i < columnCount; i++)
                {
                    string fieldName = dt.Rows[i]["En_Name"].ToString();
                    string dataType= dt.Rows[i]["Data_Type"].ToString();
                    int length;
                    if (!int.TryParse(dt.Rows[i]["Data_Length"].ToString(), out length))
                    {
                        throw new Exception("输入的数据长度不正确,不是整数，字段：" + fieldName);
                    }
                    bool notNull = dt.Rows[i]["Not_Null"].ToString() == "1";
                    string defaultValue = dt.Rows[i]["Default_Value"].ToString();
                    string comment= dt.Rows[i]["Ch_Name"].ToString();
                    string columnInfo = AddField(fieldName, dataType, length, notNull, defaultValue, comment);
                    sb.AppendLine(columnInfo);
                }
                sb.AppendLine(@"PRIMARY KEY(`CoreId`)
) ENGINE = InnoDB  DEFAULT CHARSET = utf8 comment '焊接生产数据表'");
                return sb.ToString();
            }
            catch (Exception ex)
            {
                MessageBox.Show($"{tableName}表的UI配置文件错误，请检查:" + ex.Message);
                return "";
            }
        }

        /// <summary>
        /// 增加一行字段
        /// </summary>
        /// <param name="fieldName"></param>
        /// <param name="dataType"></param>
        /// <param name="length"></param>
        /// <param name="notNull"></param>
        /// <param name="defaultValue"></param>
        /// <param name="comment"></param>
        /// <returns></returns>
        public static string AddField(string fieldName, string dataType, int length, bool notNull, string defaultValue, string comment)
        {
            dataType = dataType.Trim();
            string dataTypeDefine = $"{dataType}";
            if (dataType.IndexOf("int", StringComparison.CurrentCultureIgnoreCase) >= 0
                || dataType.IndexOf("integer", StringComparison.CurrentCultureIgnoreCase) >= 0
                || dataType.IndexOf("bigint", StringComparison.CurrentCultureIgnoreCase) >= 0)
            {
                defaultValue = "0";
            }
            else if (dataType.IndexOf("real", StringComparison.CurrentCultureIgnoreCase) >= 0
                || dataType.IndexOf("double", StringComparison.CurrentCultureIgnoreCase) >= 0
                || dataType.IndexOf("float", StringComparison.CurrentCultureIgnoreCase) >= 0)
            {
                //浮点小数
                /*float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型；
MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，如float(7,4)的 可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001。
FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。比如Decimal在不指定小数时，1.2345存入数据库中为1，没有小数*/
                defaultValue = "0";
            }
            else if (dataType.IndexOf("decimal", StringComparison.CurrentCultureIgnoreCase) >= 0
                || dataType.IndexOf("numeric", StringComparison.CurrentCultureIgnoreCase) >= 0)
            {
                //定点小数
                //p 和 s 必须遵守以下规则：0 <= s <= p <= 38.如果不指定decimal或numeric的小数位数，则无小数点后面的位数
                defaultValue = "0";
                if (dataType.ToUpper() == "DECIMAL" || dataType.ToUpper() == "NUMERIC")
                {
                    dataTypeDefine = dataType + "(10,2)";//加上两个小数点
                }
            }
            else if (dataType.IndexOf("char", StringComparison.CurrentCultureIgnoreCase) >= 0
                || dataType.IndexOf("varchar", StringComparison.CurrentCultureIgnoreCase) >= 0)
            {
                dataTypeDefine = $"{dataType}({length})";
                defaultValue = "''";
            }
            else if (dataType.IndexOf("date", StringComparison.CurrentCultureIgnoreCase) >= 0
                || dataType.IndexOf("time", StringComparison.CurrentCultureIgnoreCase) >= 0
                || dataType.IndexOf("datetime", StringComparison.CurrentCultureIgnoreCase) >= 0)
            {
                dataTypeDefine = "datetime";
                defaultValue = "current_timestamp";
            }

            string columnStr = $"{fieldName} {dataTypeDefine}{(notNull ? " not null" : "")} DEFAULT {defaultValue} COMMENT '{comment}',";
            return columnStr;
        }
    }
}
 

四、新建windows窗体FrmDataGridViewIniCofGenerate。界面布局：

FrmDataGridViewIniCofGenerate.cs源代码如下：（忽略设计器自动生成的代码）

using HansCommon.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AutoGenerateTool
{
    public partial class FrmDataGridViewIniCofGenerate : Form
    {
        public FrmDataGridViewIniCofGenerate()
        {
            InitializeComponent();
        }
        
        /// <summary>
        /// 选择配置文件 按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            richTextBox1.Clear();
            string tableName = txtTableName.Text.Trim();
            OpenFileDialog openFileDialog = new OpenFileDialog();
            openFileDialog.InitialDirectory = AppDomain.CurrentDomain.BaseDirectory;
            openFileDialog.Filter = "Execl   files   (*.xls)|*.xls";
            if (openFileDialog.ShowDialog() == DialogResult.OK)
            {
                string fileName = openFileDialog.FileName;
                string sql = GenerateUtil.GenerateSql(fileName, tableName);
                richTextBox1.AppendText(sql);
            }
        }

        private static object locker = new object();

        /// <summary>
        /// 保存 按钮
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {
            string sql = richTextBox1.Text;
            string tableName = txtTableName.Text.Trim();
            SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            saveFileDialog1.FileName = string.Format("{0}.sql", tableName);//名称
            saveFileDialog1.InitialDirectory = AppDomain.CurrentDomain.BaseDirectory;//路径
            saveFileDialog1.Title = "保存文件到";//标题
            saveFileDialog1.Filter = "Sql file (*.sql)|*.sql";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                lock (locker)
                {
                    string folder = Path.GetDirectoryName(saveFileDialog1.FileName);
                    bool existDirectory = Directory.Exists(folder);
                    if (!existDirectory)
                    {
                        Directory.CreateDirectory(folder);
                    }
                    File.AppendAllText(saveFileDialog1.FileName, sql, Encoding.Default);
                }
                MessageBox.Show("已保存创建表SQL语句到文件:\n" + saveFileDialog1.FileName);
            }
        }
    }
}
五、程序运行如图：