本文实例讲述了C#基于COM方式读取Excel表格的方法。分享给大家供大家参考，具体如下：
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Collections;
//TestEnviroment:VS2013Update4 Excel2007
//Read by COM Object
namespace SmartStore.LocalModel
{
public class ExcelTable
{
private string _path;
public ExcelTable()
{
_path = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
_path += "条码对照表.xls" ;
}
public void ReadEPC2BarCode( out ArrayList arrayPI)
{
DataTable dt = ReadSheet(2);
arrayPI = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
EPC2BarCode eb = new EPC2BarCode();
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);
}
}
public void ReadProductInfo( out ArrayList arrayPI)
{
DataTable dt = ReadSheet(1);
arrayPI = new ArrayList();
foreach (DataRow dr in dt.Rows)
{
ProductInfo pi = new ProductInfo();
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);
}
}
private DataTable ReadSheet( int indexSheet)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Sheets sheets;
Microsoft.Office.Interop.Excel.Workbook workbook = null ;
object oMissiong = System.Reflection.Missing.Value;
System.Data.DataTable dt = new System.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.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(indexSheet);
if (worksheet == null )
return null ;
string cellContent;
int iRowCount = worksheet.UsedRange.Rows.Count;
int iColCount = worksheet.UsedRange.Columns.Count;
Microsoft.Office.Interop.Excel.Range range;
//负责列头Start
DataColumn dc;
int ColumnID = 1;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1];
while (range.Text.ToString().Trim() != "" )
{
dc = new DataColumn();
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 ( int iRow = 2; iRow <= iRowCount; iRow++)
{
DataRow dr = dt.NewRow();
for ( int iCol = 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
return dt;
}
catch
{
return null ;
}
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#程序设计有所帮助。
dy(“nrwz”);
