WinForm项目开发中NPOI用法实例解析

本文实例展示了winform项目开发中npoi用法，对于c#初学者有一定的借鉴价值。具体实例如下：

private void exportmergeexcel()
{
  if (file.exists(templatexlspath))
  {
 int i = 4, _recordno = 1;
 using (filestream file = new filestream(templatexlspath, filemode.open, fileaccess.read))
 {
   hssfworkbook _excel = new hssfworkbook(file);
   icellstyle _cellstyle = createcellstly(_excel);
   isheet _sheetbasic = _excel.getsheet(excelreadhelper.sheet_basicinfo.replace("$", ""));
   isheet _sheetstreatlamp = _excel.getsheet(excelreadhelper.sheet_lampmoreless.replace("$", ""));
   isheet _sheetbasicex = _excel.getsheet(excelreadhelper.sheet_basicexinfo.replace("$", ""));
   isheet _sheetstreatlampex = _excel.getsheet(excelreadhelper.sheet_lampmorelessexinfo.replace("$", ""));

   isheet _sheetbasicteamex = _excel.getsheet(excelreadhelper.sheet_basicteamstatistics.replace("$", ""));
   isheet _sheetbasiclamptypeex = _excel.getsheet(excelreadhelper.sheet_basictypestatistics.replace("$", ""));
   isheet _sheetstreetlampmlex = _excel.getsheet(excelreadhelper.sheet_lampmorelessteamstatistics.replace("$", ""));
   isheet _sheetstreetlampteamml = _excel.getsheet(excelreadhelper.sheet_lampmorelesstypestatistics.replace("$", ""));

   file.close();

   fillbasicsheetdb(_sheetbasic, i, _recordno);
   _recordno = 1; i = 4;
   fillstreetlampdb(_sheetstreatlamp, i, _recordno);

   _recordno = 1; i = 4;
   fillbasicexsheetdb(_sheetbasicex, i, _recordno);

   _recordno = 1; i = 4;
   fillstreetlampexdb(_sheetstreatlampex, i, _recordno);

   i = 1; irow _rowsum = null; int _lamptotallampcnt = 0, _collampcnt = 0, _ncollampcnt = 0; double _lamptotallamppw = 0, _collamppw = 0, _ncollamppw = 0;
   fillbasicteamexsheetdb(_excel, _rowsum, _sheetbasicteamex, _cellstyle, i, _lamptotallampcnt, _collampcnt, _ncollampcnt, _lamptotallamppw, _collamppw, _ncollamppw);

   i = 1; _lamptotallampcnt = 0; _collampcnt = 0; _ncollampcnt = 0; _lamptotallamppw = 0; _collamppw = 0; _ncollamppw = 0;
   fillbasiclamptypeexsheetdb(_excel, _rowsum, _sheetbasiclamptypeex, _cellstyle, i, _lamptotallampcnt, _collampcnt, _ncollampcnt, _lamptotallamppw, _collamppw, _ncollamppw);

   _lamptotallampcnt = 0; _lamptotallamppw = 0; i = 1;
   fillsheetstreetlampmlsheetdb(_excel, _rowsum, _sheetstreetlampmlex, _cellstyle, i, _lamptotallampcnt, _lamptotallamppw);

   _lamptotallampcnt = 0; _lamptotallamppw = 0; i = 1;
   fillstreetlampteammlsheetdb(_excel, _rowsum, _sheetstreetlampteamml, _cellstyle, i, _lamptotallampcnt, _lamptotallamppw);

   outputmergeexcel(_excel);
 }
  }
}
private void fillbasicteamexsheetdb(hssfworkbook _excel, irow _rowsum, isheet _sheetbasicteamex, icellstyle _cellstyle, int i, int _lamptotallampcnt, int _collampcnt, int _ncollampcnt, double _lamptotallamppw, double _collamppw, double _ncollamppw)
{
  foreach (excelstatistics excelbasicex in basicteamexlist)
  {
 irow _row = _sheetbasicteamex.createrow(i);
 excelwritehelper.createstatisticsexcelrow(_row, excelbasicex, "basicteam");
 #region 总灯数
 int _ltotallampcnt = 0;
 int.tryparse(excelbasicex.lampcount, out _ltotallampcnt);
 _lamptotallampcnt += _ltotallampcnt;
 #endregion
 #region 总计算功率(kw)
 double _ltotallamppw = 0;
 double.tryparse(excelbasicex.lamppower, out _ltotallamppw);
 _lamptotallamppw += _ltotallamppw;
 #endregion
 #region 汇总灯数
 int _clampcount = 0;
 int.tryparse(excelbasicex.collectcount, out _clampcount);
 _collampcnt += _clampcount;
 #endregion
 #region 汇总功率(kw)
 double _clamppw = 0;
 double.tryparse(excelbasicex.collectpower, out _clamppw);
 _collamppw += _clamppw;
 #endregion
 #region 非汇总灯数
 int _nclampcount = 0;
 int.tryparse(excelbasicex.notcollectcount, out _nclampcount);
 _ncollampcnt += _nclampcount;
 #endregion
 #region 非汇总功率(kw)
 double _nclamppw = 0;
 double.tryparse(excelbasicex.notcollectpower, out _nclamppw);
 _ncollamppw += _nclamppw;
 #endregion
 i++;
  }
  _rowsum = _sheetbasicteamex.createrow(i);
  _rowsum.heightinpoints = 20;

  _rowsum.createcell(0).setcellvalue("合计：");
  _rowsum.createcell(1).setcellvalue(_lamptotallampcnt);
  _rowsum.createcell(2).setcellvalue(_lamptotallamppw);
  _rowsum.createcell(3).setcellvalue(_collampcnt);
  _rowsum.createcell(4).setcellvalue(_collamppw);
  _rowsum.createcell(5).setcellvalue(_ncollampcnt);
  _rowsum.createcell(6).setcellvalue(_ncollamppw);
  setrowstyle(_rowsum, _cellstyle);
}

定义样式：

/// <summary>
/// 样式创建
/// eg:
///private icellstyle createcellstly(hssfworkbook _excel)
///{
///  ifont _font = _excel.createfont();
///  _font.fontheightinpoints = 11;
///  _font.fontname = "宋体";
///  _font.boldweight = (short)fontboldweight.bold;
///  icellstyle _cellstyle = _excel.createcellstyle();
///  //_cellstyle.fillforegroundcolor = npoi.hssf.util.hssfcolor.lightgreen.index;
///  //_cellstyle.fillpattern = npoi.ss.usermodel.fillpattern.solidforeground;
///  _cellstyle.setfont(_font);
///  return _cellstyle;
///}
/// 为行设置样式
/// </summary>
/// <param name="row">irow</param>
/// <param name="cellstyle">icellstyle</param>
public static void setrowstyle(this irow row, icellstyle cellstyle)
{
  if (row != null && cellstyle != null)
  {
 for (int u = row.firstcellnum; u < row.lastcellnum; u++)
 {
   icell _cell = row.getcell(u);
   if (_cell != null)
 _cell.cellstyle = cellstyle;
 }
  }
}