两招搞定C#读取Excel文件

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

Excel.Applicationexcel=null;
Excel.Workbookswbs=null;
Excel.Workbookwb=null;
Excel.Worksheetws=null;
Excel.Rangerange1=null;
objectNothing=System.Reflection.Missing.Value;

try
{
excel=newExcel.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"];


introwCount=ws.UsedRange.Rows.Count;
intcolCount=ws.UsedRange.Columns.Count;
if(rowCount<=0)
thrownewInvalidFormatException
("文件中没有数据记录");
if(colCount<4)
thrownewInvalidFormatException
("字段个数不对");

for(inti=0;i{
this.rowNo=i+1;
object[]row=newobject[4];
for(intj=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;

DataRowdataRow=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文件

privateDataSetimportExcelToDataSet
(stringFilePath)
{
stringstrConn;
strConn="Provider=Microsoft.Jet.
OLEDB.4.0;"+"DataSource="+FilePath+";
ExtendedProperties=Excel8.0;";
OleDbConnectionconn=newOleDbConnection
(strConn);
OleDbDataAdaptermyCommand=newOleDbDataAdapter
("SELECT*FROM[Sheet1$]",strConn);
DataSetmyDataSet=newDataSet();
try
{
myCommand.Fill(myDataSet);
}
catch(Exceptionex)
{
thrownewInvalidFormatException
("该Excel文件的工作表的名字不正确,"+ex.Message);
}
returnmyDataSet;
}