PRivatevoidExportMergeExcel()
{if(File.Exists(templateXlsPath))
{inti = 4, _recordNo = 1;using(FileStream file =newFileStream(templateXlsPath, FileMode.Open, Fileaccess.Read)){HSSFWorkbook _excel =newHSSFWorkbook(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);
}
}
}
privatevoidFillBasicTeamExSheetDb(HSSFWorkbook _excel, IRow _rowSum, ISheet _sheetBasicTeamEx, ICellStyle _cellStyle,inti,int_lampTotalLampCnt,int_colLampCnt,int_ncolLampCnt,double_lampTotalLampPw,double_colLampPw,double_ncolLampPw)
{foreach(ExcelStatistics excelBasicExinbasicTeamExList)
{
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;#endregioni++;
}
_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);}
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp{ background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }2.定义样式/// <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>publicstaticvoidSetRowStyle(thisIRow row, ICellStyle cellStyle)
{if(row !=null&& cellStyle !=null)
{for(intu = row.FirstCellNum; u < row.LastCellNum; u++)
{
ICell _cell = row.GetCell(u);if(_cell !=null)
_cell.CellStyle = cellStyle;
}
}
}
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }