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 Objectnamespace 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();      }    }  }}