C#基于COM方式读取Excel表格的方法

本文实例讲述了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#程序设计有所帮助。