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#程序设计有所帮助。