C#如何读取Excel文件

C#读取Excel文件方法一：直接读取（这种直接读取单元格的方法释放很重要）

1. Excel.Application excel=null;  
2. Excel.Workbooks wbs=null;  
3. Excel.Workbook wb=null;  
4. Excel.Worksheet ws=null;  
5. Excel.Range range1=null;  
6. object Nothing=System.Reflection.Missing.Value;  
7.  
8. try  
9. {  
10. excel=new Excel.Application();  
11. excel.UserControl=true;  
12. excel.DisplayAlerts=false;  
13.  
14. excel.Application.Workbooks.Open(this.  
15. FilePath,Nothing,Nothing,Nothing,Nothing,  
16. Nothing,Nothing,Nothing,Nothing,Nothing,  
17. Nothing,Nothing,Nothing);  
18.  
19. wbs=excel.Workbooks;  
20. wb=wbs[1];  
21. ws=(Excel.Worksheet)wb.Worksheets["Sheet2"];  
22.  
23.  
24. int rowCount=ws.UsedRange.Rows.Count;  
25. int colCount=ws.UsedRange.Columns.Count;  
26. if(rowCount<=0)  
27. throw new InvalidFormatException  
28. ("文件中没有数据记录");  
29. if(colCount<4)  
30. throw new InvalidFormatException  
31. ("字段个数不对");  
32.  
33. for(int i=0;i{  
34. this.rowNo=i+1;  
35. object[]row=newobject[4];  
36. for(int j=0;j<4;j++)  
37. {  
38. range1=ws.get_Range(ws.Cells[i+2,j+1],  
39. ws.Cells[i+2,j+1]);  
40. row[j]=range1.Value;  
41.  
42. if(row[0]==null)  
43. {  
44. this.isNullRecord++;  
45. break;  
46. }  
47. }  
48.  
49. if(this.isNullRecord>0)  
50. continue;  
51.  
52. DataRow dataRow=this.readExcel(row);  
53.  
54. if(this.isNullRecord==1)  
55. continue;  
56.  
57. if(this.verifyData(dataRow)==false)  
58. errFlag++;  
59.  
60. this.updateTableCurr(dataRow);  
61. }  
62. }  
63. finally  
64. {  
65. if(excel!=null)  
66. {  
67. if(wbs!=null)  
68. {  
69. if(wb!=null)  
70. {  
71. if(ws!=null)  
72. {  
73. if(range1!=null)  
74. {  
75. System.Runtime.InteropServices.Marshal.  
76. ReleaseComObject(range1);  
77. range1=null;  
78. }  
79. System.Runtime.InteropServices.Marshal.  
80. ReleaseComObject(ws);  
81. ws=null;  
82. }  
83. wb.Close(false,Nothing,Nothing);  
84. System.Runtime.InteropServices.Marshal.  
85. ReleaseComObject(wb);  
86. wb=null;  
87. }  
88. wbs.Close();  
89. System.Runtime.InteropServices.Marshal.  
90. ReleaseComObject(wbs);  
91. wbs=null;  
92. }  
93. excel.Application.Workbooks.Close();  
94. excel.Quit();  
95. System.Runtime.InteropServices.Marshal.  
96. ReleaseComObject(excel);  
97. excel=null;  
98. GC.Collect();  
99. }  
100. }  

C#读取Excel文件方法二：通过OleDb连接，把excel文件作为数据源来读取

（这里是fill进dataset，也可以返回OleDbDataReader来逐行读，数据较快）

    注：这种方法容易把混合型的字段作为null值读取进来，解决办法是改造连接字符串

1. strConn = "Provider=Microsoft.Jet.  
2. OLEDB.4.0;Data Source=C:\\Erp1912.xls;Extended   
3. Properties='Excel8.0;HDR=Yes;IMEX=1'";  

    通过Imex=1来把混合型作为文本型读取，避免null值,来实现C#读取Excel文件

1. private DataSet importExcelToDataSet  
2. (string FilePath)  
3. {  
4. string strConn;  
5. strConn="Provider=Microsoft.Jet.  
6. OLEDB.4.0;"+"DataSource="+FilePath+";  
7. ExtendedProperties=Excel8.0;";  
8. OleDbConnection conn=new OleDbConnection  
9. (strConn);  
10. OleDbDataAdapter myCommand=new OleDbDataAdapter  
11. ("SELECT *FROM [Sheet1$]",strConn);  
12. DataSetmy DataSet=new DataSet();  
13. try  
14. {  
15. myCommand.Fill(myDataSet);  
16. }  
17. catch(Exception ex)  
18. {  
19. throw new InvalidFormatException  
20. ("该Excel文件的工作表的名字不正确,"+ex.Message);  
21. }  
22. returnmyDataSet;  
23. }
24. 相关网址：http://blog.sina.com.cn/s/blog_3e8011bf0101emk2.html