C# Excel操作记录 及 代码优化考虑

最近，因为有需求，找了很多C#操作Excel的资料来学习，完了准备在这里做个记录，以备后日不时之需。

我看到的，操作Excel的方式有四种：

1. com组件
2. OleDb
3. npoi
4. epplus

com组件

首先，com 组件的方式指的是利用 office 的 Excel 组件对Excel文件进行操作，这种方式需要电脑上安装有Excel，项目中也要添加相关引用，项目运行时也要启动Excel进程。

据说，这种方式的功能很强大，说是Excel能实现的功能都能够通过这种方法在代码里进行设置，反正我是没有验证过，因为它实在是太慢了。其他方法1s就可以搞定的，用它稳定在8.7s...

OleDb

使用Microsoft Jet 提供程序用于连接到 Excel 工作簿,将Excel文件作为数据源来读写,这种方法读写都相当快，就是不是很灵活。

它需要使用指定的连接字符串初始化 System.Data.OleDb.OleDbConnection 类的新实例，.xls文件 和 .xlsx文件的连接字符串并不一样。

case ".xls":
          strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + 
";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'";
          break;
case ".xlsx":
          strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + 
";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2;'";
          break;

上面的HDR用于判断是否将第一行作为数据读取（个人感觉应该只在读操作有用），IMEX用于判断是读取还是写入，这个需要自己尝试，我只能说，我在写入操作的时候将其设为0，读取的时候设为1，你也可以设为2，错了再调整...

它能返回所有的sheet的名称，以及单个sheet的所有列名，这有时是相当有用的。

        #region 获取Excel文件的表名 返回list集合
        /// <param name="excelFileName">路径名</param>
        /// <returns></returns>
        public static List<string> GetExcelTableName(OleDbConnection conn, string pathName)
        {
            List<string> tableName = new List<string>();
            if (File.Exists(pathName))
            {
                //conn.Open();
                System.Data.DataTable dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string strSheetTableName = null;
                foreach (System.Data.DataRow row in dt.Rows)
                {
                    strSheetTableName = row["TABLE_NAME"].ToString();
                    //过滤无效SheetName   
                    if (strSheetTableName.Contains("$") && strSheetTableName.Replace("'", "").EndsWith("$"))
                    {
                        strSheetTableName = strSheetTableName.Replace("'", "");   //可能会有 '1X$' 出现
                        strSheetTableName = strSheetTableName.Substring(0, strSheetTableName.Length - 1);
                        tableName.Add(strSheetTableName);
                    }
                }
            }
            return tableName;
        }

        #endregion

        #region 获取EXCEL工作表的列名 返回list集合
        /// <param name="Path">Excel路径名</param>
        /// <returns></returns>
        public static List<string> getExcelFileInfo(string pathName)
        {
            string strConn;
            List<string> list_ColumnName = new List<string>();
            FileInfo file = new FileInfo(pathName);
            if (!file.Exists) { throw new Exception("文件不存在"); }
            string extension = file.Extension;
            switch (extension)
            {
                case ".xls":
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'";
                    break;
                case ".xlsx":
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=2;'";
                    break;
                default:
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
            }
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConn);
            conn.Open();
            System.Data.DataTable table = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, null });

            string TableName = null;
            string ColumnName = null;
            foreach (System.Data.DataRow drow in table.Rows)
            {
                TableName = drow["Table_Name"].ToString();
                if (TableName.Contains("$") && TableName.Replace("'", "").EndsWith("$"))
                {
                    System.Data.DataTable tableColumns = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null });
                    foreach (System.Data.DataRow drowColumns in tableColumns.Rows)
                    {
                        ColumnName = drowColumns["Column_Name"].ToString();
                        list_ColumnName.Add(ColumnName);
                    }
                }
            }
            return list_ColumnName;
        }

        #endregion

读写的部分准备用copy来进行说明，虽然不会用到。

        #region 读取Excel，将第一行作为标题，其他行作为数据，再将其导出到Excel，对空白行进行处理
        public static bool copy(string copyPath, string pastePath, string copy_sheetName)
        {
            DataSet ds = new DataSet();
            OleDbConnection copy_objConn = null;//Excel的连接
            OleDbConnection paste_objConn = null;//Excel的连接
            try
            {  
                string strExtension = System.IO.Path.GetExtension(copyPath);//获取文件扩展名
                string strFileName = System.IO.Path.GetFileName(copyPath);//获取文件名
                switch (strExtension)
                {
                    case ".xls":
                        copy_objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + copyPath
                            + ";" + "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"");
                        break;
                    case ".xlsx":
                        copy_objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + copyPath
                            + ";" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\"");
                        break;
                    default:
                        copy_objConn = null;
                        break;
                }
                if (copy_objConn == null)
                {
                    return false;
                }
                copy_objConn.Open();
                string strSql = "select * from [" + copy_sheetName + "] where [系统物料号] is not null";
                OleDbCommand objCmd = new OleDbCommand(strSql, copy_objConn);//获取Excel指定Sheet表中的信息
                OleDbDataAdapter myData = new OleDbDataAdapter(strSql, copy_objConn);
                myData.Fill(ds, copy_sheetName);//填充数据

                DataTable dt = ds.Tables[0];
                strExtension = System.IO.Path.GetExtension(pastePath);//获取文件扩展名
                strFileName = System.IO.Path.GetFileName(pastePath);//获取文件名
                switch (strExtension)
                {
                    case ".xls":
                        paste_objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pastePath
                            + ";" + "Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0;\"");
                        break;
                    case ".xlsx":
                        paste_objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pastePath
                            + ";" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=0;\"");
                        break;
                    default:
                        paste_objConn = null;
                        break;
                }
                if (paste_objConn == null)
                {
                    return false;
                }
                paste_objConn.Open();
                StringBuilder strSQL = new StringBuilder();
                System.Data.OleDb.OleDbCommand cmd;
                string tableName = dt.TableName.Substring(0, dt.TableName.Length - 1);
                try
                {
                    List<string> list = GetExcelTableName(paste_objConn, pastePath);
                    if (list.Contains(tableName))
                    {
                        //覆盖文件时可能会出现Table 'Sheet1' already exists.所以这里先删除了一下
                        cmd = new System.Data.OleDb.OleDbCommand(string.Format("drop table {0}", tableName), paste_objConn);
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine("drop table failed!" + e.Message);
                }
                //创建表格字段
                strSQL.Append("CREATE TABLE ").Append("[" + tableName + "]");
                strSQL.Append("(");