WinForm项目开发中NPOI用法 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;
 }
  }
}