根据Excel表的配置,自动生成数据库表的CRUD以及相关的Winform列表和编辑界面程序文件,接上篇(自动生成实体类)

C# 专栏收录该内容
90 篇文章 0 订阅

本文仍然属于一个自动生成代码工具。

上一篇我们根据数据库表的信息,自动生成实体类.cs文件。这次我们根据Excel配置,直接生成CRUD以及窗体列表和编辑界面相关的程序源文件。

通过自动生成代码,这样可以大大减少我们的开发工作量。

在原来的应用程序AutoGenerateForm添加对NPOI类库的引用,用于读写Excel文件。相关引用【NPOI.dll】【NPOI.OOXML.dll】【NPOI.OpenXml4Net.dll】【NPOI.OpenXmlFormats.dll】

excel配置文件 Conf\DataGridViewConf_Form.xls的设计如图:

关键列:

Data_Type 表示 数据类型

Field_Category 字段类型枚举:[PrimaryKey,主键列,用于更新、删除某一行记录],[BarcodeField,条码列,用于查询数据],[Normal,其他普通列]

Form_Category 表单类型枚举:代表该列在编辑区域显示的表单控件类型。该字段对应文本框、组合框、日期时间选择等组件的某一个。

Form_Visible 代表该表单是否显示

Form_Collection 用于列表或者下拉选择

Form_Min、Form_Max 数字类型时设置上下限值

Form_Location_X、Form_Location_Y 控件所在的位置坐标

新建读写Excel操作NpoiExcelOperateUtil.cs,源程序如下:

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 AutoGenerateForm
{
    public static class NpoiExcelOperateUtil
    {
        #region Excel操作
        /// <summary>
        /// Excel的第一个工作簿(Sheet)转化成DataTable
        /// 使用EXCEL的第一个工作簿,默认为Sheet1
        /// </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
                {
                    return null;
                }
                //第一个工作簿
                ISheet sheet = workbook.GetSheetAt(0);
                if (sheet == null)
                {
                    return null;
                }
                return ExcelToTable(file, sheet.SheetName);
            }
        }
        /// <summary>
        /// Excel的指定Sheet转化成内存表
        /// </summary>
        /// <param name="file">路径</param>
        /// <param name="sheetName">sheet名称</param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string file, string sheetName)
        {
            DataTable[] dataTables = ExcelToTable(file, new List<string>() { sheetName });
            if (dataTables != null && dataTables.Length > 0)
            {
                return dataTables[0];
            }
            return null;
        }

        /// <summary>
        /// 一个excel文件的多个Sheet转化成内存表数组,
        /// 每个Sheet都对应一个数据表
        /// </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[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
                {
                    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
    }
}

原来的RawSql.cs增加自定义查询数据,整体RawSql.cs 如下:

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AutoGenerateForm
{
    /// <summary>
    /// 原生的sql增删改查
    /// </summary>
    public class RawSql
    {
        /// <summary>
        /// 执行原生的Sql与sql参数的insert、update、delete等操作,返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dict"></param>
        /// <param name="dbType">数据库类型,一般是mysql</param>
        /// <param name="index">额外的连接数据库,2为新的数据库连接字符串</param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql, Dictionary<string, object> dict, SqlSugar.DbType dbType = SqlSugar.DbType.MySql, int index = 1)
        {
            List<SugarParameter> parameters = DictToList(dict);
            using (var db = SugarDao.GetInstance(dbType, index))
            {
                return db.Ado.ExecuteCommand(sql, parameters);
            }
        }

        /// <summary>
        /// 执行原生的Sql与sql参数的select查询等操作,返回首行首列的数据
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dict"></param>
        /// <param name="dbType">数据库类型,一般是mysql</param>
        /// <param name="index">额外的连接数据库,2为新的数据库连接字符串</param>
        /// <returns></returns>
        public static object GetScalar(string sql, Dictionary<string, object> dict, SqlSugar.DbType dbType = SqlSugar.DbType.MySql, int index = 1)
        {
            List<SugarParameter> parameters = DictToList(dict);
            using (var db = SugarDao.GetInstance(dbType, index))
            {
                return db.Ado.GetScalar(sql, parameters);
            }
        }

        /// <summary>
        /// 执行原生的Sql与sql参数的select查询等操作,返回一个数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dict"></param>
        /// <param name="dbType">数据库类型,一般是mysql</param>
        /// <param name="index">额外的连接数据库,2为新的数据库连接字符串</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, Dictionary<string, object> dict, SqlSugar.DbType dbType = SqlSugar.DbType.MySql, int index = 1)
        {
            List<SugarParameter> parameters = DictToList(dict);
            using (var db = SugarDao.GetInstance(dbType, index))
            {
                return db.Ado.GetDataTable(sql, parameters);
            }
        }

        /// <summary>
        /// 字典转参数列表
        /// </summary>
        /// <param name="dict"></param>
        /// <returns></returns>
        private static List<SugarParameter> DictToList(Dictionary<string, object> dict)
        {
            List<SugarParameter> parameters = new List<SugarParameter>();
            for (int i = 0; dict != null && i < dict.Count; i++)
            {
                KeyValuePair<string, object> keyValuePair = dict.ElementAt(i);
                parameters.Add(new SugarParameter(keyValuePair.Key, keyValuePair.Value));
            }
            return parameters;
        }

        /// <summary>
        /// 查询表的相关项,生成一个内存表
        /// </summary>
        /// <param name="selectItems"></param>
        /// <param name="where_expression"></param>
        /// <param name="dictAddPara"></param>
        /// <param name="tableName"></param>
        /// <param name="tableNameAs"></param>
        /// <param name="orderfild"></param>
        /// <returns></returns>
        public static DataTable Select_By_Expression(string selectItems, string where_expression, Dictionary<string, object> dictAddPara, string tableName, string tableNameAs, string orderfild = "")
        {
            DataTable dataTable = new DataTable(tableName);
            using (var db = SugarDao.GetInstance())
            {
                if (!selectItems.Contains(orderfild))
                {
                    orderfild = "";
                }
                var datax = db.Queryable(tableName, tableNameAs).Where(where_expression).AddParameters(dictAddPara).Select(selectItems).OrderByIF(orderfild != "", orderfild);
                try
                {
                    dataTable = datax.ToDataTable();
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"【查询出现异常】表【{tableName}】-{ex.Message}");
                }
                return dataTable;
            }
        }
    }
}

新增类 CSVTitleName.cs,用于保存列的数据类型,字段类型是主键列、条码列、普通列。源程序

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AutoGenerateForm
{
    /// <summary>
    /// CSV标题头实体类
    /// </summary>
    public class CSVTitleName
    {
        /// <summary>
        /// 中文名称【列的注释信息】
        /// </summary>
        public string ChineseName { get; set; }

        /// <summary>
        /// 英文名称【列名、字段名】
        /// </summary>
        public string EnglishName { get; set; }

        /// <summary>
        /// 列的数据类型
        /// </summary>
        public string DataType { get; set; }

        /// <summary>
        /// 字段类型:主键字段、条码字段、普通字段
        /// 用于 生成【CRUD】类的相关操作是查找条码字段
        /// </summary>
        public string FieldCategory { get; set; }
    }
}

生成表单的三个辅助类AutoGenerateUtil.cs

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

namespace AutoGenerateForm
{
    /// <summary>
    /// 自动生成窗体相关控件代码 以及绑定内容,以及读取控件的值绑定到实体类的属性上等相关操作类
    /// 斯内科 2021-02-01
    /// </summary>
    public class AutoGenerateUtil
    {
        /// <summary>
        /// 获取主键列 和 条码列的名称
        /// </summary>
        /// <param name="_primaryKeyField"></param>
        /// <param name="_barcodeField"></param>
        public static void GetPrimaryKeyAndBarcodeField<T>(out string _primaryKeyField, out string _barcodeField)
        {
            _primaryKeyField = "CoreId";
            _barcodeField = "Barcode";
            Type type = typeof(T);
            //调用实例化方法(非静态方法)需要创建类型的一个实例
            object instanceObject = Activator.CreateInstance(type);
            //遍历所有可交互控件,获取控件的Tag以及对应的值,并为属性赋值
            IEnumerable<PropertyInfo> propertyCollection = type.GetProperties().Where(property => property.CustomAttributes.Count() > 0);
            //查找主键列名
            PropertyInfo propertyPrimaryKey = propertyCollection.Where(property => property.CustomAttributes.
              Where(attrData => attrData.NamedArguments.Where(attrArg => attrArg.MemberName == "IsPrimaryKey").Count() > 0).Count() > 0).FirstOrDefault();
            if (propertyPrimaryKey == null)
            {
                MessageBox.Show("没有配置实体类的主键列特性:IsPrimaryKey");
                return;
            }
            _primaryKeyField = propertyPrimaryKey.Name;

            //找到指定的条码列 [SqlSugar.SugarColumn(ColumnDescription = "Barcode")]
            PropertyInfo propertyBarcode = propertyCollection.Where(property => property.CustomAttributes.
              Where(attrData => attrData.NamedArguments.Where(attrArg => attrArg.MemberName == "ColumnDescription" && Convert.ToString(attrArg.TypedValue.Value) == "Barcode").Count() > 0).Count() > 0).FirstOrDefault();
            if (propertyBarcode == null)
            {
                MessageBox.Show($"没有配置实体类的条码列【查询条件列】特性:ColumnDescription = \"Barcode\"");
                return;
            }
            _barcodeField = propertyBarcode.Name;
        }

        /// <summary>
        /// 使用表设计器初始化DataGridView的列绑定,以及增加 编辑和删除列
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dgw_Data"></param>
        public static void InitialDataGridViewColumns(DataTable dt, DataGridView dgw_Data)
        {
            dgw_Data.AutoGenerateColumns = false;
            DataGridViewUtil.DataGridViewIni(dt, ref dgw_Data);
            //增加编辑、删除列
            DataGridViewButtonColumn dgvcEdit = new DataGridViewButtonColumn();
            dgvcEdit.Name = "dgvcEdit";
            dgvcEdit.HeaderText = "编辑";
            dgvcEdit.Text = "编辑";
            dgvcEdit.ReadOnly = true;
            dgvcEdit.UseColumnTextForButtonValue = true;//显示按钮列的单元格的文本
            dgw_Data.Columns.Add(dgvcEdit);

            DataGridViewButtonColumn dgvcDelete = new DataGridViewButtonColumn();
            dgvcDelete.Name = "dgvcDelete";
            dgvcDelete.HeaderText = "删除";
            dgvcDelete.Text = "删除";
            dgvcDelete.ReadOnly = true;
            dgvcDelete.UseColumnTextForButtonValue = true;
            dgw_Data.Columns.Add(dgvcDelete);
        }

        /// <summary>
        /// 绑定查询的数据到DataGridView上
        /// </summary>
        /// <param name="keyword">查询的条件</param>
        /// <param name="barcodeField">查询的字段</param>
        /// <param name="selectItemCollection">选择的列集合,以逗号拼接</param>
        /// <param name="currentTableName">查询的表名</param>
        /// <param name="dgw_Data">要绑定的DataGridView</param>
        public static void BindDataList(string keyword, string barcodeField, string selectItemCollection, string currentTableName, DataGridView dgw_Data)
        {
            string tableNameAs = "tb";//表的别名
            Dictionary<string, object> dictAddPara = new Dictionary<string, object>();//SQL中的参数以及对应的值
            List<string> conditionCollection = new List<string>();
            //如果关键字不为空
            if (keyword.Length > 0)
            {
                conditionCollection.Add($"{tableNameAs}.{barcodeField}=@PackBarcode");
                dictAddPara.Add("PackBarcode", keyword);
            }

            //默认使用and连接
            string where_expression = string.Join(" and ", conditionCollection);
            string tableName = currentTableName;//查找的表名
            DataTable dtTemp = new DataTable(currentTableName);
            dtTemp = RawSql.Select_By_Expression(selectItemCollection, where_expression, dictAddPara, tableName, tableNameAs);
            dgw_Data.DataSource = dtTemp;
        }

        /// <summary>
        /// 初始化创建相关设计类的所有控件信息
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="form"></param>
        public static void InitialCreateControls(DataTable dt, Form form)
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string formVisible = dt.Rows[i]["Form_Visible"].ToString();
                if (formVisible != "1")
                {
                    //如果不显示就 忽略
                    continue;
                }
                string chineseName = dt.Rows[i]["Ch_Name"].ToString();//标签中文名
                string englishName = dt.Rows[i]["En_Name"].ToString();//英文名
                string dataType = dt.Rows[i]["Data_Type"].ToString();//数据类型
                string defaultValue = dt.Rows[i]["Default_Value"].ToString();//默认值
                string formCollection = dt.Rows[i]["Form_Collection"].ToString();//组合下拉框 或者 单选按钮的项集合,使用|(竖线)分割
                string formMin = dt.Rows[i]["Form_Min"].ToString();//数字显示框的下限值
                string formMax = dt.Rows[i]["Form_Max"].ToString();//数字显示框的上限值
                string formLocationX = dt.Rows[i]["Form_Location_X"].ToString();//起始位置坐标X
                string formLocationY = dt.Rows[i]["Form_Location_Y"].ToString();//起始位置坐标Y
                int locationX;//X坐标
                int locationY;//Y坐标
                int.TryParse(formLocationX, out locationX);
                int.TryParse(formLocationY, out locationY);
                FormCategory formCategory;
                if (!Enum.TryParse(dt.Rows[i]["Form_Category"].ToString(), true, out formCategory))
                {
                    //默认使用文本框
                    formCategory = FormCategory.TextBox;
                }
                Label lbl = AutoGenerateUtil.SetLabel(chineseName, englishName, locationX + 5 - 120, locationY);
                switch (formCategory)
                {
                    case FormCategory.None:
                        break;
                    case FormCategory.TextBox:
                        form.Controls.Add(lbl);//增加标签显示

                        TextBox txb = SetTextBox(englishName, defaultValue, locationX, locationY);
                        form.Controls.Add(txb);
                        break;
                    case FormCategory.ComboBox:
                        form.Controls.Add(lbl);//增加标签显示

                        ComboBox cbo = SetComboBox(englishName, defaultValue, formCollection, locationX, locationY);
                        form.Controls.Add(cbo);
                        break;
                    case FormCategory.RadioButton:
                        form.Controls.Add(lbl);//增加标签显示

                        //这里使用GroupBox包含所有的单选按钮
                        GroupBox grp = SetGroupAndRadioButton(englishName, formCollection, locationX, locationY);
                        form.Controls.Add(grp);
                        break;
                    case FormCategory.DateTimePicker:
                        form.Controls.Add(lbl);//增加标签显示

                        DateTimePicker dtp = SetDateTimePicker(englishName, locationX, locationY);
                        form.Controls.Add(dtp);
                        break;
                    case FormCategory.NumericUpDown:
                        form.Controls.Add(lbl);//增加标签显示

                        NumericUpDown numericUpDown = SetNumericUpDown(englishName, dataType, formMin, formMax, locationX, locationY);
                        form.Controls.Add(numericUpDown);
                        break;
                }
            }
        }

        /// <summary>
        /// 绑定控件的初始值【从数据库中读取】
        /// </summary>
        /// <param name="dataTable"></param>
        public static void BindControlValue(DataTable dataTable, Form form)
        {
            if (dataTable == null || dataTable.Rows.Count < 1)
            {
                return;
            }
            DataRow dataRow = dataTable.Rows[0];
            for (int i = 0; i < form.Controls.Count; i++)
            {
                Control control = form.Controls[i];
                if (control is Label || control is Button || control.Tag == null)
                {
                    //不考虑标签 或者 Tag为null的控件
                    continue;
                }
                string fieldName = Convert.ToString(control.Tag);
                //NumericUpDown控件的Tag特殊处理,使用二元组,第一个项代表绑定的列名(字段名),第二个项代表数据类型
                if (control is NumericUpDown)
                {
                    fieldName = (control.Tag as Tuple<string, string>).Item1;
                }
                //如果数据表的列集合中不含有该字段,则忽略
                if (!dataTable.Columns.Contains(fieldName))
                {
                    continue;
                }

                //为指定的控件赋值
                if (control is TextBox)
                {
                    ((TextBox)control).Text = dataRow[fieldName].ToString();
                }
                else if (control is ComboBox)
                {
                    ComboBox cbo = control as ComboBox;
                    for (int index = 0; index < cbo.Items.Count; index++)
                    {
                        if (cbo.Items[index].ToString() == dataRow[fieldName].ToString())
                        {
                            cbo.SelectedIndex = index;
                            break;
                        }
                    }
                }
                else if (control is GroupBox)
                {
                    //设置RadioButton的选中
                    GroupBox grp = control as GroupBox;
                    for (int index = 0; index < grp.Controls.Count; index++)
                    {
                        RadioButton rdo = grp.Controls[index] as RadioButton;
                        if (rdo != null && rdo.Text == dataRow[fieldName].ToString())
                        {
                            rdo.Checked = true;
                            break;
                        }
                        else if (rdo != null)
                        {
                            rdo.Checked = false;
                        }
                    }
                }
                else if (control is DateTimePicker)
                {
                    ((DateTimePicker)control).Value = Convert.ToDateTime(dataRow[fieldName]);
                }
                else if (control is NumericUpDown)
                {
                    ((NumericUpDown)control).Value = Convert.ToDecimal(dataRow[fieldName]);
                }
            }
        }

        /// <summary>
        /// 遍历所有可交互控件,获取控件的Tag以及对应的值,并为实体类的对应属性赋值
        /// </summary>
        /// <param name="type"></param>
        /// <param name="instanceObject"></param>
        public static void SetInstanceObjectPropertyValues(Type type, object instanceObject, Form form)
        {
            for (int i = 0; i < form.Controls.Count; i++)
            {
                Control control = form.Controls[i];
                if (control is Label || control is Button || control.Tag == null)
                {
                    //不考虑标签 或者 Tag为null的控件
                    continue;
                }
                //找出控件的数据对象 也就是属性名(字段名、列名)
                string fieldName = Convert.ToString(control.Tag);
                //NumericUpDown控件的Tag特殊处理,使用二元组,第一个项代表绑定的列名(字段名),第二个项代表数据类型
                if (control is NumericUpDown)
                {
                    fieldName = (control.Tag as Tuple<string, string>).Item1;
                }
                PropertyInfo propertyInfoName = type.GetProperty(fieldName);
                //如果类不存在该属性,则忽略
                if (propertyInfoName == null)
                {
                    continue;
                }

                //为指定的控件赋值
                if (control is TextBox)
                {
                    propertyInfoName.SetValue(instanceObject, ((TextBox)control).Text);
                }
                else if (control is ComboBox)
                {
                    ComboBox cbo = control as ComboBox;
                    propertyInfoName.SetValue(instanceObject, ((ComboBox)control).Text);
                }
                else if (control is GroupBox)
                {
                    //设置RadioButton的选中
                    GroupBox grp = control as GroupBox;
                    for (int index = 0; index < grp.Controls.Count; index++)
                    {
                        RadioButton rdo = grp.Controls[index] as RadioButton;
                        if (rdo != null && rdo.Checked)
                        {
                            propertyInfoName.SetValue(instanceObject, rdo.Text);
                            break;
                        }
                    }
                }
                else if (control is DateTimePicker)
                {
                    propertyInfoName.SetValue(instanceObject, ((DateTimePicker)control).Value);
                }
                else if (control is NumericUpDown)
                {
                    NumericUpDown numericUpDown = control as NumericUpDown;
                    string dataType = (numericUpDown.Tag as Tuple<string, string>).Item2;
                    if (dataType != null && dataType.IndexOf("int", StringComparison.CurrentCultureIgnoreCase) >= 0)
                    {
                        propertyInfoName.SetValue(instanceObject, Convert.ToInt32(numericUpDown.Value));
                    }
                    else if (dataType != null && dataType.IndexOf("decimal", StringComparison.CurrentCultureIgnoreCase) >= 0)
                    {
                        propertyInfoName.SetValue(instanceObject, numericUpDown.Value);
                    }
                    else
                    {
                        propertyInfoName.SetValue(instanceObject, Convert.ToSingle(numericUpDown.Value));
                    }
                }
            }
        }

        /// <summary>
        /// 设置Label标签控件
        /// </summary>
        /// <param name="chineseName"></param>
        /// <param name="englishName"></param>
        /// <param name="locationX"></param>
        /// <param name="locationY"></param>
        /// <returns></returns>
        private static Label SetLabel(string chineseName, string englishName, int locationX, int locationY)
        {
            Label lbl = new Label();
            //如果字体较多 就设置为两行。如果字体的宽度少于16个空格【八个中文宽度大小】,就补充空格数:(16 - bufferSize.Length)
            byte[] bufferSize = Encoding.GetEncoding("gbk").GetBytes(chineseName);
            if (bufferSize.Length > 16)
            {
                lbl.Size = new Size(110, 26);
            }
            else
            {
                chineseName = chineseName.PadLeft(chineseName.Length + 16 - bufferSize.Length);
                lbl.AutoSize = true;
            }
            lbl.Name = $"lbl{englishName}";
            lbl.Text = chineseName;
            lbl.Location = new Point(locationX, locationY);
            return lbl;
        }

        /// <summary>
        /// 设置文本框控件
        /// </summary>
        /// <param name="englishName"></param>
        /// <param name="defaultValue"></param>
        /// <param name="locationX"></param>
        /// <param name="locationY"></param>
        /// <returns></returns>
        private static TextBox SetTextBox(string englishName, string defaultValue, int locationX, int locationY)
        {
            TextBox txb = new TextBox();
            txb.Name = $"txb{englishName}";
            txb.Text = defaultValue;
            txb.Location = new Point(locationX, locationY);
            txb.Size = new Size(200, 21);
            txb.Tag = englishName;//增加控件 绑定的列名(字段名)
            return txb;
        }

        /// <summary>
        /// 设置组合框控件
        /// </summary>
        /// <param name="englishName"></param>
        /// <param name="defaultValue"></param>
        /// <param name="formCollection"></param>
        /// <param name="locationX"></param>
        /// <param name="locationY"></param>
        /// <returns></returns>
        private static ComboBox SetComboBox(string englishName, string defaultValue, string formCollection, int locationX, int locationY)
        {
            ComboBox cbo = new ComboBox();
            cbo.Name = $"cbo{englishName}";
            cbo.Text = defaultValue;
            cbo.Location = new Point(locationX, locationY);
            if (!string.IsNullOrEmpty(formCollection))
            {
                string[] itemArray = formCollection.Split('|');
                for (int index = 0; index < itemArray.Length; index++)
                {
                    cbo.Items.Add(itemArray[index]);
                }
            }
            cbo.Size = new Size(200, 21);
            cbo.Tag = englishName;//增加控件 绑定的列名(字段名)
            return cbo;
        }

        /// <summary>
        /// 设置分组框和单选按钮集合【多个单选按钮放入在同一个分组框中】
        /// </summary>
        /// <param name="englishName"></param>
        /// <param name="formCollection"></param>
        /// <param name="locationX"></param>
        /// <param name="locationY"></param>
        /// <returns></returns>
        private static GroupBox SetGroupAndRadioButton(string englishName, string formCollection, int locationX, int locationY)
        {
            //这里使用GroupBox包含所有的单选按钮
            GroupBox grp = new GroupBox();
            grp.Name = $"grp{englishName}";
            //grp.Text = chineseName;
            grp.Location = new Point(locationX, locationY - 20);
            grp.Size = new Size(200, 40);
            grp.Tag = englishName;//增加控件 绑定的列名(字段名)

            if (string.IsNullOrEmpty(formCollection))
            {
                formCollection = "选项一";
            }
            string[] itemArr = formCollection.Split('|');
            for (int index = 0; index < itemArr.Length; index++)
            {
                RadioButton rdo = new RadioButton();
                rdo.Name = $"rdo{englishName}{index + 1}";
                rdo.Text = itemArr[index];
                rdo.Location = new Point(10 + 70 * index, 20);
                rdo.AutoSize = true;
                if (index == 0)
                {
                    rdo.Checked = true;//设置第一项为选中
                }
                grp.Controls.Add(rdo);
            }
            return grp;
        }

        /// <summary>
        /// 设置日期时间选择控件
        /// </summary>
        /// <param name="englishName"></param>
        /// <param name="locationX"></param>
        /// <param name="locationY"></param>
        /// <returns></returns>
        private static DateTimePicker SetDateTimePicker(string englishName, int locationX, int locationY)
        {
            DateTimePicker dtp = new DateTimePicker();
            dtp.Name = $"dtp{englishName}";
            dtp.Value = DateTime.Now;
            dtp.Location = new Point(locationX, locationY);
            dtp.Format = DateTimePickerFormat.Custom; //设置日期格式为2020-08-05
            dtp.CustomFormat = "yyyy-MM-dd HH:mm:ss";
            dtp.Tag = englishName;//增加控件 绑定的列名(字段名)
            return dtp;
        }

        /// <summary>
        /// 设置数字显示框控件
        /// NumericUpDown控件的Tag特殊处理,使用二元组,第一个项代表绑定的列名(字段名),第二个项代表数据类型
        /// </summary>
        /// <param name="englishName"></param>
        /// <param name="dataType"></param>
        /// <param name="formMin"></param>
        /// <param name="formMax"></param>
        /// <param name="locationX"></param>
        /// <param name="locationY"></param>
        /// <returns></returns>
        private static NumericUpDown SetNumericUpDown(string englishName, string dataType, string formMin, string formMax, int locationX, int locationY)
        {
            NumericUpDown numericUpDown = new NumericUpDown();
            numericUpDown.Name = $"numericUpDown{englishName}";
            //numericUpDown.Value = DateTime.Now;
            numericUpDown.Location = new Point(locationX, locationY);
            numericUpDown.Size = new Size(200, 121);
            decimal decMin;
            decimal decMax;
            decimal.TryParse(formMin, out decMin);
            decimal.TryParse(formMax, out decMax);
            numericUpDown.Minimum = decMin;
            if (decMax > decMin)
            {
                numericUpDown.Maximum = decMax;
            }
            //如果是int类型
            if (dataType != null && dataType.IndexOf("int", StringComparison.CurrentCultureIgnoreCase) >= 0)
            {
                numericUpDown.DecimalPlaces = 0;
            }
            else if (dataType != null && dataType.IndexOf("decimal", StringComparison.CurrentCultureIgnoreCase) >= 0)
            {
                //decimal设置为4位小数
                numericUpDown.DecimalPlaces = 4;
            }
            else
            {
                //float,double设置为2位小数
                numericUpDown.DecimalPlaces = 2;
            }

            //NumericUpDown控件的Tag特殊处理,使用二元组,第一个项代表绑定的列名(字段名),第二个项代表数据类型
            numericUpDown.Tag = Tuple.Create(englishName, dataType);//增加控件 绑定的列名(字段名)
            return numericUpDown;
        }
    }

    /// <summary>
    /// 表单(字段)对应的控件类型
    /// </summary>
    public enum FormCategory
    {
        /// <summary>
        /// 自增或者默认当前时间的字段显示
        /// </summary>
        None = -1,
        /// <summary>
        /// 文本框,比如条码
        /// </summary>
        TextBox = 0,
        /// <summary>
        /// 组合框,下拉等选项
        /// </summary>
        ComboBox = 1,
        /// <summary>
        /// 单选按钮控件
        /// </summary>
        RadioButton = 2,
        /// <summary>
        /// 日期时间字段
        /// </summary>
        DateTimePicker = 3,
        /// <summary>
        /// 数字显示框
        /// </summary>
        NumericUpDown = 4
    }
}

辅助类DataGridViewUtil.cs,源程序

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

namespace AutoGenerateForm
{
    /// <summary>
    /// 将Excel的配置映射到一个内存数据表中,然后为DataGridView设置数据绑定,以及自动生成列
    /// </summary>
    public class DataGridViewUtil
    {
        /// <summary>
        /// 初始化设置DataGridView的列和相关数据绑定
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="dgvw"></param>
        public static void DataGridViewIni(DataTable dt, ref DataGridView dgvw)
        {
            dgvw.Columns.Clear();
            string tableName = dt.TableName;
            try
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    if (!dt.Columns.Contains("En_Name"))
                    {
                        MessageBox.Show($"{tableName}表的UI配置文件错误,请检查--不存在En_Name列");
                        return;
                    }
                    if (!dt.Columns.Contains("Another_Name"))
                    {
                        MessageBox.Show($"{tableName}表的UI配置文件错误,请检查--不存在Another_Name列");
                        return;
                    }
                    if (!dt.Columns.Contains("Use"))
                    {
                        MessageBox.Show($"{tableName}表的UI配置文件错误,请检查--不存在Use列");
                        return;
                    }
                    if (!dt.Columns.Contains("Table_Name"))
                    {
                        MessageBox.Show($"{tableName}表的UI配置文件错误,请检查--不存在Table_Name列");
                        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;
                    }

                    for (int i = 0; i < columnCount; i++)
                    {
                        string isUse = dt.Rows[i]["Use"].ToString().Trim();
                        if (isUse != "1")
                        {
                            continue;//不添加
                        }
                        string columnName = dt.Rows[i]["En_Name"].ToString();
                        string columnDescription = dt.Rows[i]["Another_Name"].ToString();
                        DataGridViewColumn dgvc = new DataGridViewColumn();
                        dgvc.CellTemplate = new DataGridViewTextBoxCell();
                        dgvc.Name = "dgvc_" + columnName;
                        int minWidth = 50;
                        bool rtn = int.TryParse(dt.Rows[i]["Min_Width"].ToString(), out minWidth);
                        if (!rtn)
                        {
                            minWidth = 50;
                        }
                        dgvc.MinimumWidth = minWidth;
                        dgvc.DataPropertyName = columnName;
                        dgvc.HeaderText = (columnDescription == null || columnDescription.Trim().Length == 0) ? columnName : columnDescription;
                        dgvw.Columns.Add(dgvc);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"{tableName}表的UI配置文件错误,请检查:" + ex.Message);
            }
            return;
        }
    }
}

辅助模板类GenerateUtil.cs,源程序

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

namespace AutoGenerateForm
{
    /// <summary>
    /// 自动生成相关操作
    /// </summary>
    public class GenerateUtil
    {
        /// <summary>
        /// 获取表的字段类型信息 标记字段是主键字段、条码字段等
        /// </summary>
        /// <param name="xlsfilePath"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static List<CSVTitleName> GetFieldInfo(string xlsfilePath, string tableName)
        {
            List<CSVTitleName> list = new List<CSVTitleName>();
            DataTable dt = NpoiExcelOperateUtil.ExcelToTable(xlsfilePath, tableName);
            if (dt == null || dt.Rows.Count == 0)
            {
                MessageBox.Show($"{tableName}没有配置表的任何数据");
                return list;
            }

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

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                //如果包含 字段类型列,就读取字段类型 斯内科 20210129
                string fieldCategory = "Normal";
                if (dt.Columns.Contains("Field_Category") && !string.IsNullOrEmpty(dr["Field_Category"].ToString()))
                {
                    fieldCategory = dr["Field_Category"].ToString();
                }

                CSVTitleName csvTitleName = new CSVTitleName()
                {
                    ChineseName = dr["Ch_Name"].ToString(),
                    EnglishName = dr["En_Name"].ToString(),
                    DataType = dr["Data_Type"].ToString(),
                    FieldCategory = fieldCategory
                };
                list.Add(csvTitleName);
            }
            return list;
        }

        /// <summary>
        /// 获得CRUD文件内容:配置类名(表名)、条码字段、主键字段
        /// </summary>
        /// <param name="className">类名(表名)</param>
        /// <param name="fieldBarcode">条码字段</param>
        /// <param name="fieldPrimaryKey">主键字段</param>
        /// <returns></returns>
        public static string GetCRUDFileContent(string className, string fieldBarcode, string fieldPrimaryKey)
        {
            return $@"using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using SqlSugar;
using AutoGenerateForm.Models;

namespace AutoGenerateForm.CRUD
{{
    public class CRUD_{className}
    {{
        public static int Insert({className} {className}Obj)
        {{
            //添加一条数据
            using (var db = SugarDao.GetInstance())
            {{
                return db.Insertable({className}Obj).ExecuteCommand();
            }}
        }} 
        public static int Update({className} {className}Obj, int coreId)
        {{
            //更新一条数据 按照 主键
            using (var db = SugarDao.GetInstance())
            {{
                return db.Updateable({className}Obj).Where(tObj => tObj.{fieldPrimaryKey} == coreId).ExecuteCommand();
            }}
        }}

        public static void DeleteByBarcode(string barcode)
        {{
            //根据条码删除
            using (var db = SugarDao.GetInstance())
            {{
                db.Deleteable<{className}>(it => it.{fieldBarcode} == barcode).ExecuteCommand();
            }}
        }}
        public static void DeleteByCoreId(int coreId)
        {{
            //根据主键删除
            using (var db = SugarDao.GetInstance())
            {{
                db.Deleteable<{className}>(it => it.{fieldPrimaryKey} == coreId).ExecuteCommand();
            }}
        }}
        public static DataTable SelectByBarcode(string barcode)
        {{
            DataTable dataTable = new DataTable(""xxx"");
            using (var db = SugarDao.GetInstance())
            {{
                var dataS = db.Queryable<{className}>().Where(it => it.{fieldBarcode} == barcode);
                dataTable = dataS.ToDataTable();
            }}
            return dataTable;
        }}
        public static DataTable SelectByCoreId(int coreId)
        {{
            DataTable dataTable = new DataTable(""xxx"");
            using (var db = SugarDao.GetInstance())
            {{
                var dataS = db.Queryable<{className}>().Where(it => it.{fieldPrimaryKey} == coreId);
                dataTable = dataS.ToDataTable();
            }}
            return dataTable;
        }}
    }}
}}";
        }

        /// <summary>
        /// 获取窗体【编辑】的设计器的文件内容
        /// </summary>
        /// <param name="className"></param>
        /// <returns></returns>
        public static string GetFormDesignerContent(string className)
        {
            return $@"namespace AutoGenerateForm.Forms
{{
    partial class Frm{className}Edit
    {{
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name=""disposing"">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {{
            if (disposing && (components != null))
            {{
                components.Dispose();
            }}
            base.Dispose(disposing);
        }}

        #region Windows Form Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {{
            this.btnSave = new System.Windows.Forms.Button();
            this.btnCancel = new System.Windows.Forms.Button();
            this.SuspendLayout();
            // 
            // btnSave
            // 
            this.btnSave.Font = new System.Drawing.Font(""宋体"", 13F);
            this.btnSave.Location = new System.Drawing.Point(543, 527);
            this.btnSave.Name = ""btnSave"";
            this.btnSave.Size = new System.Drawing.Size(73, 41);
            this.btnSave.TabIndex = 0;
            this.btnSave.Text = ""保存"";
            this.btnSave.UseVisualStyleBackColor = true;
            this.btnSave.Click += new System.EventHandler(this.btnSave_Click);
            // 
            // btnCancel
            // 
            this.btnCancel.Font = new System.Drawing.Font(""宋体"", 13F);
            this.btnCancel.Location = new System.Drawing.Point(653, 527);
            this.btnCancel.Name = ""btnCancel"";
            this.btnCancel.Size = new System.Drawing.Size(73, 41);
            this.btnCancel.TabIndex = 1;
            this.btnCancel.Text = ""关闭"";
            this.btnCancel.UseVisualStyleBackColor = true;
            this.btnCancel.Click += new System.EventHandler(this.btnCancel_Click);
            // 
            // Frm{className}Edit
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(800, 650);
            this.Controls.Add(this.btnCancel);
            this.Controls.Add(this.btnSave);
            this.Name = ""Frm{className}Edit"";
            this.Text = ""Frm{className}Edit"";
            this.Load += new System.EventHandler(this.Frm{className}Edit_Load);
            this.ResumeLayout(false);

        }}

        #endregion

        private System.Windows.Forms.Button btnSave;
        private System.Windows.Forms.Button btnCancel;
    }}
}}";
        }

        /// <summary>
        /// 获取窗体【编辑】的文件内容
        /// </summary>
        /// <param name="className"></param>
        /// <param name="fieldPrimaryKey"></param>
        /// <returns></returns>
        public static string GetFormMainContent(string className, string fieldPrimaryKey)
        {
            return $@"using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AutoGenerateForm.Forms
{{
    public partial class Frm{className}Edit : Form
    {{
        /// <summary>
        /// 添加(insert) 还是 修改(update)表单
        /// </summary>
        bool isAdd = true;
        int coreId = -1;
        DataTable dt = new DataTable(""FormDesign"");
        /// <summary>
        /// 表名 或者 Excel工作簿名
        /// </summary>
        string CurrentTableName = ""test_msg"";

        public Frm{className}Edit(string sheetName)
        {{
            InitializeComponent();
            isAdd = true;
            CurrentTableName = sheetName;

            dt = NpoiExcelOperateUtil.ExcelToTable(AppDomain.CurrentDomain.BaseDirectory + ""Conf\\DataGridViewConf_Form.xls"", sheetName);
        }}

        public Frm{className}Edit(int _coreId, string sheetName) : this(sheetName)
        {{
            coreId = _coreId;
            isAdd = false;
        }}

        private void Frm{className}Edit_Load(object sender, EventArgs e)
        {{
            AutoGenerateUtil.InitialCreateControls(dt, this);

            //如果是修改表单,则从数据库中读取数据行,并为文本框赋值
            if (!isAdd)
            {{
                DataTable dataTable = AutoGenerateForm.CRUD.CRUD_{className}.SelectByCoreId(coreId);
                if (dataTable == null || dataTable.Rows.Count < 1)
                {{
                    MessageBox.Show($""没有找到符合条件的记录,该行数据不存在或者已经被删除,当前编号【{{coreId}}】"");
                    return;
                }}
                AutoGenerateUtil.BindControlValue(dataTable, this);
            }}
        }}

        private void btnSave_Click(object sender, EventArgs e)
        {{
            try
            {{
                Type type = typeof(AutoGenerateForm.Models.{className});
                //调用实例化方法(非静态方法)需要创建类型的一个实例
                object instanceObject = Activator.CreateInstance(type);
                //遍历所有可交互控件,获取控件的Tag以及对应的值,并为属性赋值
                AutoGenerateUtil.SetInstanceObjectPropertyValues(type, instanceObject, this);

                AutoGenerateForm.Models.{className} {className}Obj = instanceObject as AutoGenerateForm.Models.{className};
                int affectCount = -1;
                if (isAdd)
                {{
                    affectCount = AutoGenerateForm.CRUD.CRUD_{className}.Insert({className}Obj);
                }}
                else
                {{
                    {className}Obj.{fieldPrimaryKey} = coreId;
                    affectCount = AutoGenerateForm.CRUD.CRUD_{className}.Update({className}Obj, coreId);
                }}
                MessageBox.Show($""保存【{{(isAdd ? ""添加"" : ""更新"")}}】表【{{CurrentTableName}}】信息成功,受影响的行数【{{affectCount}}】"");
                this.DialogResult = DialogResult.OK;
            }}
            catch (Exception ex)
            {{
                MessageBox.Show($""保存【{{(isAdd ? ""添加"" : ""更新"")}}】表信息【{{CurrentTableName}}】失败,异常信息:\n{{ex.Message}}"");
            }}
        }}

        private void btnCancel_Click(object sender, EventArgs e)
        {{
            this.DialogResult = DialogResult.Cancel;
            this.Close();
        }}
    }}
}}";
        }

        /// <summary>
        /// 获取窗体【列表】的设计器的文件内容
        /// </summary>
        /// <param name="className"></param>
        /// <returns></returns>
        public static string GetListFormDesignerContent(string className)
        {
            return $@"namespace AutoGenerateForm.Forms
{{
    partial class Frm{className}List
    {{
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name=""disposing"">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {{
            if (disposing && (components != null))
            {{
                components.Dispose();
            }}
            base.Dispose(disposing);
        }}

        #region Windows Form Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {{
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle5 = new System.Windows.Forms.DataGridViewCellStyle();
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle6 = new System.Windows.Forms.DataGridViewCellStyle();
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle7 = new System.Windows.Forms.DataGridViewCellStyle();
            System.Windows.Forms.DataGridViewCellStyle dataGridViewCellStyle8 = new System.Windows.Forms.DataGridViewCellStyle();
            this.btnQuery = new System.Windows.Forms.Button();
            this.txbBarcode_query = new System.Windows.Forms.TextBox();
            this.lblCondition = new System.Windows.Forms.Label();
            this.btnAdd = new System.Windows.Forms.Button();
            this.dgw_Data = new System.Windows.Forms.DataGridView();
            ((System.ComponentModel.ISupportInitialize)(this.dgw_Data)).BeginInit();
            this.SuspendLayout();
            // 
            // btnQuery
            // 
            this.btnQuery.BackColor = System.Drawing.SystemColors.GradientActiveCaption;
            this.btnQuery.Font = new System.Drawing.Font(""宋体"", 10.5F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
            this.btnQuery.Location = new System.Drawing.Point(436, 25);
            this.btnQuery.Name = ""btnQuery"";
            this.btnQuery.Size = new System.Drawing.Size(73, 26);
            this.btnQuery.TabIndex = 211;
            this.btnQuery.Text = ""查 询"";
            this.btnQuery.UseVisualStyleBackColor = false;
            this.btnQuery.Click += new System.EventHandler(this.btnQuery_Click);
            // 
            // txbBarcode_query
            // 
            this.txbBarcode_query.Font = new System.Drawing.Font(""宋体"", 10.5F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
            this.txbBarcode_query.Location = new System.Drawing.Point(86, 23);
            this.txbBarcode_query.Name = ""txbBarcode_query"";
            this.txbBarcode_query.Size = new System.Drawing.Size(340, 23);
            this.txbBarcode_query.TabIndex = 209;
            // 
            // lblCondition
            // 
            this.lblCondition.AutoSize = true;
            this.lblCondition.Font = new System.Drawing.Font(""宋体"", 10.5F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
            this.lblCondition.ForeColor = System.Drawing.SystemColors.ControlText;
            this.lblCondition.Location = new System.Drawing.Point(32, 27);
            this.lblCondition.Name = ""lblCondition"";
            this.lblCondition.Size = new System.Drawing.Size(49, 14);
            this.lblCondition.TabIndex = 210;
            this.lblCondition.Text = ""关键字"";
            // 
            // btnAdd
            // 
            this.btnAdd.BackColor = System.Drawing.SystemColors.GradientActiveCaption;
            this.btnAdd.Font = new System.Drawing.Font(""宋体"", 10.5F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
            this.btnAdd.Location = new System.Drawing.Point(547, 25);
            this.btnAdd.Name = ""btnAdd"";
            this.btnAdd.Size = new System.Drawing.Size(73, 26);
            this.btnAdd.TabIndex = 212;
            this.btnAdd.Text = ""添 加"";
            this.btnAdd.UseVisualStyleBackColor = false;
            this.btnAdd.Click += new System.EventHandler(this.btnAdd_Click);
            // 
            // dgw_Data
            // 
            this.dgw_Data.AllowUserToAddRows = false;
            this.dgw_Data.AllowUserToDeleteRows = false;
            dataGridViewCellStyle5.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(231)))), ((int)(((byte)(246)))), ((int)(((byte)(253)))));
            this.dgw_Data.AlternatingRowsDefaultCellStyle = dataGridViewCellStyle5;
            this.dgw_Data.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.AllCells;
            this.dgw_Data.BackgroundColor = System.Drawing.SystemColors.Window;
            this.dgw_Data.BorderStyle = System.Windows.Forms.BorderStyle.Fixed3D;
            this.dgw_Data.ColumnHeadersBorderStyle = System.Windows.Forms.DataGridViewHeaderBorderStyle.Single;
            dataGridViewCellStyle6.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleCenter;
            dataGridViewCellStyle6.BackColor = System.Drawing.Color.FromArgb(((int)(((byte)(247)))), ((int)(((byte)(246)))), ((int)(((byte)(239)))));
            dataGridViewCellStyle6.Font = new System.Drawing.Font(""宋体"", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
            dataGridViewCellStyle6.ForeColor = System.Drawing.SystemColors.WindowText;
            dataGridViewCellStyle6.SelectionBackColor = System.Drawing.SystemColors.Highlight;
            dataGridViewCellStyle6.SelectionForeColor = System.Drawing.SystemColors.HighlightText;
            dataGridViewCellStyle6.WrapMode = System.Windows.Forms.DataGridViewTriState.True;
            this.dgw_Data.ColumnHeadersDefaultCellStyle = dataGridViewCellStyle6;
            this.dgw_Data.ColumnHeadersHeight = 25;
            this.dgw_Data.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.DisableResizing;
            dataGridViewCellStyle7.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleLeft;
            dataGridViewCellStyle7.BackColor = System.Drawing.Color.White;
            dataGridViewCellStyle7.Font = new System.Drawing.Font(""宋体"", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
            dataGridViewCellStyle7.ForeColor = System.Drawing.Color.Black;
            dataGridViewCellStyle7.SelectionBackColor = System.Drawing.Color.FromArgb(((int)(((byte)(59)))), ((int)(((byte)(188)))), ((int)(((byte)(240)))));
            dataGridViewCellStyle7.SelectionForeColor = System.Drawing.Color.White;
            dataGridViewCellStyle7.WrapMode = System.Windows.Forms.DataGridViewTriState.False;
            this.dgw_Data.DefaultCellStyle = dataGridViewCellStyle7;
            this.dgw_Data.EnableHeadersVisualStyles = false;
            this.dgw_Data.Font = new System.Drawing.Font(""宋体"", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(134)));
            this.dgw_Data.Location = new System.Drawing.Point(3, 74);
            this.dgw_Data.Name = ""dgw_Data"";
            this.dgw_Data.ReadOnly = true;
            this.dgw_Data.RowHeadersBorderStyle = System.Windows.Forms.DataGridViewHeaderBorderStyle.None;
            this.dgw_Data.RowHeadersWidthSizeMode = System.Windows.Forms.DataGridViewRowHeadersWidthSizeMode.AutoSizeToDisplayedHeaders;
            dataGridViewCellStyle8.Alignment = System.Windows.Forms.DataGridViewContentAlignment.MiddleLeft;
            dataGridViewCellStyle8.ForeColor = System.Drawing.SystemColors.WindowText;
            dataGridViewCellStyle8.SelectionBackColor = System.Drawing.SystemColors.Highlight;
            dataGridViewCellStyle8.SelectionForeColor = System.Drawing.SystemColors.HighlightText;
            this.dgw_Data.RowsDefaultCellStyle = dataGridViewCellStyle8;
            this.dgw_Data.RowTemplate.Height = 23;
            this.dgw_Data.Size = new System.Drawing.Size(920, 454);
            this.dgw_Data.CellContentClick += new System.Windows.Forms.DataGridViewCellEventHandler(this.dgw_Data_CellContentClick);
            // 
            // Frm{className}List
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(932, 540);
            this.Controls.Add(this.dgw_Data);
            this.Controls.Add(this.btnAdd);
            this.Controls.Add(this.btnQuery);
            this.Controls.Add(this.txbBarcode_query);
            this.Controls.Add(this.lblCondition);
            this.Name = ""Frm{className}List"";
            this.Text = ""Frm{className}List"";
            this.Load += new System.EventHandler(this.Frm{className}List_Load);
            ((System.ComponentModel.ISupportInitialize)(this.dgw_Data)).EndInit();
            this.ResumeLayout(false);
            this.PerformLayout();

        }}

        #endregion

        private System.Windows.Forms.Button btnQuery;
        public System.Windows.Forms.TextBox txbBarcode_query;
        private System.Windows.Forms.Label lblCondition;
        private System.Windows.Forms.Button btnAdd;
        private System.Windows.Forms.DataGridView dgw_Data;
    }}
}}";
        }

        /// <summary>
        /// 获取窗体【列表】的文件内容
        /// </summary>
        /// <param name="className"></param>
        /// <param name="fieldBarcode"></param>
        /// <param name="fieldPrimaryKey"></param>
        /// <returns></returns>
        public static string GetListFormMainContent(string className, string fieldBarcode, string fieldPrimaryKey)
        {
            return $@"using AutoGenerateForm.CRUD;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace AutoGenerateForm.Forms
{{
    public partial class Frm{className}List : Form
    {{
        /// <summary>
        /// 表Excel设计器
        /// </summary>
        DataTable dt = new DataTable(""FormDesign"");
        /// <summary>
        /// 表名 或者 Excel工作簿名
        /// </summary>
        string CurrentTableName = ""test_msg"";
        /// <summary>
        /// 所有英文名称集合【列集合】,以英文逗号(,)拼接
        /// </summary>
        string selectItems = string.Empty;
        public Frm{className}List(string sheetName)
        {{
            InitializeComponent();
            CurrentTableName = sheetName;

            dt = NpoiExcelOperateUtil.ExcelToTable(AppDomain.CurrentDomain.BaseDirectory + ""Conf\\DataGridViewConf_Form.xls"", sheetName);

            //所有英文名称集合【列集合】
            string[] DataPropertyNameArr = new string[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {{
                DataPropertyNameArr[i] = dt.Rows[i][""En_Name""].ToString();
            }}
            selectItems = string.Join("","", DataPropertyNameArr);

            AutoGenerateUtil.InitialDataGridViewColumns(dt, dgw_Data);
        }}

        private void Frm{className}List_Load(object sender, EventArgs e)
        {{
            btnQuery_Click(null, e);
        }}

        private void btnQuery_Click(object sender, EventArgs e)
        {{
            AutoGenerateUtil.BindDataList(txbBarcode_query.Text.Trim(), ""{fieldBarcode}"", selectItems, CurrentTableName, dgw_Data);
        }}

        private void btnAdd_Click(object sender, EventArgs e)
        {{
            Frm{className}Edit frm{className}Edit = new Frm{className}Edit(CurrentTableName);
            DialogResult dialog = frm{className}Edit.ShowDialog();
            if (dialog == DialogResult.OK)
            {{
                btnQuery_Click(null, e);//刷新
            }}
        }}

        private void dgw_Data_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {{
            if (e.RowIndex < 0 || e.ColumnIndex < 0)
            {{
                return;
            }}
            int coreId = Convert.ToInt32(dgw_Data[""dgvc_{fieldPrimaryKey}"", e.RowIndex].Value);
            if (dgw_Data.Columns[e.ColumnIndex].Name == ""dgvcEdit"") //编辑操作
            {{
                Frm{className}Edit frm{className}Edit = new Frm{className}Edit(coreId, CurrentTableName);
                DialogResult dialog = frm{className}Edit.ShowDialog();
                if (dialog == DialogResult.OK)
                {{
                    btnQuery_Click(null, e);//刷新
                }}
            }}
            else if (dgw_Data.Columns[e.ColumnIndex].Name == ""dgvcDelete"") //删除操作
            {{
                DialogResult dialog = MessageBox.Show($""您确定要删除该条记录吗?一经删除,不可恢复!\n当前编号【{{coreId}}】"", ""警告"", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
                if (dialog != DialogResult.Yes)
                {{
                    return;
                }}
                try
                {{
                    CRUD_{className}.DeleteByCoreId(coreId);
                    btnQuery_Click(null, e);
                }}
                catch (Exception ex)
                {{
                    MessageBox.Show(ex.Message, ""删除时出现异常"");
                }}
            }}
        }}
    }}
}}";
        }
    }
}

默认的窗体FormGenerateClass.cs设计器修改下:

窗体的主要代码 如下:

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 AutoGenerateForm
{
    public partial class FormGenerateClass : Form
    {
        public FormGenerateClass()
        {
            InitializeComponent();
        }

        private void btnGenerate_Click(object sender, EventArgs e)
        {
            rtxbClassContent.Clear();
            if (cboTable.SelectedIndex == -1)
            {
                cboTable.Focus();
                DisplayInfo("没有选择相应的表名,请检查...");                
                return;
            }
            string tableName = Convert.ToString(cboTable.SelectedValue);
            string tableComment = cboTable.Text;
            DisplayInfo($"已选择表【{tableName}】,准备自动生成实体类...");
            string sql = $@"SELECT TABLE_NAME as TableName, 
                     column_name AS DbColumnName,
                     CASE WHEN  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1)='' THEN COLUMN_TYPE ELSE  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1) END AS DataType,
                     CAST(SUBSTRING(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)+1,LOCATE(')',COLUMN_TYPE)-LOCATE('(',COLUMN_TYPE)-1) AS signed) AS Length,
                     column_default  AS  `DefaultValue`,
                     column_comment  AS  `ColumnComment`,
                     CASE WHEN COLUMN_KEY = 'PRI' THEN true ELSE false END AS `IsPrimaryKey`,
                     CASE WHEN EXTRA='auto_increment' THEN true ELSE false END as IsIdentity,
                     CASE WHEN is_nullable = 'YES' THEN true ELSE false END AS `IsNullable`
                     FROM Information_schema.columns where TABLE_NAME='{tableName}' and  TABLE_SCHEMA=(select database()) ORDER BY TABLE_NAME";
            DataTable dataTable = RawSql.GetDataTable(sql, null);
            if (dataTable == null || dataTable.Rows.Count == 0)
            {
                DisplayInfo($"没有找到表【{tableName}】的字段(列)信息...");
                return;
            }

            string fileDirectory = AppDomain.CurrentDomain.BaseDirectory + @"AutoGenerateCodes\Models";//生成到的路径
            string filePath = $"{Path.Combine(fileDirectory, tableName)}.cs";
            string text = FileContentUtil.GetFileContent(tableName, tableComment, dataTable);
            FileContentUtil.CreateFile(filePath, text, Encoding.UTF8);
            DisplayInfo($"自动生成实体类【{tableName}】成功.实体类路径:{filePath}");
            rtxbClassContent.AppendText(text);

            //是否启动自动生成CRUD、窗体类
            if (chkAutoGenerate.Checked)
            {
                //自动生成CRUD类.cs
                //查找字段类型信息 
                List<CSVTitleName> list = GenerateUtil.GetFieldInfo(AppDomain.CurrentDomain.BaseDirectory + "Conf\\DataGridViewConf_Form.xls", tableName);
                if (list == null || list.Count == 0)
                {
                    DisplayInfo($"自动生成CRUD类【CRUD_{tableName}】失败.没有找到对应表的配置信息");
                    return;
                }
                //查找到条码字段
                CSVTitleName csvBarcode = list.Find(csvName => csvName.FieldCategory.IndexOf("BarcodeField", StringComparison.CurrentCultureIgnoreCase) >= 0);
                if (csvBarcode == null)
                {
                    DisplayInfo($"自动生成CRUD类【CRUD_{tableName}】失败.没有配置条码字段【BarcodeField】");
                    return;
                }
                //查找到主键字段
                CSVTitleName csvPrimaryKey = list.Find(csvName => csvName.FieldCategory.IndexOf("PrimaryKey", StringComparison.CurrentCultureIgnoreCase) >= 0);
                if (csvBarcode == null)
                {
                    DisplayInfo($"自动生成CRUD类【CRUD_{tableName}】失败.没有配置主键字段【PrimaryKey】");
                    return;
                }
                string crudFileText = GenerateUtil.GetCRUDFileContent(tableName, csvBarcode.EnglishName, csvPrimaryKey.EnglishName);
                fileDirectory = AppDomain.CurrentDomain.BaseDirectory + @"AutoGenerateCodes\CRUD";
                filePath = Path.Combine(fileDirectory, $"CRUD_{tableName}.cs");
                FileContentUtil.CreateFile(filePath, crudFileText, Encoding.UTF8);
                DisplayInfo($"自动生成CRUD类【CRUD_{tableName}】成功.CRUD操作类路径:{filePath}");

                //自动生成【编辑】窗体:添加、更新
                string formDesignerText = GenerateUtil.GetFormDesignerContent(tableName);
                fileDirectory = AppDomain.CurrentDomain.BaseDirectory + @"AutoGenerateCodes\Forms";
                filePath = Path.Combine(fileDirectory, $"Frm{tableName}Edit.Designer.cs");
                FileContentUtil.CreateFile(filePath, formDesignerText, Encoding.UTF8);
                DisplayInfo($"自动生成窗体【设计器】类【Frm{tableName}Edit】成功.窗体编辑类路径:{filePath}");

                string formMainText = GenerateUtil.GetFormMainContent(tableName, csvPrimaryKey.EnglishName);
                filePath = Path.Combine(fileDirectory, $"Frm{tableName}Edit.cs");
                FileContentUtil.CreateFile(filePath, formMainText, Encoding.UTF8);
                DisplayInfo($"自动生成窗体类【Frm{tableName}Edit】成功.窗体编辑类路径:{filePath}");

                //自动生成【列表】窗体:查询、删除
                string listDesignerText = GenerateUtil.GetListFormDesignerContent(tableName);
                filePath = Path.Combine(fileDirectory, $"Frm{tableName}List.Designer.cs");
                FileContentUtil.CreateFile(filePath, listDesignerText, Encoding.UTF8);
                DisplayInfo($"自动生成窗体【设计器】类【Frm{tableName}List】成功.窗体列表类路径:{filePath}");

                string listMainText = GenerateUtil.GetListFormMainContent(tableName, csvBarcode.EnglishName, csvPrimaryKey.EnglishName);
                filePath = Path.Combine(fileDirectory, $"Frm{tableName}List.cs");
                FileContentUtil.CreateFile(filePath, listMainText, Encoding.UTF8);
                DisplayInfo($"自动生成窗体类【Frm{tableName}List】成功.窗体列表类路径:{filePath}");
            }
        }

        private void FormGenerateClass_Load(object sender, EventArgs e)
        {
            string sqlAllTables = "SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,CREATE_TIME,TABLE_COMMENT FROM information_schema.`TABLES` where TABLE_SCHEMA = (select database())";
            DataTable dataTable = RawSql.GetDataTable(sqlAllTables, null);
            BindingSource bindingSource = new BindingSource();
            bindingSource.DataSource = dataTable;
            cboTable.DataSource = bindingSource;
            cboTable.ValueMember = "TABLE_NAME";
            cboTable.DisplayMember = "TABLE_COMMENT";
        }

        /// <summary>
        /// 显示文本框的消息
        /// </summary>
        /// <param name="message"></param>
        private void DisplayInfo(string message)
        {
            this.BeginInvoke(new Action(() =>
            {
                if (rtxbResult.TextLength >= 20480)
                {
                    rtxbResult.Clear();
                }
                rtxbResult.AppendText($"{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")} --> {message}\n");
                rtxbResult.ScrollToCaret();
            }));
        }

        private void btnTest_Click(object sender, EventArgs e)
        {
            string sheetName = Convert.ToString(cboTable.SelectedValue);
            switch (sheetName)
            {
                case "input_output_log":
                    AutoGenerateForm.Forms.Frminput_output_logList listForm1 = new AutoGenerateForm.Forms.Frminput_output_logList(sheetName);
                    listForm1.Show();
                    break;
                case "input_output_msg":
                    AutoGenerateForm.Forms.Frminput_output_msgList listForm2 = new AutoGenerateForm.Forms.Frminput_output_msgList(sheetName);
                    listForm2.Show();
                    break;
                case "pallet_bind_pack":
                    AutoGenerateForm.Forms.Frmpallet_bind_packList listForm3 = new AutoGenerateForm.Forms.Frmpallet_bind_packList(sheetName);
                    listForm3.Show();
                    break;
            }
            
        }
    }
}

程序运行如图:

将自动生成的,在应用程序目录下:AutoGenerateCodes目录下的CRUD、Forms、Models的三个目录及文件放在 AutoGenerateForm应用程序下。

点击”测试“按钮:

  • 0
    点赞
  • 4
    评论
  • 1
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值