/// <summary>
/// 创业园多退少补
/// </summary>
/// <param name="month">统计月</param>
/// <param name="recordList">抄表记录</param>
/// <returns></returns>
private MemoryStream PioneerPark(string month, List <CustomerStatisticsViewModel> recordList)
{
var rowNumber = 19;
var filePath = System.Web.HttpContext.Current.Server.MapPath("~/Areas/DataStatistics/Template/PioneerPark.xls"); //路径
IWorkbook wk = NPOIExcel.ReadTemplateFile(filePath);
var sheet = wk.GetSheetAt(0);
var style = wk.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BottomBorderColor = HSSFColor.Black.Index;
style.LeftBorderColor = HSSFColor.Black.Index;
style.RightBorderColor = HSSFColor.Black.Index;
style.TopBorderColor = HSSFColor.Black.Index;
style.VerticalAlignment = VerticalAlignment.Center;//垂直居中
var style2 = wk.CreateCellStyle();
style2.BorderBottom = BorderStyle.Thin;
style2.BorderLeft = BorderStyle.Thin;
style2.BorderRight = BorderStyle.Thin;
style2.BorderTop = BorderStyle.Thin;
style2.BottomBorderColor = HSSFColor.Black.Index;
style2.LeftBorderColor = HSSFColor.Black.Index;
style2.RightBorderColor = HSSFColor.Black.Index;
style2.TopBorderColor = HSSFColor.Black.Index;
style2.VerticalAlignment = VerticalAlignment.Center;//垂直居中
style2.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//设置标题
var title = sheet.GetRow(0).GetCell(0).StringCellValue;
title = title.Replace("%%year%%", new string(month.Take(4).ToArray())).Replace("%%month%%", new string(month.Skip(4).Take(2).ToArray()));
sheet.GetRow(0).GetCell(0).SetCellValue(title);
//循环数据
var companyData = recordList.GroupBy(x => x.F_CustomerName).OrderBy(x => x.Key).ToList();
foreach (var item in companyData)
{
var startRows = rowNumber;
foreach (var t in item)
{
var sheetRow = rowNumber - 1;
var serialNumber = rowNumber - 18;
sheet.GetRow(sheetRow).GetCell(0).SetCellValue(serialNumber); //序号
sheet.GetRow(sheetRow).GetCell(0).CellStyle = style;
sheet.GetRow(sheetRow).GetCell(1).SetCellValue(t.F_CustomerName); //单位
sheet.GetRow(sheetRow).GetCell(1).CellStyle = style;
sheet.GetRow(sheetRow).GetCell(2).SetCellValue(t.F_CustomerAddress); //用电地址
sheet.GetRow(sheetRow).GetCell(2).CellStyle = style;
sheet.GetRow(sheetRow).GetCell(3).SetCellValue(Math.Round((double)t.F_LastMonthRecord, 2)); //本月用电月初抄表数
sheet.GetRow(sheetRow).GetCell(4).SetCellValue(Math.Round((double)t.F_ThisMonthRecord, 2)); //本月用电月末抄表数
sheet.GetRow(sheetRow).GetCell(5).SetCellFormula("E" + rowNumber + "-D" + rowNumber); //本月用电量
sheet.GetRow(sheetRow).GetCell(6).SetCellFormula("F" + rowNumber + "*I" + rowNumber); //系统计费金额
sheet.GetRow(sheetRow).GetCell(7).SetCellFormula("SUM(F" + startRows + ":F" + (startRows + item.Count() - 1) + ")"); //本月单位合户用电合计
sheet.GetRow(sheetRow).GetCell(8).SetCellValue(double.Parse("1.2")); //系统实收单价
sheet.GetRow(sheetRow).GetCell(9).SetCellValue(Math.Round((double)t.F_ThisMonthBalance, 3)); //月末系统余额
sheet.GetRow(sheetRow).GetCell(10).SetCellFormula("F" + rowNumber + "*D3"); //按实际电价计费金额
sheet.GetRow(sheetRow).GetCell(11).SetCellFormula("SUM(K" + startRows + ":K" + (startRows + item.Count() - 1) + ")"); //实际电价计费合计
sheet.GetRow(sheetRow).GetCell(12).SetCellFormula("K" + rowNumber + "-G" + rowNumber); //本月每表退/补差额
sheet.GetRow(sheetRow).GetCell(13).SetCellFormula("SUM(M" + startRows + ":M" + (startRows + item.Count() - 1) + ")"); //退补差额单位合户合计
for (var i = 3; i <= 13; i++)
{
sheet.GetRow(sheetRow).GetCell(i).CellStyle = style2;
}
rowNumber++;
}
sheet.AddMergedRegion(new CellRangeAddress(startRows - 1, startRows + item.Count() - 2, 1, 1)); //名称合并
sheet.AddMergedRegion(new CellRangeAddress(startRows - 1, startRows + item.Count() - 2, 7, 7)); //用电合并
sheet.AddMergedRegion(new CellRangeAddress(startRows - 1, startRows + item.Count() - 2, 11, 11)); //计费合并
sheet.AddMergedRegion(new CellRangeAddress(startRows - 1, startRows + item.Count() - 2, 13, 13)); //退补合并
}
//汇总统计
sheet.GetRow(2).GetCell(5).SetCellFormula("SUM(F19" + ":F" + (rowNumber - 1) + ")"); //本月系统总用量
sheet.GetRow(2).GetCell(6).SetCellFormula("SUM(G19" + ":G" + (rowNumber - 1) + ")"); //系统计费金额合计
sheet.GetRow(2).GetCell(7).SetCellFormula("F3"); //本月用电量总合计
sheet.GetRow(2).GetCell(9).SetCellFormula("SUM(J19" + ":J" + (rowNumber - 1) + ")"); //月末系统余额合计
sheet.GetRow(2).GetCell(10).SetCellFormula("SUM(K19" + ":K" + (rowNumber - 1) + ")"); //实际电价金额合计
sheet.GetRow(2).GetCell(11).SetCellFormula("K3"); //实际电价计费合户合计
sheet.GetRow(2).GetCell(12).SetCellFormula("SUM(M19" + ":M" + (rowNumber - 1) + ")"); //本月退/补差额合计
sheet.GetRow(2).GetCell(13).SetCellFormula("M3"); //本月退补差额单位合户合计
sheet.ForceFormulaRecalculation = true;
using (var ms = new MemoryStream())
{
wk.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
