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#程序设计有所帮助。