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来逐行读,数据较快)
- strConn
= "Provider=Microsoft.Jet. - OLEDB.4.0;Data
Source=C:\\Erp1912.xls;Extended - Properties='Excel8.0;HDR=Yes;IMEX=1'";
- 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;
- }
- 相关网址:http://blog.sina.com.cn/s/blog_3e8011bf0101emk2.html