详解免费高效实用的.NET操作Excel组件NPOI（.NET组件介绍之六）

很多的软件项目几乎都包含着对文档的操作，前面已经介绍过两款操作文档的组件，现在介绍一款文档操作的组件npoi。

npoi可以生成没有安装在您的服务器上的microsoft office套件的excel报表，并且在后台调用microsoft excel activex更有效率;从office文档中提取文本，以帮助您实现全文索引功能（大多数时候，此功能用于创建搜索引擎）； 从office文档提取图像； 生成包含公式的excel工作表。

 一.npoi组件概述：

npoi是完全免费使用； 涵盖excel的大多数功能（单元格样式，数据格式，公式等）；支持xls，xlsx，docx；设计为面向接口（看看npoi.ss命名空间）；支持不仅导出而且导入； .net 2.0甚至为xlsx和docx（虽然我们也支持.net 4.0）； 来自世界各地的成功案例；巨大的基本例子；对隔离存储没有依赖。

 以上是npoi的优点，其他一些优点可以不用太在意，估计很多人对“支持xls，xlsx，docx”这一特点感觉有些惊讶，因为在很多人的印象里面npoi就是对excel进行相关的操作，但是在这里突然看到了对docx也可以操作，这一特点可能让很多人感到欣喜，因为npoi的的确确是一个很不错的组件，用过的人都说好，我也不例外。

npoi的运行要求：vs2010与.net 4.0运行时；vs2005或vs2008与.net 2.0运行时（sp1）；vs2003与.net 1.1；mono；asp.net中的中等信任环境。

二.npoi核心类和方法解析：

以上是对npoi的相关背景和使用环境做了一个简单的介绍，接下来我具体的看一下npoi的一些核心类和方法，由于下载的是dll文件，还是采用.net reflector对dll文件进行反编译，以此查看源代码。

如果需要具体的了解npoi可以直接访问：http://npoi.codeplex.com/sourcecontrol/latest，提供了npoi的源码和一些demo，由于本溪介绍的重点是npoi对excel的操作，所以下面的类和实例主要是对操作excel的介绍，如果需要对docx的操作，可以具体查看相应的类demo。

1.xssfworkbook类createsheet():创建表。

public isheet createsheet(string sheetname)
{
  if (sheetname == null)
  {
    throw new argumentexception("sheetname must not be null");
  }
  if (this.containssheet(sheetname, this.sheets.count))
  {
    throw new argumentexception("the workbook already contains a sheet of this name");
  }
  if (sheetname.length > 0x1f)
  {
    sheetname = sheetname.substring(0, 0x1f);
  }
  workbookutil.validatesheetname(sheetname);
  ct_sheet sheet = this.addsheet(sheetname);
  int index = 1;
  foreach (xssfsheet sheet2 in this.sheets)
  {
    index = (int) math.max((long) (sheet2.sheet.sheetid + 1), (long) index);
  }
label_0099:
  foreach (xssfsheet sheet3 in this.sheets)
  {
    index = (int) math.max((long) (sheet3.sheet.sheetid + 1), (long) index);
  }
  string filename = xssfrelation.worksheet.getfilename(index);
  foreach (poixmldocumentpart part in base.getrelations())
  {
    if ((part.getpackagepart() != null) && filename.equals(part.getpackagepart().partname.name))
    {
      index++;
      goto label_0099;
    }
  }
  xssfsheet item = (xssfsheet) base.createrelationship(xssfrelation.worksheet, xssffactory.getinstance(), index);
  item.sheet = sheet;
  sheet.id = item.getpackagerelationship().id;
  sheet.sheetid = (uint) index;
  if (this.sheets.count == 0)
  {
    item.isselected = true;
  }
  this.sheets.add(item);
  return item;
}

2.xssfsheet类write():将文件流写入到excel。
 

 internal virtual void write(stream stream)
{
  bool flag = false;
  if (this.worksheet.sizeofcolsarray() == 1)
  {
    ct_cols colsarray = this.worksheet.getcolsarray(0);
    if (colsarray.sizeofcolarray() == 0)
    {
      flag = true;
      this.worksheet.setcolsarray(null);
    }
    else
    {
      this.setcolwidthattribute(colsarray);
    }
  }
  if (this.hyperlinks.count > 0)
  {
    if (this.worksheet.hyperlinks == null)
    {
      this.worksheet.addnewhyperlinks();
    }
    ct_hyperlink[] array = new ct_hyperlink[this.hyperlinks.count];
    for (int i = 0; i < array.length; i++)
    {
      xssfhyperlink hyperlink = this.hyperlinks[i];
      hyperlink.generaterelationifneeded(base.getpackagepart());
      array[i] = hyperlink.getcthyperlink();
    }
    this.worksheet.hyperlinks.sethyperlinkarray(array);
  }
  foreach (xssfrow row in this._rows.values)
  {
    row.ondocumentwrite();
  }
  dictionary<string, string> dictionary = new dictionary<string, string>();
  dictionary[st_relationshipid.namespaceuri] = "r";
  new worksheetdocument(this.worksheet).save(stream);
  if (flag)
  {
    this.worksheet.addnewcols();
  }
}

3.xssfsheet类createrow()：创建行。

 public virtual irow createrow(int rownum)
{
  ct_row ctrow;
  xssfrow row2 = this._rows.containskey(rownum) ? this._rows[rownum] : null;
  if (row2 != null)
  {
    ctrow = row2.getctrow();
    ctrow.set(new ct_row());
  }
  else if ((this._rows.count == 0) || (rownum > this.getlastkey(this._rows.keys)))
  {
    ctrow = this.worksheet.sheetdata.addnewrow();
  }
  else
  {
    int count = this.headmap(this._rows, rownum).count;
    ctrow = this.worksheet.sheetdata.insertnewrow(count);
  }
  xssfrow row3 = new xssfrow(ctrow, this) {
    rownum = rownum
  };
  this._rows[rownum] = row3;
  return row3;
}

4.xssfworkbook类getsheet：获取表。

 public isheet getsheet(string name)
{
  foreach (xssfsheet sheet in this.sheets)
  {
    if (name.equals(sheet.sheetname, stringcomparison.invariantcultureignorecase))
    {
      return sheet;
    }
  }
  return null;
}

5.workbookfactory类：

 public class propertysetfactory
 {
  public static propertyset create(directoryentry dir, string name);
  public static propertyset create(stream stream);
  public static summaryinformation createsummaryinformation();
  public static documentsummaryinformation createdocumentsummaryinformation();
 }

6.documentsummaryinformation：

 [serializable]
public class documentsummaryinformation : specialpropertyset
{
  // fields
  public const string default_stream_name = "\x0005documentsummaryinformation";

  // methods
  public documentsummaryinformation(propertyset ps);
  private void ensuresection2();
  public void removebytecount();
  public void removecategory();
  public void removecompany();
  public void removecustomproperties();
  public void removedocparts();
  public void removeheadingpair();
  public void removehiddencount();
  public void removelinecount();
  public void removelinksdirty();
  public void removemanager();
  public void removemmclipcount();
  public void removenotecount();
  public void removeparcount();
  public void removepresentationformat();
  public void removescale();
  public void removeslidecount();

  // properties
  public int bytecount { get; set; }
  public string category { get; set; }
  public string company { get; set; }
  public customproperties customproperties { get; set; }
  public byte[] docparts { get; set; }
  public byte[] headingpair { get; set; }
  public int hiddencount { get; set; }
  public int linecount { get; set; }
  public bool linksdirty { get; set; }
  public string manager { get; set; }
  public int mmclipcount { get; set; }
  public int notecount { get; set; }
  public int parcount { get; set; }
  public string presentationformat { get; set; }
  public override propertyidmap propertysetidmap { get; }
  public bool scale { get; set; }
  public int slidecount { get; set; }
}

具体方法：

private void ensuresection2()
{
  if (this.sectioncount < 2)
  {
    mutablesection section = new mutablesection();
    section.setformatid(sectionidmap.document_summary_information_id2);
    this.addsection(section);
  }
}

以上只是对部分的类和方法做了简单的说明，需要了解更多的内容，可以进入官网下载源码，或者使用软件查看dll文件。

三.npoi操作实例：

1.枚举(excel单元格数据类型)：

  /// <summary>
  /// 枚举(excel单元格数据类型)
  /// </summary>
  public enum npoidatatype
  {
    /// <summary>
    /// 字符串类型-值为1
    /// </summary>
    string,
    /// <summary>
    /// 布尔类型-值为2
    /// </summary>
    bool,
    /// <summary>
    /// 时间类型-值为3
    /// </summary>
    datetime,
    /// <summary>
    /// 数字类型-值为4
    /// </summary>
    numeric,
    /// <summary>
    /// 复杂文本类型-值为5
    /// </summary>
    richtext,
    /// <summary>
    /// 空白
    /// </summary>
    blank,
    /// <summary>
    /// 错误
    /// </summary>
    error
  }

2. 将datatable数据导入到excel中：

    

 /// <summary>
    /// 将datatable数据导入到excel中
    /// </summary>
    /// <param name="data">要导入的数据</param>
    /// <param name="iscolumnwritten">datatable的列名是否要导入</param>
    /// <param name="sheetname">要导入的excel的sheet的名称</param>
    /// <param name="filename">文件夹路径</param>
    /// <returns>导入数据行数(包含列名那一行)</returns>
    public static int datatabletoexcel(datatable data, string sheetname, bool iscolumnwritten, string filename)
    {
      if (data == null)
      {
        throw new argumentnullexception("data");
      }
      if (string.isnullorempty(sheetname))
      {
        throw new argumentnullexception(sheetname);
      }
      if (string.isnullorempty(filename))
      {
        throw new argumentnullexception(filename);
      }
      iworkbook workbook = null;
      if (filename.indexof(".xlsx", stringcomparison.ordinal) > 0)
      {
        workbook = new xssfworkbook();
      }        
      else if (filename.indexof(".xls", stringcomparison.ordinal) > 0)
      {
        workbook = new hssfworkbook();
      }

      filestream fs = null;
      try
      {
        fs = new filestream(filename, filemode.openorcreate, fileaccess.readwrite);
        isheet sheet;
        if (workbook != null)
        {
          sheet = workbook.createsheet(sheetname);
        }
        else
        {
          return -1;
        }

        int j;
        int count;
        //写入datatable的列名，写入单元格中
        if (iscolumnwritten)
        {
          var row = sheet.createrow(0);
          for (j = 0; j < data.columns.count; ++j)
          {
            row.createcell(j).setcellvalue(data.columns[j].columnname);
          }
          count = 1;
        }
        else
        {
          count = 0;
        }
        //遍历循环datatable具体数据项
        int i;
        for (i = 0; i < data.rows.count; ++i)
        {
          var row = sheet.createrow(count);
          for (j = 0; j < data.columns.count; ++j)
          {
            row.createcell(j).setcellvalue(data.rows[i][j].tostring());
          }
          ++count;
        }
        //将文件流写入到excel
        workbook.write(fs);
        return count;
      }
      catch (ioexception ioex)
      {
        throw new ioexception(ioex.message);
      }
      catch (exception ex)
      {
        throw new exception(ex.message);
      }
      finally
      {
        if (fs != null)
        {
          fs.close();
        }
      }
    }

3.将excel中的数据导入到datatable中：

  /// <summary>
    /// 将excel中的数据导入到datatable中
    /// </summary>
    /// <param name="sheetname">excel工作薄sheet的名称</param>
    /// <param name="isfirstrowcolumn">第一行是否是datatable的列名</param>
    /// <param name="filename">文件路径</param>
    /// <returns>返回的datatable</returns>
    public static datatable exceltodatatable(string sheetname, bool isfirstrowcolumn, string filename)
    {
      if (string.isnullorempty(sheetname))
      {
        throw new argumentnullexception(sheetname);
      }
      if (string.isnullorempty(filename))
      {
        throw new argumentnullexception(filename);
      }
      var data = new datatable();
      iworkbook workbook = null;
      filestream fs = null;
      try
      {
        fs = new filestream(filename, filemode.open, fileaccess.read);
        if (filename.indexof(".xlsx", stringcomparison.ordinal) > 0)
        {
          workbook = new xssfworkbook(fs);
        }
        else if (filename.indexof(".xls", stringcomparison.ordinal) > 0)
        {
          workbook = new hssfworkbook(fs);
        }

        isheet sheet = null;
        if (workbook != null)
        {
          //如果没有找到指定的sheetname对应的sheet，则尝试获取第一个sheet
          sheet = workbook.getsheet(sheetname) ?? workbook.getsheetat(0);
        }
        if (sheet == null) return data;
        var firstrow = sheet.getrow(0);
        //一行最后一个cell的编号 即总的列数
        int cellcount = firstrow.lastcellnum;
        int startrow;
        if (isfirstrowcolumn)
        {
          for (int i = firstrow.firstcellnum; i < cellcount; ++i)
          {
            var cell = firstrow.getcell(i);
            var cellvalue = cell.stringcellvalue;
            if (cellvalue == null) continue;
            var column = new datacolumn(cellvalue);
            data.columns.add(column);
          }
          startrow = sheet.firstrownum + 1;
        }
        else
        {
          startrow = sheet.firstrownum;
        }
        //最后一列的标号
        var rowcount = sheet.lastrownum;
        for (var i = startrow; i <= rowcount; ++i)
        {
          var row = sheet.getrow(i);
          //没有数据的行默认是null
          if (row == null) continue;
          var datarow = data.newrow();
          for (int j = row.firstcellnum; j < cellcount; ++j)
          {
            //同理，没有数据的单元格都默认是null
            if (row.getcell(j) != null)
              datarow[j] = row.getcell(j).tostring();
          }
          data.rows.add(datarow);
        }

        return data;
      }
      catch (ioexception ioex)
      {
        throw new ioexception(ioex.message);
      }
      catch (exception ex)
      {
        throw new exception(ex.message);
      }
      finally
      {
        if (fs != null)
        {
          fs.close();
        }
      }
    }

4.读取excel文件内容转换为dataset：

  /// <summary>
    /// 读取excel文件内容转换为dataset,列名依次为 "c0"……c[columnlength-1]
    /// </summary>
    /// <param name="filename">文件绝对路径</param>
    /// <param name="startrow">数据开始行数(1为第一行)</param>
    /// <param name="columndatatype">每列的数据类型</param>
    /// <returns></returns>
    public static dataset readexcel(string filename, int startrow, params npoidatatype[] columndatatype)
    {
      var ds = new dataset("ds");
      var dt = new datatable("dt");
      var sb = new stringbuilder();
      using (var stream = new filestream(filename, filemode.open, fileaccess.read))
      {
        //使用接口，自动识别excel2003/2007格式
        var workbook = workbookfactory.create(stream);
        //得到里面第一个sheet
        var sheet = workbook.getsheetat(0);
        int j;
        irow row;
        //columndatatype赋值
        if (columndatatype.length <= 0)
        {
          //得到第i行
          row = sheet.getrow(startrow - 1);
          columndatatype = new npoidatatype[row.lastcellnum];
          for (var i = 0; i < row.lastcellnum; i++)
          {
            var hs = row.getcell(i);
            columndatatype[i] = getcelldatatype(hs);
          }
        }
        for (j = 0; j < columndatatype.length; j++)
        {
          var tp = getdatatabletype(columndatatype[j]);
          dt.columns.add("c" + j, tp);
        }
        for (var i = startrow - 1; i <= sheet.physicalnumberofrows; i++)
        {
          //得到第i行
          row = sheet.getrow(i);
          if (row == null) continue;
          try
          {
            var dr = dt.newrow();

            for (j = 0; j < columndatatype.length; j++)
            {
              dr["c" + j] = getcelldata(columndatatype[j], row, j);
            }
            dt.rows.add(dr);
          }
          catch (exception er)
          {
            sb.append(string.format("第{0}行出错：{1}\r\n", i + 1, er.message));
          }
        }
        ds.tables.add(dt);
      }
      if (ds.tables[0].rows.count == 0 && sb.tostring() != "") throw new exception(sb.tostring());
      return ds;
    }

5.从dataset导出到2003：

  /// <summary>
    /// 从dataset导出到memorystream流2003
    /// </summary>
    /// <param name="savefilename">文件保存路径</param>
    /// <param name="sheetname">excel文件中的sheet名称</param>
    /// <param name="ds">存储数据的dataset</param>
    /// <param name="startrow">从哪一行开始写入，从0开始</param>
    /// <param name="datatypes">dataset中的各列对应的数据类型</param>
    public static bool createexcel2003(string savefilename, string sheetname, dataset ds, int startrow, params npoidatatype[] datatypes)
    {
      try
      {
        if (startrow < 0) startrow = 0;
        var wb = new hssfworkbook();
        var dsi = propertysetfactory.createdocumentsummaryinformation();
        dsi.company = "pkm";
        var si = propertysetfactory.createsummaryinformation();
        si.title =
        si.subject = "automatic genereted document";
        si.author = "pkm";
        wb.documentsummaryinformation = dsi;
        wb.summaryinformation = si;
        var sheet = wb.createsheet(sheetname);
        //sheet.setcolumnwidth(0, 50 * 256);
        //sheet.setcolumnwidth(1, 100 * 256);
        icell cell;
        int j;
        var maxlength = 0;
        var curlength = 0;
        object columnvalue;
        var dt = ds.tables[0];
        if (datatypes.length < dt.columns.count)
        {
          datatypes = new npoidatatype[dt.columns.count];
          for (var i = 0; i < dt.columns.count; i++)
          {
            var dtcolumntype = dt.columns[i].datatype.name.tolower();
            switch (dtcolumntype)
            {
              case "string":
                datatypes[i] = npoidatatype.string;
                break;
              case "datetime":
                datatypes[i] = npoidatatype.datetime;
                break;
              case "boolean":
                datatypes[i] = npoidatatype.bool;
                break;
              case "double":
                datatypes[i] = npoidatatype.numeric;
                break;
              default:
                datatypes[i] = npoidatatype.string;
                break;
            }
          }
        }

        // 创建表头
        var row = sheet.createrow(0);
        //样式
        var style1 = wb.createcellstyle();
        //字体
        var font1 = wb.createfont();
        //字体颜色
        font1.color = hssfcolor.white.index;
        //字体加粗样式
        font1.boldweight = (short)fontboldweight.bold;
        //style1.fillbackgroundcolor = hssfcolor.white.index;                              
        style1.fillforegroundcolor = hssfcolor.green.index;
        //getxlcolour(wb, levelonecolor);// 设置图案色
        //getxlcolour(wb, levelonecolor);// 设置背景色
        style1.fillpattern = fillpattern.solidforeground;
        //样式里的字体设置具体的字体样式
        style1.setfont(font1);
        //文字水平对齐方式
        style1.alignment = horizontalalignment.center;
        //文字垂直对齐方式
        style1.verticalalignment = verticalalignment.center;
        row.heightinpoints = 25;
        for (j = 0; j < dt.columns.count; j++)
        {
          columnvalue = dt.columns[j].columnname;
          curlength = encoding.default.getbytecount(columnvalue.tostring());
          maxlength = (maxlength < curlength ? curlength : maxlength);
          var colounwidth = 256 * maxlength;
          sheet.setcolumnwidth(j, colounwidth);
          try
          {
            //创建第0行的第j列
            cell = row.createcell(j);
            //单元格式设置样式
            cell.cellstyle = style1;

            try
            {
              cell.setcelltype(celltype.string);
              cell.setcellvalue(columnvalue.tostring());
            }
            catch (exception ex)
            {
              throw new exception(ex.message);
            }

          }
          catch (exception ex)
          {
            throw new exception(ex.message);
          }
        }
        // 创建每一行
        for (var i = startrow; i < ds.tables[0].rows.count; i++)
        {
          var dr = ds.tables[0].rows[i];
          //创建第i行
          row = sheet.createrow(i + 1);
          for (j = 0; j < dt.columns.count; j++)
          {
            columnvalue = dr[j];
            curlength = encoding.default.getbytecount(columnvalue.tostring());
            maxlength = (maxlength < curlength ? curlength : maxlength);
            var colounwidth = 256 * maxlength;
            sheet.setcolumnwidth(j, colounwidth);
            try
            {
              //创建第i行的第j列
              cell = row.createcell(j);
              // 插入第j列的数据
              try
              {
                var dtype = datatypes[j];
                switch (dtype)
                {
                  case npoidatatype.string:
                    {
                      cell.setcelltype(celltype.numeric);
                      cell.setcellvalue(columnvalue.tostring());
                    }
                    break;
                  case npoidatatype.datetime:
                    {
                      cell.setcelltype(celltype.numeric);
                      cell.setcellvalue(columnvalue.tostring());
                    }
                    break;
                  case npoidatatype.numeric:
                    {
                      cell.setcelltype(celltype.numeric);
                      cell.setcellvalue(convert.todouble(columnvalue));
                    }
                    break;
                  case npoidatatype.bool:
                    {
                      cell.setcelltype(celltype.numeric);
                      cell.setcellvalue(convert.toboolean(columnvalue));
                    }
                    break;
                  case npoidatatype.richtext:
                    {
                      cell.setcelltype(celltype.numeric);
                      cell.setcellvalue(columnvalue.tostring());
                    }
                    break;
                }
              }
              catch (exception ex)
              {
                cell.setcelltype(celltype.numeric);
                cell.setcellvalue(columnvalue.tostring());
                throw new exception(ex.message);
              }
            }
            catch (exception ex)
            {
              throw new exception(ex.message);
            }
          }
        }
        //生成文件在服务器上
        using (var fs = new filestream(savefilename, filemode.openorcreate, fileaccess.write))
        {
          wb.write(fs);
        }

        return true;
      }
      catch (exception er)
      {
        throw new exception(er.message);
      }

    }

 6.从dataset导出到memorystream流2007：

/// <summary>
    /// 从dataset导出到memorystream流2007
    /// </summary>
    /// <param name="savefilename">文件保存路径</param>
    /// <param name="sheetname">excel文件中的sheet名称</param>
    /// <param name="ds">存储数据的dataset</param>
    /// <param name="startrow">从哪一行开始写入，从0开始</param>
    /// <param name="datatypes">dataset中的各列对应的数据类型</param>
    public static bool createexcel2007(string savefilename, string sheetname, dataset ds, int startrow, params npoidatatype[] datatypes)
    {
      try
      {
        if (startrow < 0) startrow = 0;
        var wb = new xssfworkbook();
        var sheet = wb.createsheet(sheetname);
        icell cell;
        int j;
        var maxlength = 0;
        int curlength;
        object columnvalue;
        var dt = ds.tables[0];
        if (datatypes.length < dt.columns.count)
        {
          datatypes = new npoidatatype[dt.columns.count];
          for (var i = 0; i < dt.columns.count; i++)
          {
            var dtcolumntype = dt.columns[i].datatype.name.tolower();
            switch (dtcolumntype)
            {
              case "string":
                datatypes[i] = npoidatatype.string;
                break;
              case "datetime":
                datatypes[i] = npoidatatype.datetime;
                break;
              case "boolean":
                datatypes[i] = npoidatatype.bool;
                break;
              case "double":
                datatypes[i] = npoidatatype.numeric;
                break;
              default:
                datatypes[i] = npoidatatype.string;
                break;
            }
          }
        }
        //创建表头
        var row = sheet.createrow(0);
        //样式
        var style1 = wb.createcellstyle();
        //字体
        var font1 = wb.createfont();
        //字体颜色
        font1.color = hssfcolor.white.index;
        //字体加粗样式
        font1.boldweight = (short)fontboldweight.bold;
        //style1.fillbackgroundcolor = hssfcolor.white.index;
        //getxlcolour(wb, levelonecolor);
        // 设置图案色
        style1.fillforegroundcolor = hssfcolor.green.index;
        //getxlcolour(wb, levelonecolor);// 设置背景色
        style1.fillpattern = fillpattern.solidforeground;
        //样式里的字体设置具体的字体样式
        style1.setfont(font1);
        //文字水平对齐方式
        style1.alignment = horizontalalignment.center;
        //文字垂直对齐方式
        style1.verticalalignment = verticalalignment.center;
        row.heightinpoints = 25;
        for (j = 0; j < dt.columns.count; j++)
        {
          columnvalue = dt.columns[j].columnname;
          curlength = encoding.default.getbytecount(columnvalue.tostring());
          maxlength = (maxlength < curlength ? curlength : maxlength);
          var colounwidth = 256 * maxlength;
          sheet.setcolumnwidth(j, colounwidth);
          try
          {
            //创建第0行的第j列
            cell = row.createcell(j);
            //单元格式设置样式
            cell.cellstyle = style1;

            try
            {
              cell.setcellvalue(columnvalue.tostring());
            }
            catch (exception ex)
            {
              throw new exception(ex.message);
            }

          }
          catch (exception ex)
          {
            throw new exception(ex.message);
          }
        }
        // 创建每一行
        for (var i = startrow; i < ds.tables[0].rows.count; i++)
        {
          var dr = ds.tables[0].rows[i];
          //创建第i行
          row = sheet.createrow(i + 1);
          for (j = 0; j < dt.columns.count; j++)
          {
            columnvalue = dr[j];
            curlength = encoding.default.getbytecount(columnvalue.tostring());
            maxlength = (maxlength < curlength ? curlength : maxlength);
            var colounwidth = 256 * maxlength;
            sheet.setcolumnwidth(j, colounwidth);
            try
            {
              //创建第i行的第j列
              cell = row.createcell(j);
              // 插入第j列的数据
              try
              {
                var dtype = datatypes[j];
                switch (dtype)
                {
                  case npoidatatype.string:
                    {
                      cell.setcellvalue(columnvalue.tostring());
                    }
                    break;
                  case npoidatatype.datetime:
                    {
                      cell.setcellvalue(columnvalue.tostring());
                    }
                    break;
                  case npoidatatype.numeric:
                    {
                      cell.setcellvalue(convert.todouble(columnvalue));
                    }
                    break;
                  case npoidatatype.bool:
                    {
                      cell.setcellvalue(convert.toboolean(columnvalue));
                    }
                    break;
                  case npoidatatype.richtext:
                    {
                      cell.setcellvalue(columnvalue.tostring());
                    }
                    break;
                }
              }
              catch (exception ex)
              {
                cell.setcellvalue(columnvalue.tostring());
                throw new exception(ex.message);
              }
            }
            catch (exception ex)
            {
              throw new exception(ex.message);
            }
          }
        }
        //生成文件在服务器上
        using (var fs = new filestream(savefilename, filemode.openorcreate, fileaccess.write))
        {
          wb.write(fs);
        }
        return true;
      }
      catch (exception er)
      {
        throw new exception(er.message);
      }

    }

   7.读excel-根据npoidatatype创建的datatable列的数据类型：

 /// <summary>
    /// 读excel-根据npoidatatype创建的datatable列的数据类型
    /// </summary>
    /// <param name="datatype"></param>
    /// <returns></returns>
    private static type getdatatabletype(npoidatatype datatype)
    {
      var tp = typeof(string);
      switch (datatype)
      {
        case npoidatatype.bool:
          tp = typeof(bool);
          break;
        case npoidatatype.datetime:
          tp = typeof(datetime);
          break;
        case npoidatatype.numeric:
          tp = typeof(double);
          break;
        case npoidatatype.error:
          tp = typeof(string);
          break;
        case npoidatatype.blank:
          tp = typeof(string);
          break;
      }
      return tp;
    }


    /// <summary>
    /// 读excel-得到不同数据类型单元格的数据
    /// </summary>
    /// <param name="datatype">数据类型</param>
    /// <param name="row">数据中的一行</param>
    /// <param name="column">哪列</param>
    /// <returns></returns>
    private static object getcelldata(npoidatatype datatype, irow row, int column)
    {
      switch (datatype)
      {
        case npoidatatype.string:
          try
          {
            return row.getcell(column).datecellvalue;
          }
          catch
          {
            try
            {
              return row.getcell(column).stringcellvalue;
            }
            catch
            {
              return row.getcell(column).numericcellvalue;
            }
          }
        case npoidatatype.bool:
          try { return row.getcell(column).booleancellvalue; }
          catch { return row.getcell(column).stringcellvalue; }
        case npoidatatype.datetime:
          try { return row.getcell(column).datecellvalue; }
          catch { return row.getcell(column).stringcellvalue; }
        case npoidatatype.numeric:
          try { return row.getcell(column).numericcellvalue; }
          catch { return row.getcell(column).stringcellvalue; }
        case npoidatatype.richtext:
          try { return row.getcell(column).richstringcellvalue; }
          catch { return row.getcell(column).stringcellvalue; }
        case npoidatatype.error:
          try { return row.getcell(column).errorcellvalue; }
          catch { return row.getcell(column).stringcellvalue; }
        case npoidatatype.blank:
          try { return row.getcell(column).stringcellvalue; }
          catch { return ""; }
        default: return "";
      }
    }

    /// <summary>
    /// 获取单元格数据类型
    /// </summary>
    /// <param name="hs">单元格对象</param>
    /// <returns></returns>
    private static npoidatatype getcelldatatype(icell hs)
    {
      npoidatatype dtype;
      datetime t1;
      var cellvalue = "";

      switch (hs.celltype)
      {
        case celltype.blank:
          dtype = npoidatatype.string;
          cellvalue = hs.stringcellvalue;
          break;
        case celltype.boolean:
          dtype = npoidatatype.bool;
          break;
        case celltype.numeric:
          dtype = npoidatatype.numeric;
          cellvalue = hs.numericcellvalue.tostring(cultureinfo.invariantculture);
          break;
        case celltype.string:
          dtype = npoidatatype.string;
          cellvalue = hs.stringcellvalue;
          break;
        case celltype.error:
          dtype = npoidatatype.error;
          break;
        default:
          dtype = npoidatatype.datetime;
          break;
      }
      if (cellvalue != "" && datetime.tryparse(cellvalue, out t1)) dtype = npoidatatype.datetime;
      return dtype;
    }

四.总结：

本文是接着上五篇介绍.net组件，目的只是在于总结一些组件的用法，将文章作为一个引子，各位读者可以根据文章的介绍更加深入的去了解相关组件。有些地方写的有误，还望多多包涵和指正，欢迎大家给我建议介绍一些你们在项目中经常使用的组件，可以跟大家做一个分享。