public void TestRepository() { IHomeDbContext context = new HomeDbContext(); BuildInfo build = context.GetBuildById("000001G001"); Assert.AreEqual(build.BuildId, "000001G001"); }
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); }