C#读取Excel的三种方式以及比较分析

（1）oledb方式

优点：将excel直接当做数据源处理，通过sql直接读取内容，读取速度较快。

缺点：读取数据方式不够灵活，无法直接读取某一个单元格，只有将整个sheet页读取出来后（结果为datatable）再在datatable中根据行列数来获取指定的值。

当excel数据量很大时。会非常占用内存，当内存不够时会抛出内存溢出的异常。

读取代码如下：

 public datatable getexceltablebyoledb(string strexcelpath, string tablename)
 {
   try
   {
     datatable dtexcel = new datatable();
     //数据表
     dataset ds = new dataset();
     //获取文件扩展名
     string strextension = system.io.path.getextension(strexcelpath);
     string strfilename = system.io.path.getfilename(strexcelpath);
     //excel的连接
     oledbconnection objconn = null;
     switch (strextension)
     {
       case ".xls":
         objconn = new oledbconnection("provider=microsoft.jet.oledb.4.0;data source=" + strexcelpath + ";" + "extended properties=\"excel 8.0;hdr=no;imex=1;\"");
         break;
       case ".xlsx":
         objconn = new oledbconnection("provider=microsoft.ace.oledb.12.0;data source=" + strexcelpath + ";" + "extended properties=\"excel 12.0;hdr=no;imex=1;\"");
         break;
       default:
         objconn = null;
         break;
     }
     if (objconn == null)
     {
       return null;
     }
     objconn.open();
     //获取excel中所有sheet表的信息
     //system.data.datatable schematable = objconn.getoledbschematable(system.data.oledb.oledbschemaguid.tables, null);
     //获取excel的第一个sheet表名
     //string tablename = schematable.rows[0][2].tostring().trim();
     string strsql = "select * from [" + tablename + "]";
     //获取excel指定sheet表中的信息
     oledbcommand objcmd = new oledbcommand(strsql, objconn);
     oledbdataadapter mydata = new oledbdataadapter(strsql, objconn);
     mydata.fill(ds, tablename);//填充数据
     objconn.close();
     //dtexcel即为excel文件中指定表中存储的信息
     dtexcel = ds.tables[tablename];
     return dtexcel;
   }
   catch
   {
     return null;
   }
 }

下面说明一下连接字符串

hdr=yes，这代表第一行是标题，不做为数据使用（但是我在实际使用中，如果第一行存在复杂数值，那么读取得到的datatable列标题会自动设置为f1、f2等方式命名，与实际应用不符，所以当时是通过hdr=no方式将所有内容读取到datatable中，然后手动将第一行设置成标题的）；imex ( import export mode )设置
imex 有三种模式：
0 is export mode
1 is import mode
2 is linked mode (full update capabilities)
我这里特别要说明的就是 imex 参数了，因为不同的模式代表著不同的读写行为：
当 imex=0 时为“汇出模式”，这个模式开启的 excel 档案只能用来做“写入”用途。
当 imex=1 时为“汇入模式”，这个模式开启的 excel 档案只能用来做“读取”用途。
当 imex=2 时为“链接模式”，这个模式开启的 excel 档案可同时支援“读取”与“写入”用途。

---------------------------------

另外，读取excel2007版本的文件时，版本应该从8.0改为12.0，同时驱动不能再用jet，而应该用ace。负责会造成“找不到可安装的 isam”的错误。

---------------------------------

在网上还发现采用这种方式存在取出的sheet表的个数多于实际excel表中的sheet表个数的情况，其原因有二：

1. 取出的名称中，包括了xl命名管理器中的名称（参见xl2007的公式--命名管理器， 快捷键crtl+f3）；

2. 取出的名称中，包括了filterdatabase后缀的， 这是xl用来记录filter范围的。

对于第一点比较简单， 删除已有命名管理器中的内容即可；第二点处理起来比较麻烦， filter删除后这些名称依然保留着，简单的做法是新增sheet然后将原sheet copy进去。但实际情况并不能为每个excel做以上检查。下面给出了过滤的方案。（此问题我们有验证过，大家自己验证一下吧）

 //objconn为读取excel的链接，下面通过过滤来获取有效的sheet页名称集合
  system.data.datatable schematable = objconn.getoledbschematable(system.data.oledb.oledbschemaguid.tables, null);
  list<string> lstsheetnames = new list<string>();
  for (int i = 0; i < schematable.rows.count; i++)
  {
    string strsheetname = (string)dtsheetname.rows[i]["table_name"];
    if (strsheetname.contains("$") && !strsheetname.replace("'", "").endswith("$"))
    {
      //过滤无效sheetname完毕....
      continue;
    }
    if (lstsheetnames != null && !lstsheetnames.contains(strsheetname))
      lstsheetnames.add(strsheetname);
  }

因为读取出来无效sheetname一般情况最后一个字符都不会是$。如果sheetname有一些特殊符号，读取出来的sheetname会自动加上单引号。比如在excel中将sheetname编辑成mysheet(1)，此时读取出来的sheetname就为：'mysheet(1)$'，所以判断最后一个字符是不是$之前最好过滤一下单引号。

---------------------------------

（2）com组件的方式（通过添加 microsoft.office.interop.excel引用实现）

优点：能够非常灵活的读取excel中的数据，用户可以灵活的调用各种函数进行处理。

缺点：基于单元格的处理，读取速度较慢，对于数据量较大的文件最好不要使用此种方式读取。

需要添加相应的dll引用，必须存在此引用才可使用，如果是web站点部署在iis上时，还需要服务器机子已安装了excel，有时候还需要为配置iis权限。

读取代码如下：

 private stopwatch wath = new stopwatch();
 /// <summary>
 /// 使用com读取excel
 /// </summary>
 /// <param name="excelfilepath">路径</param>
 /// <returns>datatabel</returns>
 public system.data.datatable getexceldata(string excelfilepath)
 {
   excel.application app = new excel.application();
   excel.sheets sheets;
   excel.workbook workbook = null;
   object omissiong = system.reflection.missing.value;
   system.data.datatable dt = new system.data.datatable();
   wath.start();
   try
   {
     if (app == null)
     {
       return null;
     }
     workbook = app.workbooks.open(excelfilepath, omissiong, omissiong, omissiong, omissiong, omissiong, 
       omissiong, omissiong, omissiong, omissiong, omissiong, omissiong, omissiong, omissiong, omissiong);
     //将数据读入到datatable中——start  
     sheets = workbook.worksheets;
     excel.worksheet worksheet = (excel.worksheet)sheets.get_item(1);//读取第一张表
     if (worksheet == null)
       return null;
     string cellcontent;
     int irowcount = worksheet.usedrange.rows.count;
     int icolcount = worksheet.usedrange.columns.count;
     excel.range range;
     //负责列头start
     datacolumn dc;
     int columnid = 1;
     range = (excel.range)worksheet.cells[1, 1];
     while (range.text.tostring().trim() != "")
     {
       dc = new datacolumn();
       dc.datatype = system.type.gettype("system.string");
       dc.columnname = range.text.tostring().trim();
       dt.columns.add(dc);
 
       range = (excel.range)worksheet.cells[1, ++columnid];
     }
     //end
     for (int irow = 2; irow <= irowcount; irow++)
     {
       datarow dr = dt.newrow();
       for (int icol = 1; icol <= icolcount; icol++)
       {
         range = (excel.range)worksheet.cells[irow, icol];
         cellcontent = (range.value2 == null) ? "" : range.text.tostring();
           dr[icol - 1] = cellcontent;
       }
       dt.rows.add(dr);
     }
     wath.stop();
     timespan ts = wath.elapsed;
     //将数据读入到datatable中——end
     return dt;
   }
   catch
   {
     return null;
   }
   finally
   {
     workbook.close(false, omissiong, omissiong);
     system.runtime.interopservices.marshal.releasecomobject(workbook);
     workbook = null;
     app.workbooks.close();
     app.quit();
     system.runtime.interopservices.marshal.releasecomobject(app);
     app = null;
     gc.collect();
     gc.waitforpendingfinalizers();
   }
 }
 /// <summary>
 /// 使用com，多线程读取excel（1 主线程、4 副线程）
 /// </summary>
 /// <param name="excelfilepath">路径</param>
 /// <returns>datatabel</returns>
 public system.data.datatable threadreadexcel(string excelfilepath)
 {
   excel.application app = new excel.application();
   excel.sheets sheets = null;
   excel.workbook workbook = null;
   object omissiong = system.reflection.missing.value;
   system.data.datatable dt = new system.data.datatable();
   wath.start();
   try
   {
     if (app == null)
     {
       return null;
     }
     workbook = app.workbooks.open(excelfilepath, omissiong, omissiong, omissiong, omissiong, omissiong, omissiong, 
       omissiong, omissiong, omissiong, omissiong, omissiong, omissiong, omissiong, omissiong);
     //将数据读入到datatable中——start  
     sheets = workbook.worksheets;
     excel.worksheet worksheet = (excel.worksheet)sheets.get_item(1);//读取第一张表
     if (worksheet == null)
       return null;
     string cellcontent;
     int irowcount = worksheet.usedrange.rows.count;
     int icolcount = worksheet.usedrange.columns.count;
     excel.range range;
     //负责列头start
     datacolumn dc;
     int columnid = 1;
     range = (excel.range)worksheet.cells[1, 1];
     while (icolcount >= columnid)
     {
       dc = new datacolumn();
       dc.datatype = system.type.gettype("system.string");
       string strnewcolumnname = range.text.tostring().trim();
       if (strnewcolumnname.length == 0) strnewcolumnname = "_1";
       //判断列名是否重复
       for (int i = 1; i < columnid; i++)
       {
         if (dt.columns[i - 1].columnname == strnewcolumnname)
           strnewcolumnname = strnewcolumnname + "_1";
       }
       dc.columnname = strnewcolumnname;
       dt.columns.add(dc);
       range = (excel.range)worksheet.cells[1, ++columnid];
     }
     //end
     //数据大于500条，使用多进程进行读取数据
     if (irowcount - 1 > 500)
     {
       //开始多线程读取数据
       //新建线程
       int b2 = (irowcount - 1) / 10;
       datatable dt1 = new datatable("dt1");
       dt1 = dt.clone();
       sheetoptions sheet1thread = new sheetoptions(worksheet, icolcount, 2, b2 + 1, dt1);
       thread othread1 = new thread(new threadstart(sheet1thread.sheettodatatable));
       othread1.start();
       //阻塞 1 毫秒，保证第一个读取 dt1
       thread.sleep(1);
       datatable dt2 = new datatable("dt2");
       dt2 = dt.clone();
       sheetoptions sheet2thread = new sheetoptions(worksheet, icolcount, b2 + 2, b2 * 2 + 1, dt2);
       thread othread2 = new thread(new threadstart(sheet2thread.sheettodatatable));
       othread2.start();
       datatable dt3 = new datatable("dt3");
       dt3 = dt.clone();
       sheetoptions sheet3thread = new sheetoptions(worksheet, icolcount, b2 * 2 + 2, b2 * 3 + 1, dt3);
       thread othread3 = new thread(new threadstart(sheet3thread.sheettodatatable));
       othread3.start();
       datatable dt4 = new datatable("dt4");
       dt4 = dt.clone();
       sheetoptions sheet4thread = new sheetoptions(worksheet, icolcount, b2 * 3 + 2, b2 * 4 + 1, dt4);
       thread othread4 = new thread(new threadstart(sheet4thread.sheettodatatable));
       othread4.start();
       //主线程读取剩余数据
       for (int irow = b2 * 4 + 2; irow <= irowcount; irow++)
       {
         datarow dr = dt.newrow();
         for (int icol = 1; icol <= icolcount; icol++)
         {
           range = (excel.range)worksheet.cells[irow, icol];
           cellcontent = (range.value2 == null) ? "" : range.text.tostring();
           dr[icol - 1] = cellcontent;
         }
         dt.rows.add(dr);
       }
       othread1.join();
       othread2.join();
       othread3.join();
       othread4.join();
       //将多个线程读取出来的数据追加至 dt1 后面
       foreach (datarow dr in dt.rows)
         dt1.rows.add(dr.itemarray);
       dt.clear();
       dt.dispose();
       foreach (datarow dr in dt2.rows)
         dt1.rows.add(dr.itemarray);
       dt2.clear();
       dt2.dispose();
       foreach (datarow dr in dt3.rows)
         dt1.rows.add(dr.itemarray);
       dt3.clear();
       dt3.dispose();
       foreach (datarow dr in dt4.rows)
         dt1.rows.add(dr.itemarray);
       dt4.clear();
       dt4.dispose();
       return dt1;
     }
     else
     {
       for (int irow = 2; irow <= irowcount; irow++)
       {
         datarow dr = dt.newrow();
         for (int icol = 1; icol <= icolcount; icol++)
         {
           range = (excel.range)worksheet.cells[irow, icol];
           cellcontent = (range.value2 == null) ? "" : range.text.tostring();
           dr[icol - 1] = cellcontent;
         }
         dt.rows.add(dr);
       }
     }
     wath.stop();
     timespan ts = wath.elapsed;
     //将数据读入到datatable中——end
     return dt;
   }
   catch
   {
     return null;
   }
   finally
   {
     workbook.close(false, omissiong, omissiong);
     system.runtime.interopservices.marshal.releasecomobject(workbook);
     system.runtime.interopservices.marshal.releasecomobject(sheets);
     workbook = null;
     app.workbooks.close();
     app.quit();
     system.runtime.interopservices.marshal.releasecomobject(app);
     app = null;
     gc.collect();
     gc.waitforpendingfinalizers();
   }
 }

（3）npoi方式读取excel（此方法未经过测试）

npoi 是 poi 项目的 .net 版本。poi是一个开源的java读写excel、word等微软ole2组件文档的项目。使用 npoi 你就可以在没有安装 office 或者相应环境的机器上对 word/excel 文档进行读写。

优点：读取excel速度较快，读取方式操作灵活性

缺点：需要下载相应的插件并添加到系统引用当中。

 /// <summary>
 /// 将excel中的数据导入到datatable中
 /// </summary>
 /// <param name="sheetname">excel工作薄sheet的名称</param>
 /// <param name="isfirstrowcolumn">第一行是否是datatable的列名</param>
 /// <returns>返回的datatable</returns>
 public datatable exceltodatatable(string sheetname, bool isfirstrowcolumn)
 {
   isheet sheet = null;
   datatable data = new datatable();
   int startrow = 0;
   try
   {
     fs = new filestream(filename, filemode.open, fileaccess.read);
     if (filename.indexof(".xlsx") > 0) // 2007版本
       workbook = new xssfworkbook(fs);
     else if (filename.indexof(".xls") > 0) // 2003版本
       workbook = new hssfworkbook(fs);
     if (sheetname != null)
     {
       sheet = workbook.getsheet(sheetname);
     }
     else
     {
       sheet = workbook.getsheetat(0);
     }
     if (sheet != null)
     {
       irow firstrow = sheet.getrow(0);
       int cellcount = firstrow.lastcellnum; //一行最后一个cell的编号 即总的列数
       if (isfirstrowcolumn)
       {
         for (int i = firstrow.firstcellnum; i < cellcount; ++i)
         {
           datacolumn column = new datacolumn(firstrow.getcell(i).stringcellvalue);
           data.columns.add(column);
         }
         startrow = sheet.firstrownum + 1;
       }
       else
       {
         startrow = sheet.firstrownum;
       }
       //最后一列的标号
       int rowcount = sheet.lastrownum;
       for (int i = startrow; i <= rowcount; ++i)
       {
         irow row = sheet.getrow(i);
         if (row == null) continue; //没有数据的行默认是null　　　　　　　
         
         datarow datarow = data.newrow();
         for (int j = row.firstcellnum; j < cellcount; ++j)
         {
           if (row.getcell(j) != null) //同理，没有数据的单元格都默认是null
             datarow[j] = row.getcell(j).tostring();
         }
         data.rows.add(datarow);
       }
     }
     return data;
   }
   catch (exception ex)
   {
     console.writeline("exception: " + ex.message);
     return null;
   }
 }

下面是一些相关的文章，大家可以参考下