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#相关内容感兴趣的读者可查看本站专题：《C#操作Excel技巧总结》、《C#程序设计之线程使用技巧总结》、《C#中XML文件操作技巧汇总》、《C#常见控件用法教程》、《WinForm控件用法总结》、《C#数据结构与算法教程》、《C#数组操作技巧总结》及《C#面向对象程序设计入门教程》

希望本文所述对大家C#程序设计有所帮助。