Excel 二次开发系列（4）： 操作一个已经存在Excel

Excel表格文档由于其简单易用，普遍应用于不少单位或部门，因而在编写应用程序过程中，有时会需要对Excel文档进行操作，最简单的情况通常有两种：（1）需要获取文档中一些单元格的值；（2）将文档导入至数据库。 

1).在操作Excel文档之前，需要添加对Excel对象库的引用：

引用—COM—Microsoft Excel 12.0 Object Library（版本号可能不同） 

2).以下代码示意打开一个已有的Excel文档的第一个sheet页，获取单元格“B1”的内容，并判断其值是否为“my”：

 

       private void Operate(string pFileName)
          {
             Excel.Application app = new Excel.Application();//打开一个Excel应用
             if (app == null)
             {
                 return;
             }
 
             Workbooks wbs = app.Workbooks;
             _Workbook wb = wbs.Add(pFileName);//打开一个现有的工作薄
             Sheets shs = wb.Sheets;
             _Worksheet sh = (_Worksheet)shs.get_Item(1);//选择第一个Sheet页
             if (sh == null)
              {
                 return;
             }
 
             Range r = sh.get_Range("B1", Missing.Value);
             if (System.Convert.ToString(r.Value2).Trim().Equals("my"))
              {
                 //Do Something.
             }

            //释放资源 (在第一个系列里面讲到了资源的释放，在这里可以用了) 
               ReleaseCOM(sh);
             ReleaseCOM(shs);
             ReleaseCOM(wb);
             ReleaseCOM(wbs);
             app.Quit();
             ReleaseCOM(app);

         }

4)基于Excel中的函数可以编辑出相当复杂的公式，有时候在处理Excel文档时，希望使用Excel中的公式，以下结合FREQUENCY，MATCH函数对B列计算distinct count，将其写入B100单元格（假定有数据的行不超过100行）：

 string count = sh.UsedRange.Rows.Count.ToString();
            Range r = sh.get_Range("B100", Missing.Value);
            r.Formula = "=SUM(IF(FREQUENCY(MATCH(B1:B" + count + ",B1:B" + count + ",0),MATCH(B1:B" + count + ",B1:B" + count + ",0))>0,1))";
            Console.WriteLine(System.Convert.ToString(r.Value2));

5).从Excel导入数据，可以先使用ODBC，以select查询时使用“[sheet页名称$]”作为“表名”，将数据以datatable形式载入到内存中，之后的事就比较简单了，可以根据datatable逐行构造insert语句，然后插入到目标数据库中：

 

private DataTable LoadExcel(string pPath)
          {
             string connString = "Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;SafeTransactions=0;ReadOnly=1;MaxScanRows=16;Threads=3;MaxBufferSize=2048;UserCommitSync=Yes;FIL=excel 8.0;PageTimeout=5;";
             connString += "DBQ=" + pPath;
             OdbcConnection conn = new OdbcConnection(connString);
             OdbcCommand cmd = new OdbcCommand();
             cmd.Connection = conn;
             //获取Excel中第一个Sheet名称，作为查询时的表名
             string sheetName = this.GetExcelSheetName(pPath);
             string sql = "select * from [" + sheetName.Replace('.', '#') + "$]";
             cmd.CommandText = sql;
             OdbcDataAdapter da = new OdbcDataAdapter(cmd);
             DataSet ds = new DataSet();
             try
              {                
                 da.Fill(ds);
                 return ds.Tables[0];
             }
             catch (Exception x)
              {
                 ds = null;
                 throw new Exception("从Excel文件中获取数据时发生错误！");
             }
             finally
              {
                 cmd.Dispose();
                 cmd = null;
                 da.Dispose();
                 da = null;
                 if (conn.State == ConnectionState.Open)
                  {
                     conn.Close();
                 }
                 conn = null;
             }
         }

6).关于从Excel导入数据，网上应该有很多例子，这里补充一个关于数据导入时的效率问题。最初在从Excel导入数据到MySql中时，当文件达到50000行时，逐行导入花了10余分钟的时间。大量执行插入操作，逐条执行很慢原因可能在于：

(1)每执行一条插入语句，客户端需要与服务器交互一次，这要有代价；

(2)一些数据库对每一条插入操作都执行事务，这也要有代价

所以在大量执行插入操作时，应该尽量先多个insert语句拼接好，例如每次拼接好200个insert语句，然后再一次执行它，这样可以大幅提高效率。