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

原创 2018年03月25日 20:24:11

我这里用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;
            }
        }
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/hyunbar/article/details/79690124

mysql添加表注释、字段注释、查看与修改注释

1 创建表的时候写注释 create table test1 ( field_name int comment '字段的注释' )comment='表的注释'; 2 修改表的注释 al...
  • ZChangfeng
  • ZChangfeng
  • 2017-06-15 19:56:39
  • 1174

Mysql对表格和表格中的字段添加注释

MySQL对表格和表格中的字段添加注释
  • dream_follower
  • dream_follower
  • 2016-12-14 09:14:52
  • 849

MySQL获取表中各字段的注释

在创建一张数据库表时,表的字段通常都是字母的组合,如userName、userId等等。但当我们进行系统开发时,需要将数据库查询的结果在前台显示,我们希望表头是中文显示,这时候,数据库表字段的注释就是...
  • u013485584
  • u013485584
  • 2017-07-19 10:22:50
  • 1511

MySQL-修改字段类型、设置默认值,以及添加注释

修改字段类型、设置默认值,以及添加注释: ALTER TABLE m_cultureact_gzl MODIFY COLUMN SIGN INT(1) DEFAULT  1 COMMENT '为了给...
  • ZHOU_VIP
  • ZHOU_VIP
  • 2017-12-11 18:34:47
  • 858

mysql修改字段注释

来源:http://blog.sina.com.cn/s/blog_72aace390102uwgg.html 1. 问题      已经有很多数据的按照业务逻...
  • aosica321
  • aosica321
  • 2017-01-06 14:27:29
  • 486

mysql表注释乱码

问题 执行语句: alter table TABLE_XXX comment ‘中文号’; 结果: use information_schema; select * from TABLES w...
  • r13929847477
  • r13929847477
  • 2017-06-09 11:35:59
  • 1787

mysql如何查看建表时的注释

1.给大家做演示,这里随便创建一张学生表,代码如下: CREATE TABLE `student` ( `id` int(11) AUTO_INCREMENT PRIMARY KEY ...
  • wangqing_12345
  • wangqing_12345
  • 2016-07-06 16:56:56
  • 2420

Mysql中用SQL增加、删除字段,修改字段名、字段类型、注释,调整字段顺序

Mysql中用SQL增加、删除字段,修改字段名、字段类型、注释,调整字段顺序 转自:http://www.cnblogs.com/wenlj/p/5258102.html http://datab...
  • yueguanyun
  • yueguanyun
  • 2017-10-16 14:06:00
  • 1157

mysql 给表和字段加注释

给表加注释: ALTER TABLE table_name COMMENT='旺旺信息表'; 给列加注释: ALTER table table_name MODIFY `column_name` d...
  • ludengji
  • ludengji
  • 2013-09-03 19:38:41
  • 86743

mysql列与注释修改SQL语句

--  新增列与注释 ALTER table  tb1  add spec_mark int   DEFAULT 0 COMMENT '0-表示普通,1-表示颜色'; -- 修改列与...
  • RodJohnsonDoctor
  • RodJohnsonDoctor
  • 2015-02-11 18:23:17
  • 11230
收藏助手
不良信息举报
您举报文章:读取Execl中读取数据并修改Mysql中表的注释和字段的注释
举报原因:
原因补充:

(最多只允许输入30个字)