C#读取Excel文件两招搞定

摘要：C#读取Excel文件可以通过直接读取和OleDb连接，把excel文件作为数据源来读取；本文结合具体代码向您介绍C#读取Excel文件的方法。 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=newobject[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(); } } C#读取Excel文件方法二：通过OleDb连接，把excel文件作为数据源来读取（这里是fill进dataset，也可以返回OleDbDataReader来逐行读，数据较快） 注：这种方法容易把混合型的字段作为null值读取进来，解决办法是改造连接字符串 strConn = "Provider=Microsoft.Jet. OLEDB.4.0;Data Source=C://Erp1912.xls;Extended Properties='Excel8.0;HDR=Yes;IMEX=1'"; 通过Imex=1来把混合型作为文本型读取，避免null值,来实现C#读取Excel文件 private DataSet importExcelToDataSet (string FilePath) { string strConn; strConn="Provider=Microsoft.Jet. OLEDB.4.0;"+"DataSource="+FilePath+"; ExtendedProperties=Excel8.0;"; OleDbConnection conn=new OleDbConnection (strConn); OleDbDataAdapter myCommand=new OleDbDataAdapter ("SELECT *FROM [Sheet1$]",strConn); DataSetmy DataSet=new DataSet(); try { myCommand.Fill(myDataSet); } catch(Exception ex) { throw new InvalidFormatException ("该Excel文件的工作表的名字不正确,"+ex.Message); } returnmyDataSet; } 以上介绍C#读取Excel数据库的两种方法，希望对你有所帮助。