public void TestGetNameByCode()
{
HomeDbContext context = new HomeDbContext();
EnergyItemDict energy = context.GetEnergyItemByCode("01000");
Console.WriteLine(energy.EnergyItemName + ":" + energy.EnergyItemCode);
}
public Excel ExportReportToExcel(string basePath, string buildId, string energyCode, string[] regionIds, string date, string type)
{
type = type.ToUpper();
string templatePath = basePath + "/DayReportTemplate.xls";
string reportType = " 日报(" + date + ")";
if (type == "MM")
{
reportType = " 月报(" + date + ")";
templatePath = basePath + "/MonthReportTemplate.xls";
}
else if (type == "YY")
{
reportType = " 年报(" + date + ")";
templatePath = basePath + "/YearReportTemplate.xls";
}
else if (type == "DD")
{
reportType = " 日报(" + date + ")";
templatePath = basePath + "/DayReportTemplate.xls";
}
//根据模板生成Excel
FileStream file = new FileStream(templatePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook book = new HSSFWorkbook(file);
HSSFSheet sheet = (HSSFSheet)book.GetSheet("Sheet1");
//创建单元格格式：数字型单元格默认取小数点后两位
ICellStyle style = book.CreateCellStyle();
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
//获取当前建筑名
IHomeDbContext homeContext = new HomeDbContext();
BuildInfo build = homeContext.GetBuildById(buildId);
//获取能源类型
EnergyItemDict energyItem = reportContext.GetUnitByEnergyCode(energyCode);
//string[] circuitIds = circuits.Split(',');
List <ReportValue> data = context.GetReportValueList(energyCode, regionIds, date, type);
var totalList = data.GroupBy(d => new { d.Time })
.Select(group => new ReportValue
{
Id = "Total",
Name = "总计",
Time = group.Key.Time,
Value = group.Sum(p => p.Value)
}).ToList();
data.AddRange(totalList);
//设置Excel标题
sheet.GetRow(0).GetCell(0).SetCellValue(build.BuildName + " 区域用能 " + reportType);
//设置Excel中报表的能源类别
sheet.GetRow(1).GetCell(1).SetCellValue(energyItem.EnergyItemName);
//设置Excel中报表的能源单位
sheet.GetRow(1).GetCell(5).SetCellValue(energyItem.EnergyItemUnit);
//根据传入circuitIds填充excel
int rowId = 0;
for (int i = 0; i < regionIds.Length + 1; i++)
{
//使用lamda表达式筛选List中Id与传入的Id对应的仪表：一次填充一行Excel
List <ReportValue> current;
if (i == regionIds.Length)
{
current = data.FindAll(p => p.Id == "Total");
}
else
{
current = data.FindAll(p => p.Id == regionIds[i]);
}
if (current.Count > 0)
{
IRow row = sheet.CreateRow(rowId + 3);
row.CreateCell(0).SetCellValue(current[0].Name);
decimal total = 0;
switch (type)
{
case "DD":
foreach (var item in current)
{ //遍历筛选出列表中当前回路中每个小时的数据，填充到Excel中
//当前行的第一列已经填充回路名称，根据时间设置向右偏移一位，将对应数据写入到当前单元格，并设置格式
if (item.Time != null)
{
DateTime time = Convert.ToDateTime(item.Time);
row.CreateCell(time.Hour + 1).SetCellValue((double)item.Value);
row.GetCell(time.Hour + 1).CellStyle = style;
total += Convert.ToDecimal(item.Value);
}
}
row.CreateCell(25).SetCellValue((double)total);
row.GetCell(25).CellStyle = style;
break;
case "MM":
foreach (var item in current)
{
if (item.Time != null)
{
DateTime time = Convert.ToDateTime(item.Time);
row.CreateCell(time.Day).SetCellValue((double)item.Value);
row.GetCell(time.Day).CellStyle = style;
total += Convert.ToDecimal(item.Value);
}
}
row.CreateCell(32).SetCellValue((double)total);
row.GetCell(32).CellStyle = style;
break;
case "YY":
foreach (var item in current)
{
if (item.Time != null)
{
DateTime time = Convert.ToDateTime(item.Time);
row.CreateCell(time.Month).SetCellValue((double)item.Value);
row.GetCell(time.Month).CellStyle = style;
total += Convert.ToDecimal(item.Value);
}
}
row.CreateCell(13).SetCellValue((double)total);
row.GetCell(13).CellStyle = style;
break;
}
rowId++;
}
}
Excel excel = new Excel();
excel.Name = build.BuildName + "区域用能" + reportType + "[" + Guid.NewGuid().ToString("N") + "].xls";
using (MemoryStream stream = new MemoryStream())
{
book.Write(stream);
stream.Seek(0, SeekOrigin.Begin);
excel.Data = stream.ToArray();
}
return(excel);
}
public EnergyItemDict GetUnitByEnergyCode(string energyCode)
{
EnergyItemDict dict = _db.EnergyItemDict.Find(energyCode);
return(dict);
}
