Analysis of NPOI usage examples in WinForm project development

This article shows the usage of NPOI in WinForm project development, which has certain reference value for beginners in C#. Specific examples are as follows:

private void ExportMergeExcel()
{
  if ((templateXlsPath))
  {
 int i = 4, _recordNo = 1;
 using (FileStream file = new FileStream(templateXlsPath, , ))
 {
   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("$", ""));

   ();

   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);
 (_row, excelBasicEx, "BasicTeam");
 #region Total number of lights int _lTotalLampCnt = 0;
 (, out _lTotalLampCnt);
 _lampTotalLampCnt += _lTotalLampCnt;
 #endregion
 #region Total Computed Power (KW) double _lTotalLampPw = 0;
 (, out _lTotalLampPw);
 _lampTotalLampPw += _lTotalLampPw;
 #endregion
 #region summary number of lights int _cLampCount = 0;
 (, out _cLampCount);
 _colLampCnt += _cLampCount;
 #endregion
 #region Summary Power (KW) double _cLampPw = 0;
 (, out _cLampPw);
 _colLampPw += _cLampPw;
 #endregion
 #region Non-summarized lights int _ncLampCount = 0;
 (, out _ncLampCount);
 _ncolLampCnt += _ncLampCount;
 #endregion
 #region Non-summary power (KW) double _ncLampPw = 0;
 (, out _ncLampPw);
 _ncolLampPw += _ncLampPw;
 #endregion
 i++;
  }
  _rowSum = _sheetBasicTeamEx.CreateRow(i);
  _rowSum.HeightInPoints = 20;

  _rowSum.CreateCell(0).SetCellValue("total:");
  _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);
}

Define style:

/// &lt;summary&gt;
/// Style creation/// eg:
///private ICellStyle CreateCellStly(HSSFWorkbook _excel)
///{
///  IFont _font = _excel.CreateFont();
///  _font.FontHeightInPoints = 11;
/// _font.FontName = "Stand Up";///  _font.Boldweight = (short);
///  ICellStyle _cellStyle = _excel.CreateCellStyle();
///  //_cellStyle.FillForegroundColor = ;
///  //_cellStyle.FillPattern = ;
///  _cellStyle.SetFont(_font);
///  return _cellStyle;
///}
/// Style the row/// &lt;/summary&gt;
/// &lt;param name="row"&gt;IRow&lt;/param&gt;
/// &lt;param name="cellStyle"&gt;ICellStyle&lt;/param&gt;
public static void SetRowStyle(this IRow row, ICellStyle cellStyle)
{
  if (row != null &amp;&amp; cellStyle != null)
  {
 for (int u = ; u &lt; ; u++)
 {
   ICell _cell = (u);
   if (_cell != null)
 _cell.CellStyle = cellStyle;
 }
  }
}