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

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; }  }}