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数据库的两种方法，希望对你有所帮助。