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

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

程序员文章站 2024-02-18 11:47:10
本文实例展示了winform项目开发中npoi用法,对于c#初学者有一定的借鉴价值。具体实例如下: private void exportmergeexcel()...

本文实例展示了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;
 }
  }
}