public ExcelPackage ExportNetworkMeasures(Network network) { var package = new ExcelPackage(); int measuresCount = 0; package.Workbook.Properties.Author = "EnergieNetwork"; package.Workbook.Properties.Title = network.Name + " Maßnahmen"; foreach (var networkCompany in network.NetworkCompanies) { string companyName; if (networkCompany.Company.Name.Length > 30) { companyName = networkCompany.Company.Name.Remove(27) + "..."; } else { companyName = networkCompany.Company.Name; } ExcelWorksheet worksheet = CreateSheet(package, companyName); var row = 1; worksheet.Cells[row, 3].Value = network.Name + " Maßnahmen"; worksheet.Row(row++). Height = 23; worksheet.Cells[row, 3].Value = networkCompany.Company.Name; worksheet.Row(row++). Height = 20; ExcelRange rangeHead = worksheet.Cells[1, 3, 2, 3]; worksheet.Cells[++row, 2].Value = "Maßnahme"; worksheet.Cells[row, 3].Value = "Kennzeichnung"; worksheet.Cells[row, 4].Value = "Einsparung \nErdgas kWh"; worksheet.Cells[row, 5].Value = "Einsparung \nHeizöl kWh"; worksheet.Cells[row, 6].Value = "Einsparung \nFernwärme kWh"; worksheet.Cells[row, 7].Value = "Einsparung \nStrom kWh"; worksheet.Cells[row, 8].Value = "Einsparung Gesamt \nkWh"; worksheet.Cells[row, 9].Value = "Investition \n€"; worksheet.Cells[row, 10].Value = "Laufzeit der \nMaßnahme a"; worksheet.Cells[row, 11].Value = "Beginn im Jahr"; worksheet.Cells[row, 12].Value = "Status"; worksheet.Cells[row, 13].Value = "Letzte Änderung"; ExcelRange rangeTitles = worksheet.Cells[row, 2, row, 13]; var beginDataRow = row + 1; measuresCount = 0; foreach (var measure in networkCompany.Measures) { measuresCount++; var energieeinsparung = from energySaving in measure.EnergySavings select new{ SourceName = energySaving.EnergySource.Name, SourceSaving = energySaving.Value }; double savingGas = 0; double savingOil = 0; double savingHeat = 0; double savingVoltage = 0; foreach (var energie in energieeinsparung) { if (energie.SourceName == "naturalGas") { savingGas += energie.SourceSaving; } if (energie.SourceName == "fuelOil") { savingOil += energie.SourceSaving; } if (energie.SourceName == "districtHeating") { savingHeat += energie.SourceSaving; } if (energie.SourceName == "electricity") { savingVoltage += energie.SourceSaving; } } worksheet.Column(3). Width = 75; worksheet.Cells[++row, 2].LoadFromCollection(new[]{ new{ Maßnahme = measure.Title, Kennzeichnung = measure.Description, Erdgas = Math.Round(savingGas), Heizöl = Math.Round(savingOil), Fernwärme = Math.Round(savingHeat), Strom = Math.Round(savingVoltage), Einsparung = Math.Round((from energySaving in measure.EnergySavings select energySaving.Value).Sum(), 0), Invest = Math.Round(measure.Investment, 2), Laufzeit = measure.Duration, Beginn = measure.RelatedDuration, Status = measure.State.Title, Änderung = measure.LastChange, } }); worksheet.Cells[row, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; worksheet.Cells[row, 4].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; worksheet.Cells[row, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[row, 5].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; worksheet.Cells[row, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[row, 6].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; worksheet.Cells[row, 6].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[row, 7].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; worksheet.Cells[row, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[row, 8].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; worksheet.Cells[row, 8].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[row, 9].Style.Numberformat.Format = "#,##0.00 [$€-1];[RED]-#,##0.00 [$€-1]"; worksheet.Cells[row, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[row, 11].Style.Numberformat.Format = "dd-mm-yyyy"; worksheet.Cells[row, 13].Style.Numberformat.Format = "dd-mm-yyyy"; } ExcelRange rangeData = worksheet.Cells[beginDataRow, 2, row, 13]; ExcelRange rngData = worksheet.Cells[beginDataRow - 1, 2, row, 13]; worksheet.Cells[++row, 3].Value = "Summe"; worksheet.Cells[row, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[row, 4].Formula = "=SUM(D5:D" + (measuresCount + 4) + ")"; worksheet.Cells[row, 4].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; worksheet.Cells[row, 5].Formula = "=SUM(E5:E" + (measuresCount + 4) + ")"; worksheet.Cells[row, 5].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; worksheet.Cells[row, 6].Formula = "=SUM(F5:F" + (measuresCount + 4) + ")"; worksheet.Cells[row, 6].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; worksheet.Cells[row, 7].Formula = "=SUM(G5:G" + (measuresCount + 4) + ")"; worksheet.Cells[row, 7].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; worksheet.Cells[row, 8].Formula = "=SUM(H5:H" + (measuresCount + 4) + ")"; worksheet.Cells[row, 8].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; worksheet.Cells[row, 9].Formula = "=SUM(I5:I" + (measuresCount + 4) + ")"; worksheet.Cells[row, 9].Style.Numberformat.Format = "#,##0.00 [$€-1];[RED]-#,##0.00 [$€-1]"; ExcelRange rangeSummary = worksheet.Cells[row, 2, row, 13]; rangeHead.Style.Font.Bold = true; rangeHead.Style.Font.Size = 16; rangeTitles.Style.Font.Bold = true; rangeTitles.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; rangeTitles.Style.VerticalAlignment = ExcelVerticalAlignment.Top; rangeTitles.Style.Font.Color.SetColor(Color.White); rangeTitles.Style.Fill.PatternType = ExcelFillStyle.Solid; rangeTitles.Style.Fill.BackgroundColor.SetColor(Color.DarkOliveGreen); rangeSummary.Style.Font.Bold = true; rangeTitles.Style.Border.Top.Style = ExcelBorderStyle.Thick; rangeTitles.Style.Border.Bottom.Style = ExcelBorderStyle.Thick; rangeTitles.Style.Border.Left.Style = ExcelBorderStyle.Thick; rangeTitles.Style.Border.Right.Style = ExcelBorderStyle.Thick; rangeData.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; rangeData.Style.Border.Left.Style = ExcelBorderStyle.Thick; rangeData.Style.Border.Right.Style = ExcelBorderStyle.Thick; rangeSummary.Style.Border.Top.Style = ExcelBorderStyle.Thick; rangeSummary.Style.Border.Bottom.Style = ExcelBorderStyle.Thick; rangeSummary.Style.Border.Left.Style = ExcelBorderStyle.Thick; rangeSummary.Style.Border.Right.Style = ExcelBorderStyle.Thick; worksheet.View.FreezePanes(5, 3); rngData.AutoFilter = true; worksheet.Cells.AutoFitColumns(); worksheet.Column(3). Width = 75; worksheet.Column(3). Style.WrapText = true; worksheet.Row(4). Height = 30; for (int i = 0; i <= 10; i++) { worksheet.Cells[4, 3 + i].Style.WrapText = true; } //ExcelChart chart = worksheet.Drawings.AddChart("Status", eChartType.PieExploded3D); //chart.Title.Text = "Status Übersicht"; //chart.Legend.Position = eLegendPosition.Right; //chart.Legend.Add(); //chart.SetPosition(++row, 5, 3, 0); //chart.SetSize(320, 240); //chart.Series.Add("L" + (beginDataRow) + ":L" + (row - 2),"L"+ (beginDataRow - 1) +":L"+ (beginDataRow - 1) ); ////chart.DataLabel.ShowCategory = true; ////chart.DataLabel.ShowPercent = true; ////chart.DataLabel.ShowLeaderLines = true; //chart.Legend.Remove(); //chart.Style = eChartStyle.Style2; //ExcelChart chart1 = worksheet.Drawings.AddChart("Einsparungen", eChartType.Line); //chart1.Title.Text = "Energieeinsparungen"; //chart1.SetPosition(row, 5, 7, 0); //chart1.SetSize(320, 240); //chart1.Series.Add("D" + (beginDataRow - 1) + ":D" + (row - 2) + ";" + // "E" + (beginDataRow - 1) + ":E" + (row - 2) + ";" + // "F" + (beginDataRow - 1) + ":F" + (row - 2) + ";" + // "G" + (beginDataRow - 1) + ":G" + (row - 2), // "B" + (beginDataRow - 1) + ":B" + (row - 2) // ); //chart1.Style = eChartStyle.Style2; } return package; }
private ICollection<Survey> CreateSurveys(Network network) { ICollection<Survey> surveys = new Collection<Survey>(); Survey survey; int year = DateTime.Now.Year; for (int i = year - 1; i <= network.EndDate.Year; i++) { survey = new Survey{ SurveyId = Guid.NewGuid(), Title = i.ToString(), Date = new DateTime(i, 1, 1) }; surveys.Add(survey); } return surveys; }
public ExcelPackage ExportNetworkData(Network network) { var package = new ExcelPackage(); package.Workbook.Properties.Author = "EnergieNetwork"; package.Workbook.Properties.Title = network.Name; string networkName; if (network.Name.Length > 30) { networkName = network.Name.Remove(27) + "..."; } else { networkName = network.Name; } if (network.NetworkCompanies.Count == 0) { ExcelWorksheet worksheet = CreateSheet(package, networkName); worksheet.Cells[1, 2].Value = "Keine Daten vorhanden"; } foreach (var company in network.NetworkCompanies) { string companyName; if (company.Company.Name.Length > 30) { companyName = company.Company.Name.Remove(27) + "..."; } else { companyName = company.Company.Name; } ExcelWorksheet worksheet = CreateSheet(package, companyName); var row = 1; worksheet.Cells[row, 2].Value = network.Name; worksheet.Cells[row, 2].Style.Font.Bold = true; worksheet.Cells[row, 2].Style.Font.Size = 17; worksheet.Cells[row, 2].Style.VerticalAlignment = ExcelVerticalAlignment.Top; worksheet.Row(row++). Height = 28; worksheet.Cells[row, 2].Value = company.Company.Name; worksheet.Cells[row, 2].Style.Font.Bold = true; worksheet.Cells[row++, 2].Style.Font.Size = 14; row = appendnetworkCompanyToSheet(worksheet, company, row); } return package; }
private NetworkCompany CreateCompany(RegisterBindingModel model, Network network) { ICollection<Survey> surveys = CreateSurveys(network); var address = new Address{ AddressId = Guid.NewGuid(), Street = model.Street, PostalCode = model.PostalCode, City = model.City, Website = model.Website, Lat = model.Lat, Lon = model.Lon }; var company = new Company{ CompanyId = Guid.NewGuid(), Name = model.CompanyName, Address = address }; return new NetworkCompany{ NetworkCompanyId = Guid.NewGuid(), Company = company, Surveys = surveys, Network = network }; }