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

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

1、新建一个任意类型的项目。安装Microsoft.Office.Interop.Excel，这种方式依赖于office，电脑上要装相应版本的office

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

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

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

    <add key="FilesPath" value="F:\HR-Link-Work\Pro\ProCommets\ProCommets\bin\ExcelFiles"/>

遍历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;
            }
        }