C#基于COM方式读取Excel表格的方法
本文实例讲述了C#基于COM方式读取Excel表格的方法。分享给大家供大家参考,具体如下:
usingSystem; usingSystem.Collections.Generic; usingSystem.Collections.ObjectModel; usingSystem.Data; usingSystem.Linq; usingSystem.Text; usingSystem.Threading.Tasks; usingSystem.Windows; usingSystem.Collections; //TestEnviroment:VS2013Update4Excel2007 //ReadbyCOMObject namespaceSmartStore.LocalModel { publicclassExcelTable { privatestring_path; publicExcelTable() { _path=System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase; _path+="条码对照表.xls"; } publicvoidReadEPC2BarCode(outArrayListarrayPI) { DataTabledt=ReadSheet(2); arrayPI=newArrayList(); foreach(DataRowdrindt.Rows) { EPC2BarCodeeb=newEPC2BarCode(); eb.EPC=(string)dr["epcID"]; eb.Barcode=(string)dr["条形码"]; eb.EPC=eb.EPC.Trim(); eb.Barcode=eb.Barcode.Trim(); if(eb.EPC==null||eb.EPC.Length<=0) break; arrayPI.Add(eb); } } publicvoidReadProductInfo(outArrayListarrayPI) { DataTabledt=ReadSheet(1); arrayPI=newArrayList(); foreach(DataRowdrindt.Rows) { ProductInfopi=newProductInfo(); pi.Name=(string)dr["商品名称"]; pi.SN=(string)dr["商品编号"]; pi.BarCode=(string)dr["商品条码"]; pi.Brand=(string)dr["品牌"]; pi.Color=(string)dr["颜色"]; pi.Size=(string)dr["尺码"]; pi.Name=pi.Name.Trim(); pi.SN=pi.SN.Trim(); pi.BarCode=pi.BarCode.Trim(); pi.Brand=pi.Brand.Trim(); pi.Color=pi.Color.Trim(); pi.Size=pi.Size.Trim(); if(pi.Name==null||pi.Name.Length<=0) break; arrayPI.Add(pi); } } privateDataTableReadSheet(intindexSheet) { Microsoft.Office.Interop.Excel.Applicationapp=newMicrosoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Sheetssheets; Microsoft.Office.Interop.Excel.Workbookworkbook=null; objectoMissiong=System.Reflection.Missing.Value; System.Data.DataTabledt=newSystem.Data.DataTable(); try { workbook=app.Workbooks.Open(_path,oMissiong,oMissiong,oMissiong,oMissiong, oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong,oMissiong); //将数据读入到DataTable中——Start sheets=workbook.Worksheets; //输入1,读取第一张表 Microsoft.Office.Interop.Excel.Worksheetworksheet=(Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(indexSheet); if(worksheet==null) returnnull; stringcellContent; intiRowCount=worksheet.UsedRange.Rows.Count; intiColCount=worksheet.UsedRange.Columns.Count; Microsoft.Office.Interop.Excel.Rangerange; //负责列头Start DataColumndc; intColumnID=1; range=(Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1,1]; while(range.Text.ToString().Trim()!="") { dc=newDataColumn(); dc.DataType=System.Type.GetType("System.String"); dc.ColumnName=range.Text.ToString().Trim(); dt.Columns.Add(dc); range=(Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1,++ColumnID]; } //End for(intiRow=2;iRow<=iRowCount;iRow++) { DataRowdr=dt.NewRow(); for(intiCol=1;iCol<=iColCount;iCol++) { range=(Microsoft.Office.Interop.Excel.Range)worksheet.Cells[iRow,iCol]; cellContent=(range.Value2==null)?"":range.Text.ToString(); //if(iRow==1) //{ //dt.Columns.Add(cellContent); //} //else //{ dr[iCol-1]=cellContent; //} } //if(iRow!=1) dt.Rows.Add(dr); } //将数据读入到DataTable中——End returndt; } catch { returnnull; } finally { workbook.Close(false,oMissiong,oMissiong); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook=null; app.Workbooks.Close(); app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app=null; GC.Collect(); GC.WaitForPendingFinalizers(); } } } }
更多关于C#相关内容感兴趣的读者可查看本站专题:《C#操作Excel技巧总结》、《C#程序设计之线程使用技巧总结》、《C#中XML文件操作技巧汇总》、《C#常见控件用法教程》、《WinForm控件用法总结》、《C#数据结构与算法教程》、《C#数组操作技巧总结》及《C#面向对象程序设计入门教程》
希望本文所述对大家C#程序设计有所帮助。