读取Execl中读取数据并修改Mysql中表的注释和字段的注释

一时失言乱红尘 2022-05-27 22:12 171阅读 0赞

**我这里用com的方式去做，还有其他方法不再列举**

**1、新建一个任意类型的项目。安装Microsoft.Office.Interop.Excel，这种方式依赖于office，电脑上要装相应版本的office**  
![这里写图片描述][70]

我电脑上装了office专业版，MySql.Data不要选最新的，

2、引用程序集**using Microsoft.Office.Interop.Excel;**

文件夹路径：写在配置文件里，容易改

遍历Excel文件夹，

/// <summary>
            /// 获取文件夹下Excel文件,并打开操作
            /// </summary>
            /// <param name="filesPath">文件夹路径</param>
            public void GetExlFiles(string filesPath)
            {
                if (!Directory.Exists(filesPath))
                {
                    Directory.CreateDirectory(filesPath);
                }
                try
                {
                    DirectoryInfo directoryInfo = new DirectoryInfo(filesPath);
                    FileInfo[] files = directoryInfo.GetFiles();
                    //如果files存在，遍历
                    if (files.Length > 0)
                    {
                        foreach (var item in files)
                        {
                            //F:\HR-Link-Work\Pro\ProCommets\ProCommets\bin\ExcelFiles\tran_entity_logic_fields.xlsx
                            ReadExcel(item.FullName);
                        }
                    }
    
                    DirectoryInfo[] directoryInfoFolder = directoryInfo.GetDirectories();
                    //如果主文件夹下，还有文件夹，递归
                    if (directoryInfoFolder.Length > 0)
                    {
                        foreach (DirectoryInfo dir in directoryInfoFolder)
                        {
                            GetExlFiles(dir.FullName);
                        }
                    }
    
                }
                catch (Exception ex)
                {
    
                    throw ex;
                }
            }
            #endregion

**3、四个查询sql的方法**

#region Dal
            /// <summary>
            /// 查找表的comments
            /// </summary>
            /// <param name="tableName">表名</param>
            /// <returns></returns>
            public DataSet FindTableComment(string tableName)
            {
                try
                {
                    using (MySqlConnection con = new MySqlConnection(ConfigValue._conStr))
                    {
                        string sql = "select * from information_schema.TABLES WHERE `TABLE_NAME`='" + tableName + "'";
                        //string sql = "SELECT * FROM information_schema.TABLES WHERE `TABLE_NAME`=@tableName";
    
                        con.Open();
                        using (MySqlCommand cmd = new MySqlCommand(sql, con))
                        {
                            using (MySqlDataAdapter ad = new MySqlDataAdapter(sql, ConfigValue._conStr))
                            {
                                DataSet ds = new DataSet();
                                ad.Fill(ds);
                                return ds;
    
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
            /// <summary>
            /// 查询表中每个字段的comment
            /// </summary>
            /// <param name="tableName">表名</param>
            /// <returns></returns>
            public DataSet FindTableFiledsComment(string tableName)
            {
                try
                {
                    using (MySqlConnection con = new MySqlConnection(ConfigValue._conStr))
                    {
                        string sql = "select * from information_schema.COLUMNS WHERE `TABLE_NAME`='" + tableName + "'";
    
                        con.Open();
                        using (MySqlCommand cmd = new MySqlCommand(sql, con))
                        {
                            using (MySqlDataAdapter ad = new MySqlDataAdapter(sql, ConfigValue._conStr))
                            {
                                DataSet ds = new DataSet();
                                ad.Fill(ds);
                                return ds;
    
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
            /// <summary>
            /// 更新表的注释
            /// </summary>
            /// <param name="tableName">表名</param>
            /// <param name="comment">注释</param>
            /// <returns></returns>
            public int UpdateTableComment(string tableName, string comment)
            {
                try
                {
                    string sql = "alter table " + tableName + " comment '" + comment + "'";
                    using (MySqlConnection con = new MySqlConnection(ConfigValue._conStr))
                    {
                        con.Open();
                        using (MySqlCommand cmd = new MySqlCommand(sql, con))
                        {
                            return cmd.ExecuteNonQuery();
    
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
    
            }
    
            /// <summary>
            /// 批量更新表字段的注释：alter table view_define_action modify column key_id int comment 'key_id'
            /// </summary>
            /// <param name="sqlList">一组修改字段注释的字符串</param>
            public void UpdateTableFieldsComment(List<string> sqlList)
            {
                try
                {
                    using (MySqlConnection con = new MySqlConnection(ConfigValue._conStr))
                    {
                        con.Open();
                        MySqlCommand cmd = new MySqlCommand();
                        cmd.Connection = con;
                        MySqlTransaction tx = con.BeginTransaction();
                        cmd.Transaction = tx;
                        try
                        {
                            for (int i = 0; i < sqlList.Count; i++)
                            {
                                string strsql = sqlList[i].ToString();
                                if (strsql.Trim().Length > 1)
                                {
                                    cmd.CommandText = strsql;
                                    cmd.ExecuteNonQuery();
                                }
                            }
                            tx.Commit();
                        }
                        catch (System.Data.SqlClient.SqlException ex)
                        {
    
                            tx.Rollback();
                            throw new Exception(ex.Message);
                        }
                        cmd.Dispose();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
    
            #endregion

**4、替换表名注释**

/// <summary>
            /// 获取表的注释,有注释不操作，是空的话就更新
            /// </summary>
            /// <param name="tableName">表名</param>
            /// <param name="txt">表注释</param>
            public void GetTableCommentsUpdate(string tableName, string txt)
            {
                DataSet ds = FindTableComment(tableName);
                if (ds == null || ds.Tables[0].Rows == null || ds.Tables[0].Rows.Count == 0)
                    return;
                //表名
                string TABLE_NAME = ds.Tables[0].Rows[0]["TABLE_NAME"].ToString().Trim();
                //表注释
                string TABLE_COMMENT = ds.Tables[0].Rows[0]["TABLE_COMMENT"].ToString().Trim();
                //注释为空
                if (string.IsNullOrEmpty(TABLE_COMMENT))
                {
                    UpdateTableComment(tableName, txt);
                }
            }

**5、生成一个表中需要更新注释的一组sql语句**

/// <summary>
            /// 生成修改每个字段的sql语句
            /// </summary>
            /// <param name="tableName">表名</param>
            /// <param name="filed">字段</param>
            /// <param name="comment">注释</param>
            /// <returns></returns>
            public string GetFielsSql(string tableName, string filed, string comment)
            {
                try
                {
                    string sql = string.Empty;
                    //传入的注释为空，返回空
                    if (string.IsNullOrEmpty(comment))
                        return string.Empty;
                    //获取tableName的所有字段及注释
                    DataSet ds = FindTableFiledsComment(tableName);
                    foreach (DataRow i in ds.Tables[0].Rows)
                    {
                        //1.判断数据库表列名是否为空 2.表中的列名=Excel中的列名。
                        if (!string.IsNullOrEmpty(i["COLUMN_NAME"].ToString()) && filed == i["COLUMN_NAME"].ToString())
                        {
                            string tableCom = i["COLUMN_COMMENT"].ToString().Trim();
                            //如果字段中注释为空
                            if (string.IsNullOrEmpty(tableCom))
                            {
                                sql = " alter table " + tableName + " modify column " + filed + " int comment '" + comment + "'";
                                return sql;
                            }
                        }
                    }
    
                    return string.Empty;
                }
                catch (Exception ex)
                {
    
                    throw ex;
                }
            }

**6、打开Excel操作**

/// <summary>
            /// 打开Excel执行相关操作，
            /// </summary>
            /// <param name="filePath"></param>
            /// <returns></returns>
            public string ReadExcel(string filePath)
            {
                Application app = null;
                Workbooks wbks = null;
                try
                {
                    app = new Application();
                    wbks = app.Workbooks;
                    _Workbook _wbk = wbks.Add(filePath);
                    Sheets shs = _wbk.Sheets;
    
                    //获取第一个sheet
                    _Worksheet _wsh = shs.Item[1];
                    if (_wsh == null) return null;
                    string sheetName = _wsh.Name;
                    //sheet[1]的行总数
                    int rowsCount = _wsh.UsedRange.Rows.Count;
                    //sheet[1]的列总数
                    int ColumsCount = _wsh.UsedRange.Columns.Count;
    
                    Range range = (Range)_wsh.Cells[1, 1];
                    //sheet[1]的第一个单元格中的文本，作为表的注释
                    string txt = range.Text;
    
                    //更新表的注释
                    GetTableCommentsUpdate(sheetName, txt);
    
                    string rangeCellStart = string.Empty;
                    string rangeCellEnd = string.Empty;
                    List<string> sqlList = new List<string>();
                    //从第三行开始读取数据，第一行为表的注释，第二行为列名
                    for (int row = 3; row <= rowsCount; row++)
                    {
                        //字段名
                        rangeCellStart = ((Range)_wsh.Cells[row, 1]).Text;
                        //字段注释
                        rangeCellEnd = ((Range)_wsh.Cells[row, ColumsCount]).Text;
                        //1.字段名为空，中断循环。2.字段名中含有‘注’字的，中断循环
                        if (string.IsNullOrEmpty(rangeCellStart) || rangeCellStart.Contains("注"))
                        {
                            break;
                        }
                        //调用函数，为没有注释的字段，更新注释，sheetName作为表名
                        string sql = GetFielsSql(sheetName, rangeCellStart, rangeCellEnd);
                        //判断返回的sql语句是否有误
                        if (!string.IsNullOrEmpty(sql))
                        {
                            sqlList.Add(sql);
                        }
                    }
    
                    //更新表字段注释
                    UpdateTableFieldsComment(sqlList);
    
                    wbks.Close();              
                    app.Quit();
                    return null;
    
                }
                catch (Exception ex)
                {
                    wbks.Close();
                    app.Quit();
                    throw ex;
                }
            }

[70]: /images/20220528/a83abe58560540e791d67ee2d95b4e25.png