用c#讀取Excel的三種方法

C#讀取Excel的三種方法及比較(1)OleDB方式優(yōu)點(diǎn)：將Excel直接當(dāng)做數(shù)據(jù)源處理，通過SQL直接讀取內(nèi)容，讀取速度較快。缺點(diǎn)：讀取數(shù)據(jù)方式不夠靈活，無法直接讀取某一個單元格，只有將整個Sheet頁讀取出來后(結(jié)果為Datatable)再在Datatable中根據(jù)行列數(shù)來獲取指定的值。當(dāng)Excel數(shù)據(jù)量很大時。會非常占用內(nèi)存，當(dāng)內(nèi)存不夠時會拋出內(nèi)存溢出的異常。讀取代碼如下：1:publicDataTableGetExcelTableByOleDB(stringstrExcelPath,stringtableName)2：(3:try4: {5:DataTabledtExcel=newDataTable。；6:〃數(shù)據(jù)表7:DataSetds=newDataSet();8:〃獲取文件擴(kuò)展名9:stringstrExtension=System.IO.Path.GetExtension(strExcelPath);10:stringstrFileName=System.IO.Path.GetFileName(strExcelPath);11://Excel的連接12:OleDbConnectionobjConn=null;

13:switch(strExtension)14: {15:case".xls":16:objConn=newOleDbConnection("Provider=MicrosoftJetOLEDB.4.0;DataSource="+strExcelPath+";"+"ExtendedProperties=\''Excel8.0;HDR=NO;IMEX=1;\"");17:break;18:case".xlsx":19:objConn=newOleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;DataSource="+strExcelPath+";"+"ExtendedProperties=\"Excel12.0;HDR=NO;IMEX=1;\"");20:break;21:default:22:objConn=null;23:break;TOC\o"1-5"\h\z24: }25:if(objConn==null)26: {27:returnnull;28: }29:objConn.Open();30://獲取Excel中所有Sheet表的信息

31://System.Data.DataTableschemaTable=objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);32://獲取Excel的第一個Sheet表名33://stringtableName=schemaTable.Rows[0][2].ToString().Trim();34:stringstrSql="select*from["+tableName+'1”;35://獲取Excel指定Sheet表中的信息36:OleDbCommandobjCmd=newOleDbCommand(strSql,objConn);37:OleDbDataAdaptermyData=newOleDbDataAdapter(strSql,objConn);38:myData.Fill(ds,tableName);//填充數(shù)據(jù)39:objConn.Close();40://dtExcel即為excel文件中指定表中存儲的信息41:dtExcel=ds.Tables[tableName];42:returndtExcel;TOC\o"1-5"\h\z}44:catch{46:returnnull;}}下面說明一下連接字符串HDR=Yes,這代表第一行是標(biāo)題，不做為數(shù)據(jù)使用(但是我在實(shí)際使用中，如果第一行存在復(fù)雜數(shù)值，那么讀取得到的Datatable列標(biāo)題會自動設(shè)置為F1、

F2等方式命名，與實(shí)際應(yīng)用不符，所以當(dāng)時是通過HDR=No方式將所有內(nèi)容讀取到Datatable中，然后手動將第一行設(shè)置成標(biāo)題的)；IMEX(IMportEXportmode)設(shè)置IMEX有三種模式：0isExportmodeisImportmodeisLinkedmode(fullupdatecapabilities)我這里特別要說明的就是IMEX參數(shù)了，因?yàn)椴煌哪Ｊ酱碇煌淖x寫行為：當(dāng)IMEX=0時為“匯出模式”，這個模式開啟的Excel檔案只能用來做“寫入”用途。當(dāng)IMEX=1時為“匯入模式”，這個模式開啟的Excel檔案只能用來做“讀取”用途。當(dāng)IMEX=2時為“鏈接模式”，這個模式開啟的Excel檔案可同時支援“讀取”與“寫入”用途。另外，讀取Excel2007版本的文件時，版本應(yīng)該從8.0改為12.0，同時驅(qū)動不能再用Jet，而應(yīng)該用ACE。負(fù)責(zé)會造成“找不到可安裝的ISAM”的錯誤。在網(wǎng)上還發(fā)現(xiàn)采用這種方式存在取出的Sheet表的個數(shù)多于實(shí)際Excel表中的Sheet表個數(shù)的情況，其原因有二：

.取出的名稱中，包括了XL命名管理器中的名稱(參見XL2007的公式--命名管理器，快捷鍵Crtl+F3);.取出的名稱中，包括了FilterDatabase后綴的，這是XL用來記錄Filter范圍的。對于第一點(diǎn)比較簡單，刪除已有命名管理器中的內(nèi)容即可；第二點(diǎn)處理起來比較麻煩，F(xiàn)ilter刪除后這些名稱依然保留著，簡單的做法是新增Sheet然后將原SheetCopy進(jìn)去。但實(shí)際情況并不能為每個Excel做以上檢查。下面給出了過濾的方案。(此問題我們有驗(yàn)證過，大家自己驗(yàn)證一下吧)1://objConn為讀取Excel的鏈接，下面通過過濾來獲取有效的Sheet頁名稱集合2:System.Data.DataTableschemaTable=objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);3:List<string>lstSheetNames=newList<string>。；4:for(inti=0;i<schemaTable.Rows.Count;i++)TOC\o"1-5"\h\z5: {6:stringstrSheetName=(string)dtSheetName.Rows[i]["TABLE_NAME“];7:if(strSheetName.Contains("$")&&!strSheetName.Replace('"","").EndsWith("$"))8: {9://過濾無效SheetName完畢….10:continue;11: }

12:if(IstSheetNames!=null&&llstSheetNames.Contains(strSheetName))13:lstSheetNames.Add(strSheetName);14: }因?yàn)樽x取出來無效SheetName一般情況最后一個字符都不會是$。如果SheetName有一些特殊符號，讀取出來的SheetName會自動加上單引號。比如在Excel中將SheetName編輯成MySheet(l)，此忖讀取出來的SheetName就為：'MySheet(1)$'，所以判斷最后一個字符是不是$之前最好過濾一下單引號。(2)Com組件的方式(通過添加Microsoft.Office.Interop.Excel引用實(shí)現(xiàn))優(yōu)點(diǎn)：能夠非常靈活的讀取Excel中的數(shù)據(jù)，用戶可以靈活的調(diào)用各種函數(shù)進(jìn)行處理。缺點(diǎn)：基于單元格的處理，讀取速度較慢，對于數(shù)據(jù)量較大的文件最好不要使用此種方式讀取。需要添加相應(yīng)的DLL引用，必須存在此引用才可使用，如果是Web站點(diǎn)部署在IIS上時，還需要服務(wù)器機(jī)子已安裝了Excel，有忖候還需要為配置IIS權(quán)限。讀取代碼如下：1:privateStopwatchwath=newStopwatch。；2:///<summary>3:///使用COM讀取Excel

4:///</summary>5:///<paramname="excelFilePath">路徑</param>6:///<returns>DataTabel</returns>7:publicSystem.Data.DataTableGetExcelData(stringexcelFilePath)8：(9:Excel.Applicationapp=newExcel.Application();10:Excel.Sheetssheets;11:Excel.Workbookworkbook=null;12:objectoMissiong=System.Reflection.Missing.Value;13:System.Data.DataTabledt=newSystem.Data.DataTable();14:wath.Start();15:try16: {17:if(app==null)18:19:returnnull;20:21:workbook=app.Workbooks.Open(excelFilePath,oMissiong,21:oMissiong,oMissiong,oMissiong,oMissiong,22:oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);23://將數(shù)據(jù)讀入到23://將數(shù)據(jù)讀入到DataTable中Start24:sheets=workbook.Worksheets;24:25:Excel.Worksheetworksheet=(Excel.Worksheet)sheets.get_Item(1);//讀取第一張表26:if(worksheet==null)27:returnnull;28:stringcellContent;29:intiRowCount=worksheet.UsedRange.Rows.Count;30:intiColCount=worksheet.UsedRange.Columns.Count;31:Excel.Rangerange;32://負(fù)責(zé)列頭Start33:DataColumndc;34:intColumnID=1;35: range=(Excel.Range)worksheet.Cells[1,1];36:while(range.Text.ToString().Trim()!=""){dc=newDataColumn();39:dc.DataType=System.Type.GetType("System.String");40:dc.ColumnName=range.Text.ToString().Trim();41:dt.Columns.Add(dc);range=(Excel.Range)worksheetCells[1,++ColumnID];44:

45://End46:for(intiRow=2;iRow<=iRowCount;iRow++)TOC\o"1-5"\h\z47: {48:DataRowdr=dt.NewRow();49:for(intiCol=1;iCol<=iColCount;iCol++){range=(Excel.Range)worksheetCells[iRow,iCol];52:cellContent=(range.Value2==null)?"":range.Text.ToString();53:dr[iCol-1]=cellContent;54: }55:dt.Rows.Add(dr);56: }57:wath.Stop();58:TimeSpants=wath.Elapsed;59://將數(shù)據(jù)讀入到DataTable中——End60:returndt;TOC\o"1-5"\h\z}62:catch{64:returnnull;}66:finally

67:68:workbook.Close(false,oMissiong,oMissiong);69:System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);70: workbook=null;71:app.Workbooks.Close();72:app.Quit();73:System.Runtime.InteropServices.Marshal.ReleaseComObject(app);74: app=null;75:GC.Collect();76:GC.WaitForPendingFinalizers();}}79:///<summary>80:///使用COM,多線程讀取Excel(1主線程、4副線程)81:///</summary>82:///<paramname="excelFilePath”>路徑</param>83:///<returns>DataTabel</returns>84:publicSystem.Data.DataTableThreadReadExcel(stringexcelFilePath){86:Excel.Applicationapp=newExcel.Application();87:Excel.Sheetssheets=null;88:Excel.Workbookworkbook=null;

89:objectoMissiong=System.Reflection.Missing.Value;90:System.Data.DataTabledt=newSystem.Data.DataTable();91:wath.Start();92:tryTOC\o"1-5"\h\z{94:if(app==null){96:returnnull;}workbook=app.Workbooks.Open(excelFilePath, oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,99:oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong);100:〃將數(shù)據(jù)讀入到DataTable中——Start101: sheets=workbook.Worksheets;102:Excel.Worksheetworksheet=(Excel.Worksheet)sheets.get_Item(1);//讀取第一張表103:if(worksheet==null)104:returnnull;105:stringcellContent;106:intiRowCount=worksheet.UsedRange.Rows.Count;107:intiColCount=worksheet.UsedRange.Columns.Count;

108:Excel.Rangerange;109:〃負(fù)責(zé)列頭Start110:DataColumndc;111:intColumnID=1;112: range=(Excel.Range)worksheet.Cells[1,1];113:while(iColCount>=ColumnID){dc=newDataColumn。；116:dc.DataType=System.Type.GetType("System.String“)；117:stringstrNewColumnName=range.TextToStringO.Trim。；118:if(strNewColumnName.Length==0)strNewColumnName="_1";119:〃判斷列名是否重復(fù)120:for(inti=1;i<ColumnID;i++)TOC\o"1-5"\h\z121: {122:if(dt.Columns[i-1].ColumnName==strNewColumnName)123:strNewColumnName=strNewColumnName+"_1”;124: }125:dc.ColumnName=strNewColumnName;126:dt.Columns.Add(dc);range=(Excel.Range)worksheet.Cells[1,++ColumnID];}129://End

130:〃數(shù)據(jù)大于500條，使用多進(jìn)程進(jìn)行讀取數(shù)據(jù)131:if(iRowCount-1>500)132: {133:〃開始多線程讀取數(shù)據(jù)134:〃新建線程135:intb2=(iRowCount-1)/10;136:DataTabledt1=newDataTable("dt1");dt1=dtClone。；138:SheetOptionssheet1thread=newSheetOptions(worksheet,iColCount,2,b2+1,dt1);Threadothread1=newThread(newThreadStart(sheet1thread.SheetToDataTable));140:othread1.Start();141://阻塞1毫秒，保證第一個讀取dt1142:Thread.Sleep(1);143:DataTabledt2=newDataTable("dt2");dt2=dtClone。；145:SheetOptionssheet2thread=newSheetOptions(worksheet,iColCount,b2+2,b2*2+1,dt2);Threadothread2=newThread(newThreadStart(sheet2thread.SheetToDataTable));147:othread2.Start();

148:DataTabledt3=newDataTable("dt3n);149: dt3=dtClone。；150:SheetOptionssheet3thread=newSheetOptions(worksheet,iColCount,b2*2+2,b2*3+1,dt3);151: Threadothread3=newThread(newThreadStart(sheet3thread.SheetToDataTable));152:othread3.Start();153:DataTabledt4=newDataTable("dt4");154: dt4=dt.Clone();155:SheetOptionssheet4thread=newSheetOptions(worksheet,iColCount,b2*3+2,b2*4+1,dt4);156: Threadothread4=newThread(newThreadStart(sheet4thread.SheetToDataTable));157:othread4.Start();158:〃主線程讀取剩余數(shù)據(jù)159:for(intiRow=b2*4+2;iRow<=iRowCount;iRow++)160: {161:DataRowdr=dt.NewRow();162:for(intiCol=1;iCol<=iColCount;iCol++){range=(Excel.Range)worksheet.Celis[iRow,iCol];165:cellContent=(range.Value2==null)?"":range.Text.ToString();

166:dr[iCol-1]=cellContent;167: }168:dt.Rows.Add(dr);169: }170:othread1Join();171:othread2Join();172:othread3JoinO;173:othread4JoinO;174:〃將多個線程讀取出來的數(shù)據(jù)追加至dt1后面175:foreach(DataRowdrindt.Rows)176:dt1.Rows.Add(dr.ItemArray);177:dt.Clear();178:dt.Dispose();179:foreach(DataRowdrindt2.Rows)180:dt1.Rows.Add(dr.ItemArray);181:dt2.Clear();182:dt2.Dispose();183:foreach(DataRowdrindt3.Rows)184:dt1.Rows.Add(dr.ItemArray);185:dt3.Clear();186:dt3.Dispose();187:foreach(DataRowdrindt4.Rows)

188:dt1.Rows.Add(dr.ItemArray);189:dt4.Clear();190:dt4.Dispose();191:returndt1;TOC\o"1-5"\h\z192: }193:else194: {195:for(intiRow=2;iRow<=iRowCount;iRow++)196: {197:DataRowdr=dtNewRow。；198:for(intiCol=1;iCol<=iColCount;iCol++){range=(Excel.Range)worksheet.Cells[iRow,iCol];201:cellContent=(range.Value2==null)?"":range.Text.ToString();202:dr[iCol-1]=cellContent;203: }204:dt.Rows.Add(dr);}}207:wath.Stop();208:TimeSpants=wath.Elapsed;209:〃將數(shù)據(jù)讀入到DataTable中——End

210:returndt;TOC\o"1-5"\h\z211: }212:catch213: {214:returnnull;215: }216:finally217: {218:workbook.Close(false,oMissiong,oMissiong);219:System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);220:System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);221: workbook=null;222:app.Workbooks.Close();223:app.Quit();224:System.Runtime.InteropServices.Marshal.ReleaseComObject(app);225: app=null;226:GC.Collect();227:GC.WaitForPendingFinalizers();}}(3)NPOI方式讀取Excel(此方法未經(jīng)過測試)

NPOI是POI項(xiàng)目的.NET版本。POI是一個開源的Java讀寫Excel、WORD等微軟OLE2組件文檔的項(xiàng)目。使用NPOI你就可以在沒有安裝Office或者相應(yīng)環(huán)境的機(jī)器上對WORD/EXCEL文檔進(jìn)行讀寫。優(yōu)點(diǎn)：讀取Excel速度較快，讀取方式操作靈活性缺點(diǎn)：需要下載相應(yīng)的插件并添加到系統(tǒng)引用當(dāng)中。1:///<summary>2:///將excel中的數(shù)據(jù)導(dǎo)入到DataTable中3:///</summary>4:///<paramname="sheetName”>excel工作薄sheet的名稱</param>5:///<paramname="isFirstRowColumn">第一行是否是DataTable的列名</param>6:///<m皿狽>返回的DataTable</returns>7:publicDataTableExcelToDataTable(stringsheetName,boolisFirstRowColumn)8:{9:ISheetsheet=null;10:DataTabledata=newDataTable。；11:intstartRow=0;12:try{fs=newFileStream(fileName,FileMode.Open,FileAccess.Read);15:if(fleName.IndexOf(".xlsx")>0)//2007版本

16:workbook=newXSSFWorkbook(fs);16:17:elseif(fileName.IndexOf