欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-05-18 14:19:26
本文实例讲述了c#基于com方式读取excel表格的方法。分享给大家供大家参考,具体如下: using system; using system.collect...

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