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

概述本文实例讲述了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 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);        //将数据读入到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);        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#程序设计有所帮助。

总结

以上是内存溢出为你收集整理的C#基于COM方式读取Excel表格的方法全部内容，希望文章能够帮你解决C#基于COM方式读取Excel表格的方法所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。