private void MakeSingleChart([NotNull] ExcelWorksheet ws, int chartRow, int columnIdx, [NotNull][ItemNotNull] List <ArchiveEntry> providerentries, int startrow, int endrow, [NotNull] string chartname, int width = 800) { ExcelChart chart = ws.Drawings.AddChart(chartname + "chart", eChartType.ColumnStacked); chart.Title.Text = chartname; chart.SetPosition(chartRow, 0, columnIdx + 2, 0); chart.SetSize(width, 500); var barChart = (ExcelBarChart)chart.PlotArea.ChartTypes[0]; barChart.GapWidth = 0; for (int i = 0; i < providerentries.Count; i++) { var name = (string)ws.Cells[1, i + 2].Value; string col = XlsxDumper.GetExcelColumnName(i + 2); Trace("column " + col); var ser1 = chart.Series.Add(ws.Cells[col + startrow + ":" + col + endrow], ws.Cells["A" + startrow + ":A" + endrow]); ser1.Header = name; } chart.Legend.Position = eLegendPosition.Bottom; AddBkwChart(providerentries, ws, chart, startrow, endrow); }
private void AddBkwChart([NotNull][ItemNotNull] List <ArchiveEntry> providerentries, [NotNull] ExcelWorksheet ws, [NotNull] ExcelChart chart, int startrow, int endrow) { int colIdx = providerentries.Count + 2; var name = (string)ws.Cells[1, colIdx].Value; string col = XlsxDumper.GetExcelColumnName(colIdx); Trace("column " + col); var ls = chart.PlotArea.ChartTypes.Add(eChartType.Line); var ser1 = ls.Series.Add(ws.Cells[col + startrow + ":" + col + endrow], ws.Cells["A" + startrow + ":A" + endrow]); ser1.Header = name; }
private void WriteBkwColumn([NotNull] ExcelWorksheet ws, int columnIdx, int sumCol) { var dbRaw = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice); var bkw = dbRaw.Fetch <BkwProfile>(); ws.Cells[1, columnIdx].Value = "BKW"; int rowIdx = 2; var vals = bkw[0].Profile.Values; for (int i = 0; i < vals.Count; i++) { ws.Cells[rowIdx, columnIdx].Value = vals[i]; rowIdx++; } ws.Cells[columnIdx, sumCol].Value = "BKW"; string colletter = XlsxDumper.GetExcelColumnName(columnIdx); ws.Cells[columnIdx, sumCol + 1].Formula = "=Sum(" + colletter + ":" + colletter + ")/1000000/4"; }
private void ProcessOneSumType([NotNull] ScenarioSliceParameters slice, [NotNull] string fn, SumType sumType) { var dbArchive = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.ProfileGeneration, slice, DatabaseCode.SummedLoadForAnalysis); var saHouses = SaveableEntry <ArchiveEntry> .GetSaveableEntry(dbArchive, SaveableEntryTableType.SummedLoadsForAnalysis, Services.Logger); int columnIdx = 2; using (ExcelPackage p = new ExcelPackage()) { var entries = saHouses.LoadAllOrMatching(); var providerentries = entries.Where(x => x.Key.SumType == sumType).ToList(); var ws = p.Workbook.Worksheets.Add("sheet1"); int sumCol = providerentries.Count + 4; foreach (var entry in providerentries) { string colLabel; if (sumType == SumType.ByProvider) { colLabel = entry.Key.ProviderType; } else if (sumType == SumType.ByProfileSource) { colLabel = entry.Key.ProfileSource; } else { throw new FlaException("Unknown sum type"); } ws.Cells[1, columnIdx].Value = colLabel; int rowIdx = 2; var vals = entry.Profile.ConvertFromEnergyToPower(); double multiplier = 1; if (entry.GenerationOrLoad == GenerationOrLoad.Generation) { multiplier = -1; } for (int i = 0; i < vals.Values.Count; i++) { ws.Cells[rowIdx, columnIdx].Value = vals.Values[i] * multiplier; rowIdx++; } ws.Cells[columnIdx, sumCol].Value = colLabel; string colletter = XlsxDumper.GetExcelColumnName(columnIdx); ws.Cells[columnIdx, sumCol + 1].Formula = "=Sum(" + colletter + ":" + colletter + ")/1000000/4"; columnIdx++; } WriteBkwColumn(ws, columnIdx, sumCol); //winter chart int startrow = 2; int endrow = startrow + 24 * 4 * 14; int chartRow = columnIdx + 2; MakeSingleChart(ws, chartRow, columnIdx, providerentries, startrow, endrow, "Winter"); //sommer chart startrow = 24 * 4 * (7 + 130); endrow = startrow + 24 * 4 * 7; chartRow += 30; MakeSingleChart(ws, chartRow, columnIdx, providerentries, startrow, endrow, "Sommer"); //sommer chart startrow = 10000; endrow = 11000; chartRow += 30; MakeSingleChart(ws, chartRow, columnIdx, providerentries, startrow, endrow, "Frühjahr"); p.SaveAs(new FileInfo(fn)); SaveToArchiveDirectory(fn, RelativeDirectory.Report, slice); } Info("saved " + fn); }