C#中Excel文件的读取

   excel.application excel = null;
   excel.workbooks wbs = null;
   excel.workbook wb = null;
   excel.worksheet ws = null;
   excel.range range1 = null;
   object nothing = system.reflection.missing.value;
    
   try
   {
    excel = new excel.application();
    excel.usercontrol = true;
    excel.displayalerts = false;
                    
    excel.application.workbooks.open(this.filepath,nothing,nothing,nothing,nothing,nothing,nothing,nothing,nothing,nothing,nothing,nothing,nothing ) ;
    
    wbs = excel.workbooks;
    wb = wbs[1];
    ws = (excel.worksheet)wb.worksheets["sheet2"];
    
         
    int rowcount = ws.usedrange.rows.count;
    int colcount = ws.usedrange.columns.count;
    if (rowcount <= 0)
     throw new invalidformatexception("文件中没有数据记录");
    if (colcount < 4 )
     throw new invalidformatexception("字段个数不对");
    
    for (int i = 0;i    {

     this.rowno = i + 1;
     object[] row = new object[4];
     for (int j = 0;j<4;j++)
     {
      range1 = ws.get_range(ws.cells[i+2,j+1],ws.cells[i+2,j+1]);
      row[j] = range1.value;

      if (row[0] == null)
      {
       this.isnullrecord++;
       break;
      }
     }
                   
     if (this.isnullrecord > 0)
      continue;

     datarow datarow = this.readexcel(row);

     if (this.isnullrecord == 1)
      continue;
  
     if (this.verifydata(datarow) == false)
      errflag++;
  
     this.updatetablecurr(datarow);
    }
    
   }
   finally
   {
    if (excel != null)
    {
     if (wbs != null)
     {
      if (wb != null)
      {
       if (ws != null)
       {
        if (range1 != null)
        {
         system.runtime.interopservices.marshal.releasecomobject(range1);
         range1 = null;
        }
        system.runtime.interopservices.marshal.releasecomobject(ws);
        ws = null;
       }
       wb.close(false,nothing,nothing); 
       system.runtime.interopservices.marshal.releasecomobject(wb);
       wb = null;
      }
      wbs.close();
      system.runtime.interopservices.marshal.releasecomobject(wbs);
      wbs = null;
     }
     excel.application.workbooks.close();
     excel.quit();
     system.runtime.interopservices.marshal.releasecomobject(excel);
     excel = null;
     gc.collect();
    }
   }

方法二：这里是fill进dataset，也可以返回oledbdatareader来逐行读，数据较快
注：这种方法容易把混合型的字段作为null值读取进来，解决办法是改造连接字符串
strconn = "provider=microsoft.jet.oledb.4.0;data source=c://erp1912.xls;extended properties='excel 8.0;hdr=yes;imex=1'";
通过imex=1来把混合型作为文本型读取，避免null值,详细请见http://www.connectionstrings.com
private dataset importexceltodataset(string filepath)
  {
   string strconn;
   strconn = "provider=microsoft.jet.oledb.4.0;" + "data source=" + filepath + ";extended properties=excel 8.0;";
   oledbconnection conn = new oledbconnection(strconn);
   oledbdataadapter mycommand = new oledbdataadapter("select * from [sheet1$]",strconn);
   dataset mydataset = new dataset();
   try
   {
    mycommand.fill(mydataset);
   }
   catch(exception ex)
   {
    throw new invalidformatexception("该excel文件的工作表的名字不正确," + ex.message);
   }
   return mydataset;
  }