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

C#使用NOPI库实现导入Excel文档

程序员文章站 2023-12-01 22:39:40
使用nopi导入excel文档 nopi版本:2.3.0,依赖于npoi的sharpziplib版本:0.86,经测试适用于.net4.0+ 记录遇到的几个问题...

使用nopi导入excel文档

nopi版本:2.3.0,依赖于npoi的sharpziplib版本:0.86,经测试适用于.net4.0+

记录遇到的几个问题

1.nopi中的iworkbook接口:xls使用hssfworkbook类实现,xlsx使用xssfworkbook类实现

2.日期转换,判断row.getcell(j).celltype == npoi.ss.usermodel.celltype.numeric && hssfdateutil.iscelldateformatted(row.getcell(j)

不能直接使用row.getcell(j).datecellvalue,这玩意会直接抛出异常来~

1. 将文件流转换为datatable

  /// <summary>
  /// 根据excel格式读取excel
  /// </summary>
  /// <param name="stream">文件流</param>
  /// <param name="type">excel格式枚举类型,xls/xlsx</param>
  /// <param name="sheetname">表名,默认取第一张</param>
  /// <returns>datatable</returns>
  private static datatable importexcel(stream stream, excelexttype type, string sheetname)
  {
    datatable dt = new datatable();
    iworkbook workbook;
    try
    {
      //xls使用hssfworkbook类实现,xlsx使用xssfworkbook类实现
      switch (type)
      {
        case excelexttype.xlsx:
          workbook = new xssfworkbook(stream);
          break;
        default:
          workbook = new hssfworkbook(stream);
          break;
      }
      isheet sheet = null;
      //获取工作表 默认取第一张
      if (string.isnullorwhitespace(sheetname))
        sheet = workbook.getsheetat(0);
      else
        sheet = workbook.getsheet(sheetname);

      if (sheet == null)
        return null;
      ienumerator rows = sheet.getrowenumerator();
      #region 获取表头
      irow headerrow = sheet.getrow(0);
      int cellcount = headerrow.lastcellnum;
      for (int j = 0; j < cellcount; j++)
      {
        icell cell = headerrow.getcell(j);
        if (cell != null)
        {
          dt.columns.add(cell.tostring());
        }
        else
        {
          dt.columns.add("");
        }
      }
      #endregion
      #region 获取内容
      for (int i = (sheet.firstrownum + 1); i <= sheet.lastrownum; i++)
      {
        irow row = sheet.getrow(i);
        datarow datarow = dt.newrow();

        for (int j = row.firstcellnum; j < cellcount; j++)
        {
          if (row.getcell(j) != null)
          {
            //判断单元格是否为日期格式
            if (row.getcell(j).celltype == npoi.ss.usermodel.celltype.numeric && hssfdateutil.iscelldateformatted(row.getcell(j)))
            {
              if (row.getcell(j).datecellvalue.year >=1970)
              {
                datarow[j] = row.getcell(j).datecellvalue.tostring();
              }
              else
              {
                datarow[j] = row.getcell(j).tostring();

              }
            }
            else
            {
              datarow[j] = row.getcell(j).tostring();
            }
          }
        }
        dt.rows.add(datarow);
      }
      #endregion

    }
    catch (exception ex)
    {
      dt=null;
    }
    finally
    {
      //if (stream != null)
      //{
      //  stream.close();
      //  stream.dispose();
      //}
    }
    return dt;
  }

2. 文件上载导入

  /// <summary>
  /// 上传excel导入
  /// </summary>
  /// <param name="file">上载文件对象</param>
  /// <param name="errormsg">错误信息</param>
  /// <param name="sheetname">表名,默认取第一张</param>
  /// <returns></returns>
  public static datatable import(system.web.httppostedfilebase file, ref string errormsg, string sheetname = "")
  {
    if (file == null || file.inputstream == null || file.inputstream.length == 0)
    {
      errormsg = "请选择要导入的excel文件";
      return null;
    }
    var exceltype = getexcelfiletype(file.filename);
    if (exceltype == null)
    {
      errormsg = "请选择正确的excel文件";
      return null;
    }
    using (var stream = new memorystream())
    {
      file.inputstream.position = 0;
      file.inputstream.copyto(stream);
      var dt = importexcel(stream, exceltype.value, sheetname);
      if (dt == null)
        errormsg = "导入失败,请选择正确的excel文件";
      return dt;
    }
  }

3. 本地路径读取导入
 

  /// <summary>
  /// 根据文件路径导入excel
  /// </summary>
  /// <param name="filepath"></param>
  /// <param name="errormsg">错误信息</param>
  /// <param name="sheetname">表名,默认取第一张</param>
  /// <returns>可能为null的datatable</returns>
  public static datatable import(string filepath, ref string errormsg, string sheetname = "")
  {
    var exceltype = getexcelfiletype(filepath);
    if (getexcelfiletype(filepath) == null)
    {
      errormsg = "请选择正确的excel文件";
      return null;
    }
    if (!file.exists(filepath))
    {
      errormsg = "没有找到要导入的excel文件";
      return null;
    }
    datatable dt;
    using (var stream = new filestream(filepath, filemode.open, fileaccess.read, fileshare.readwrite))
    {
      dt = importexcel(stream, exceltype.value, sheetname);
    }
    if (dt == null)
      errormsg = "导入失败,请选择正确的excel文件";
    return dt;
  }

4.完整demo

附赠一个winform导入excel的demo。

https://github.com/yimogit/nopiexceldemo