public ExcelPackage ExportCompanyData(NetworkCompany networkCompany) { var package = new ExcelPackage(); package.Workbook.Properties.Author = "EnergieNetwork"; package.Workbook.Properties.Title = networkCompany.Company.Name; 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, 2].Value = networkCompany.Company.Name; worksheet.Cells[row, 2].Style.Font.Bold = true; worksheet.Cells[row, 2].Style.Font.Size = 14; worksheet.Row(row++). Height = 23; row = appendnetworkCompanyToSheet(worksheet, networkCompany, row); return package; }
private int appendnetworkCompanyToSheet(ExcelWorksheet worksheet, NetworkCompany networkCompany, int row) { string companyName; if (networkCompany.Company.Name.Length > 30) { companyName = networkCompany.Company.Name.Remove(27) + "..."; } else { companyName = networkCompany.Company.Name; } var headlinesColor = Color.MediumSeaGreen; var inprogress = 0; var finished = 0; var total = 0; var saved = 0; var denied = 0; worksheet.Cells[++row, 2].Value = "Kontaktinformationen"; worksheet.Cells[row, 2].Style.Font.Bold = true; worksheet.Cells[row, 2].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row++, 2].Style.Fill.BackgroundColor.SetColor(headlinesColor); //worksheet.Row(row++).Height = 20; //worksheet.Cells[row, 6].Style.Numberformat.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; //worksheet.Cells[row, 6].Style.Numberformat.Format ="#0\\.00%"; worksheet.Cells[row++, 2].Value = networkCompany.Company.Address.Street; worksheet.Cells[row++, 2].Value = networkCompany.Company.Address.PostalCode + " " + networkCompany.Company.Address.City; worksheet.Cells[row++, 2].Value = networkCompany.Company.Address.Website; worksheet.Cells[++row, 2].Value = "Ansprechpartner"; worksheet.Cells[row, 2].Style.Font.Bold = true; worksheet.Cells[row, 2].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 2].Style.Fill.BackgroundColor.SetColor(headlinesColor); foreach (var employee in networkCompany.Company.Employees) { row++; worksheet.Cells[row++, 2].Value = employee.FirstName + " " + employee.LastName; worksheet.Cells[row++, 2].Value = employee.Email; worksheet.Cells[row++, 2].Value = employee.PhoneNumber; } if (networkCompany.Measures.Any()) { foreach (var measure in networkCompany.Measures) { total = measure.EnergySavings.Aggregate(total, (current, saving) => (int) (current + saving.Value)); if (measure.State.Title == "abgeschlossen") { finished++; saved = measure.EnergySavings.Aggregate(saved, (current, saving) => (int) (current + saving.Value)); } if (measure.State.Title == "abgelehnt") { denied = measure.EnergySavings.Aggregate(denied, (current, saving) => (int) (current + saving.Value)); } if (measure.State.Title == "in Bearbeitung") { inprogress++; } } total -= denied; worksheet.Cells[++row, 2].Value = "Maßnahmen"; worksheet.Cells[row, 2].Style.Font.Bold = true; worksheet.Cells[row, 2, row, 4].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row, 2, row, 4].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[++row, 2].Value = "Maßnahmen gesamt"; worksheet.Cells[row++, 4].Value = networkCompany.Measures.Count; worksheet.Cells[row, 2].Value = "in Bearbeitung"; worksheet.Cells[row++, 4].Value = inprogress; worksheet.Cells[row, 2].Value = "abgeschlossene Maßnahmen"; worksheet.Cells[row++, 4].Value = finished; worksheet.Cells[row, 2].Value = "eingesparte Energie"; worksheet.Cells[row, 3].Value = "kWh"; worksheet.Cells[row, 4].Value = saved; worksheet.Cells[row++, 4].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row, 2].Value = "Potiential abgelehnter Maßnahmen"; worksheet.Cells[row, 3].Value = "kWh"; worksheet.Cells[row, 4].Value = denied; worksheet.Cells[row++, 4].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row, 2].Value = "gesamt Einsparungspotiential"; worksheet.Cells[row, 3].Value = "kWh"; worksheet.Cells[row, 4].Value = total; worksheet.Cells[row++, 4].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row - 1, 6].Value = "verbleibendes Potential"; worksheet.Cells[row - 1, 8].Value = "kWh"; worksheet.Cells[row - 1, 9].Formula = new ExcelCellAddress(row - 1, 4).Address + "-" + new ExcelCellAddress(row - 3, 4).Address; int chartrowStart; if ((row - 15) <= 0) { chartrowStart = 1; } else { chartrowStart = row - 15; } ExcelChart chartMeasures = worksheet.Drawings.AddChart("Maßnahmen", eChartType.Pie); chartMeasures.Title.Text = "Maßnahmenpotential"; chartMeasures.SetPosition(chartrowStart, 0, 4, 20); chartMeasures.SetSize(350, 280); var data1Address = "'" + companyName + "'!" + new ExcelCellAddress(row - 1, 9).Address; var data2Address = "'" + companyName + "'!" + new ExcelCellAddress(row - 2, 4).Address; var data3Address = "'" + companyName + "'!" + new ExcelCellAddress(row - 3, 4).Address; var xAddress = "'" + companyName + "'!" + new ExcelCellAddress(row - 1, 6).Address + "," + "'" + companyName + "'!" + new ExcelCellAddress(row - 2, 2).Address + "," + "'" + companyName + "'!" + new ExcelCellAddress(row - 3, 2).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress(row - 1, 3).Address); var seriesMeasures = chartMeasures.Series.Add(data1Address + "," + data2Address + "," + data3Address, xAddress); seriesMeasures.HeaderAddress = legendAddress; chartMeasures.Style = eChartStyle.Style2; } { var effizienzstrom = new double[3000]; var effizienzerdgas = new double[3000]; var effizienzheizöl = new double[3000]; var effizienzfernwärme = new double[3000]; var effizienz = new double[3000]; var effiemissstrom = new double[3000]; var effiemisserdgas = new double[3000]; var effiemissheizöl = new double[3000]; var effiemissfernwärme = new double[3000]; var effiemiss = new double[3000]; var minYear = 3000; var maxYear = 0; var isEmpty = networkCompany.Surveys.Count(s => s.Readings.Count > 0) == 0; if (networkCompany.Surveys.Count(s => s.Readings.Count > 0) == 0) { return row; } foreach (var survey in networkCompany.Surveys) { var relevantYear = true; foreach (var reading in survey.Readings) { var value = reading.Value / 1000; effizienz[survey.Date.Year] += value; effiemiss[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000; if (reading.EnergySource.Name == "electricity") { effizienzstrom[survey.Date.Year] += value; effiemissstrom[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000; } if (reading.EnergySource.Name == "naturalGas") { effizienzerdgas[survey.Date.Year] += value; effiemisserdgas[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000; } if (reading.EnergySource.Name == "fuelOil") { effizienzheizöl[survey.Date.Year] += value; effiemissheizöl[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000; } if (reading.EnergySource.Name == "districtHeating") { effizienzfernwärme[survey.Date.Year] += value; effiemissfernwärme[survey.Date.Year] += value * reading.EnergySource.CO2Equivalent / 1000; } if (minYear > survey.Date.Year) { minYear = survey.Date.Year; } if (maxYear < survey.Date.Year) { maxYear = survey.Date.Year; } relevantYear = effizienz[survey.Date.Year] != 0; } if ((!relevantYear) && (minYear == survey.Date.Year)) { minYear++; } if ((!relevantYear) && (maxYear == survey.Date.Year)) { maxYear--; } } row++; var co2Offset = maxYear - minYear + 6; var rowOffset = 0; for (int index = minYear; index <= maxYear; index++) { worksheet.Cells[row + 0, 2].Value = "Energieverbrauch"; worksheet.Cells[row + 0, 2].Style.Font.Bold = true; worksheet.Cells[row + 0, 4 + (index - minYear)].Value = index; worksheet.Cells[row + 0, 4 + (index - minYear)].Style.Font.Bold = true; worksheet.Cells[row + 1, 2].Value = "Strom"; worksheet.Cells[row + 1, 3].Value = "MWh"; worksheet.Cells[row + 1, 4 + (index - minYear)].Value = effizienzstrom[index]; worksheet.Cells[row + 1, 4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 2, 2].Value = "Erdgas"; worksheet.Cells[row + 2, 3].Value = "MWh"; worksheet.Cells[row + 2, 4 + (index - minYear)].Value = effizienzerdgas[index]; worksheet.Cells[row + 2, 4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 3, 2].Value = "Heizöl"; worksheet.Cells[row + 3, 3].Value = "MWh"; worksheet.Cells[row + 3, 4 + (index - minYear)].Value = effizienzheizöl[index]; worksheet.Cells[row + 3, 4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 4, 2].Value = "Fernwärme"; worksheet.Cells[row + 4, 3].Value = "MWh"; worksheet.Cells[row + 4, 4 + (index - minYear)].Value = effizienzfernwärme[index]; worksheet.Cells[row + 4, 4 + (index - minYear)].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 0, (co2Offset + 2)].Value = "CO2-Emissionen"; worksheet.Cells[row + 0, (co2Offset + 2)].Style.Font.Bold = true; worksheet.Cells[row + 0, (co2Offset + 4) + (index - minYear)].Value = index; worksheet.Cells[row + 0, (co2Offset + 4) + (index - minYear)].Style.Font.Bold = true; worksheet.Cells[row + 1, (co2Offset + 2)].Value = "CO2 Strom"; worksheet.Cells[row + 1, (co2Offset + 3)].Value = "t"; worksheet.Cells[row + 1, (co2Offset + 4) + (index - minYear)].Value = effiemissstrom[index]; worksheet.Cells[row + 1, (co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; worksheet.Cells[row + 2, (co2Offset + 2)].Value = "CO2 Erdgas"; worksheet.Cells[row + 2, (co2Offset + 3)].Value = "t"; worksheet.Cells[row + 2, (co2Offset + 4) + (index - minYear)].Value = effiemisserdgas[index]; worksheet.Cells[row + 2, (co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; worksheet.Cells[row + 3, (co2Offset + 2)].Value = "CO2 Heizöl"; worksheet.Cells[row + 3, (co2Offset + 3)].Value = "t"; worksheet.Cells[row + 3, (co2Offset + 4) + (index - minYear)].Value = effiemissheizöl[index]; worksheet.Cells[row + 3, (co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; worksheet.Cells[row + 4, (co2Offset + 2)].Value = "CO2 Fernwärme"; worksheet.Cells[row + 4, (co2Offset + 3)].Value = "t"; worksheet.Cells[row + 4, (co2Offset + 4) + (index - minYear)].Value = effiemissfernwärme[index]; worksheet.Cells[row + 4, (co2Offset + 4) + (index - minYear)].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; } worksheet.Cells[row + 0, 6 + maxYear - minYear].Value = "Summe"; worksheet.Cells[row + 0, 6 + maxYear - minYear].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[row + 0, 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 0, 2, row, 6 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + 0, 2, row, 6 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[row + 1, 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 1, 4, row + 1, 4 + maxYear - minYear).Address); worksheet.Cells[row + 1, 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 1, 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 2, 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 2, 4, row + 2, 4 + maxYear - minYear).Address); worksheet.Cells[row + 2, 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 2, 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 3, 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 3, 4, row + 3, 4 + maxYear - minYear).Address); worksheet.Cells[row + 3, 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 3, 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 4, 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 4, 4, row + 4, 4 + maxYear - minYear).Address); worksheet.Cells[row + 4, 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 4, 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row + 0, co2Offset + 6 + maxYear - minYear].Value = "Summe"; worksheet.Cells[row + 0, co2Offset + 6 + maxYear - minYear].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; worksheet.Cells[row + 0, co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 0, co2Offset + 2, row, co2Offset + 6 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + 0, co2Offset + 2, row, co2Offset + 6 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[row + 1, co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 1, co2Offset + 4, row + 1, co2Offset + 4 + maxYear - minYear).Address); worksheet.Cells[row + 1, co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 1, co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; worksheet.Cells[row + 2, co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 2, co2Offset + 4, row + 2, co2Offset + 4 + maxYear - minYear).Address); worksheet.Cells[row + 2, co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 2, co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; worksheet.Cells[row + 3, co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 3, co2Offset + 4, row + 3, co2Offset + 4 + maxYear - minYear).Address); worksheet.Cells[row + 3, co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 3, co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; worksheet.Cells[row + 4, co2Offset + 6 + maxYear - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row + 4, co2Offset + 4, row + 4, co2Offset + 4 + maxYear - minYear).Address); worksheet.Cells[row + 4, co2Offset + 6 + maxYear - minYear].Style.Font.Bold = true; worksheet.Cells[row + 4, co2Offset + 6 + maxYear - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; row += 5; worksheet.Cells[row, 2].Value = "Summe"; worksheet.Cells[row, 2].Style.Font.Bold = true; worksheet.Cells[row, 3].Value = "MWh"; worksheet.Cells[row, 3].Style.Font.Bold = true; worksheet.Cells[row, co2Offset + 2].Value = "Summe"; worksheet.Cells[row, co2Offset + 2].Style.Font.Bold = true; worksheet.Cells[row, co2Offset + 3].Value = "t"; worksheet.Cells[row, co2Offset + 3].Style.Font.Bold = true; rowOffset = 18; worksheet.Cells[++row + rowOffset + 0, 2].Value = "Entwicklung des Energieverbrauches"; worksheet.Cells[row + rowOffset + 0, 2].Style.Font.Bold = true; worksheet.Cells[row + rowOffset + 0, 2, row + 18, 4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + rowOffset + 0, 2, row + 18, 4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[row + rowOffset + 1, 2].Value = "Strom"; worksheet.Cells[row + rowOffset + 2, 2].Value = "Erdgas"; worksheet.Cells[row + rowOffset + 3, 2].Value = "Heizöl"; worksheet.Cells[row + rowOffset + 4, 2].Value = "Fernwärme"; worksheet.Cells[row + rowOffset + 5, 2].Value = "Gesamt"; worksheet.Cells[row + rowOffset + 5, 2].Style.Font.Bold = true; worksheet.Cells[row + rowOffset + 0, co2Offset + 2].Value = "Entwicklung der CO2-Emissionen"; worksheet.Cells[row + rowOffset + 0, co2Offset + 2].Style.Font.Bold = true; worksheet.Cells[row + rowOffset + 0, co2Offset + 2, row + rowOffset + 0, co2Offset + 4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + rowOffset + 0, co2Offset + 2, row + rowOffset + 0, co2Offset + 4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[row + rowOffset + 1, co2Offset + 2].Value = "CO2 Strom"; worksheet.Cells[row + rowOffset + 2, co2Offset + 2].Value = "CO2 Erdgas"; worksheet.Cells[row + rowOffset + 3, co2Offset + 2].Value = "CO2 Heizöl"; worksheet.Cells[row + rowOffset + 4, co2Offset + 2].Value = "CO2 Fernwärme"; worksheet.Cells[row + rowOffset + 5, co2Offset + 2].Value = "CO2 Gesamt"; worksheet.Cells[row + rowOffset + 5, co2Offset + 2].Style.Font.Bold = true; rowOffset = 42; worksheet.Cells[row + rowOffset + 0, 2].Value = "Anteile am Gesamtenergieverbrauch"; worksheet.Cells[row + rowOffset + 0, 2].Style.Font.Bold = true; worksheet.Cells[row + rowOffset + 0, 2, row + rowOffset + 0, 4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + rowOffset + 0, 2, row + rowOffset + 0, 4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[row + rowOffset + 1, 2].Value = "Stromanteil"; worksheet.Cells[row + rowOffset + 2, 2].Value = "Erdgasanteil"; worksheet.Cells[row + rowOffset + 3, 2].Value = "Heizölanteil"; worksheet.Cells[row + rowOffset + 4, 2].Value = "Fernwärmeanteil"; worksheet.Cells[row + rowOffset + 0, co2Offset + 2].Value = "Anteile an den Gesamtemissionen"; worksheet.Cells[row + rowOffset + 0, co2Offset + 2].Style.Font.Bold = true; worksheet.Cells[row + rowOffset + 0, co2Offset + 2, row + rowOffset + 0, co2Offset + 4 + maxYear - minYear].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[row + rowOffset + 0, co2Offset + 2, row + rowOffset + 0, co2Offset + 4 + maxYear - minYear].Style.Fill.BackgroundColor.SetColor(headlinesColor); worksheet.Cells[row + rowOffset + 1, co2Offset + 2].Value = "CO2 Stromanteil"; worksheet.Cells[row + rowOffset + 2, co2Offset + 2].Value = "CO2 Erdgasanteil"; worksheet.Cells[row + rowOffset + 3, co2Offset + 2].Value = "CO2 Heizölanteil"; worksheet.Cells[row + rowOffset + 4, co2Offset + 2].Value = "CO2 Fernwärmeanteil"; for (int index = minYear; index <= maxYear; index++) { worksheet.Cells[row - 1, 4 + index - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row - 5, 4 + index - minYear, row - 2, 4 + index - minYear).Address); worksheet.Cells[row - 1, 4 + index - minYear].Style.Font.Bold = true; worksheet.Cells[row - 1, 4 + index - minYear].Style.Numberformat.Format = "#,##0;[RED] -#,##0"; worksheet.Cells[row - 1, co2Offset + 4 + index - minYear].Formula = string.Format("Sum({0})", new ExcelAddress(row - 5, co2Offset + 4 + index - minYear, row - 2, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row - 1, co2Offset + 4 + index - minYear].Style.Font.Bold = true; worksheet.Cells[row - 1, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "#,##0.00;[RED] -#,##0.00"; rowOffset = 18; worksheet.Cells[row + rowOffset + 1, 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 5, 4).Address, new ExcelCellAddress(row - 5, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 1, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 2, 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 4, 4).Address, new ExcelCellAddress(row - 4, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 2, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 3, 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 3, 4).Address, new ExcelCellAddress(row - 3, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 3, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 4, 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 2, 4).Address, new ExcelCellAddress(row - 2, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 4, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 5, 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 1, 4).Address, new ExcelCellAddress(row - 1, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 5, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 5, 4 + index - minYear].Style.Font.Bold = true; worksheet.Cells[row + rowOffset + 1, co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 5, co2Offset + 4).Address, new ExcelCellAddress(row - 5, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 1, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 2, co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 4, co2Offset + 4).Address, new ExcelCellAddress(row - 4, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 2, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 3, co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 3, co2Offset + 4).Address, new ExcelCellAddress(row - 3, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 3, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 4, co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 2, co2Offset + 4).Address, new ExcelCellAddress(row - 2, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 4, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 5, co2Offset + 4 + index - minYear].Formula = string.Format("IF({0}<>0,Sum({1}/{0}),0)", new ExcelCellAddress(row - 1, co2Offset + 4).Address, new ExcelCellAddress(row - 1, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 5, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; rowOffset = 42; worksheet.Cells[row + rowOffset + 1, 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 5, 4 + index - minYear).Address, new ExcelCellAddress(row - 1, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 1, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 2, 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 4, 4 + index - minYear).Address, new ExcelCellAddress(row - 1, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 2, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 3, 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 3, 4 + index - minYear).Address, new ExcelCellAddress(row - 1, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 3, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 4, 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 2, 4 + index - minYear).Address, new ExcelCellAddress(row - 1, 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 4, 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 1, co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 5, co2Offset + 4 + index - minYear).Address, new ExcelCellAddress(row - 1, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 1, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 2, co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 4, co2Offset + 4 + index - minYear).Address, new ExcelCellAddress(row - 1, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 2, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 3, co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 3, co2Offset + 4 + index - minYear).Address, new ExcelCellAddress(row - 1, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 3, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; worksheet.Cells[row + rowOffset + 4, co2Offset + 4 + index - minYear].Formula = string.Format("IF({1}<>0,Sum({0}/{1}),0)", new ExcelCellAddress(row - 2, co2Offset + 4 + index - minYear).Address, new ExcelCellAddress(row - 1, co2Offset + 4 + index - minYear).Address); worksheet.Cells[row + rowOffset + 4, co2Offset + 4 + index - minYear].Style.Numberformat.Format = "0.00%"; } ExcelChart chartOverview = worksheet.Drawings.AddChart("Entwicklung der Energieeffizienz", eChartType.Line); chartOverview.Title.Text = "Entwicklung der Energieeffizienz"; int chartrowStart; int chartrowOffset; int chartpixelOffset; int chartsizeDelta; if (co2Offset < 9) { chartrowOffset = co2Offset + 1; chartpixelOffset = 80; chartsizeDelta = 355 + (40 * (maxYear - minYear)); } else { chartrowOffset = co2Offset + 1; chartpixelOffset = 10; chartsizeDelta = 315 + (40 * (maxYear - minYear)); } if ((row - 27) <= 0) { chartrowStart = 1; } else { chartrowStart = row - 27; } chartOverview.SetPosition(chartrowStart, 0, chartrowOffset, chartpixelOffset); chartOverview.SetSize(chartsizeDelta, 380); { var data1Address = new ExcelAddress((row + 23), 4, (row + 23), (4 + maxYear - minYear)).Address; var data2Address = new ExcelAddress((row + 23), 4 + co2Offset, (row + 23), (4 + co2Offset + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 6, 4, row - 6, (4 + maxYear - minYear)).Address; var series1Overview = chartOverview.Series.Add(data1Address, xAddress); series1Overview.HeaderAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 18), 2).Address); var series2Overview = chartOverview.Series.Add(data2Address, xAddress); series2Overview.HeaderAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 18), co2Offset + 2).Address); } chartOverview.Style = eChartStyle.Style2; ExcelChart chartEnergieVerbrauch = worksheet.Drawings.AddChart("Energieverbrauch", eChartType.ColumnClustered); chartEnergieVerbrauch.Title.Text = "Energieverbrauch"; chartEnergieVerbrauch.SetPosition(row, 0, 1, 0); chartEnergieVerbrauch.SetSize(305 + (40 * (maxYear - minYear)), 315); for (int index = 0; index < 4; index++) { var dataAddress = new ExcelAddress((row - 5 + index), 4, (row - 5 + index), (4 + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 6, 4, row - 6, (4 + maxYear - minYear)).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 5 + index), 2).Address); var seriesEnergieVerbrauch = chartEnergieVerbrauch.Series.Add(dataAddress, xAddress); seriesEnergieVerbrauch.HeaderAddress = legendAddress; } chartEnergieVerbrauch.Style = eChartStyle.Style2; chartEnergieVerbrauch.YAxis.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; ExcelChart chartEmission = worksheet.Drawings.AddChart("CO2-Emissionen", eChartType.ColumnClustered); chartEmission.Title.Text = "CO2-Emissionen"; chartEmission.SetPosition(row, 0, 1 + co2Offset, 0); chartEmission.SetSize(305 + (40 * (maxYear - minYear)), 315); for (int index = 0; index < 4; index++) { var dataAddress = new ExcelAddress((row - 5 + index), 4 + co2Offset, (row - 5 + index), (4 + co2Offset + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 6, 4 + co2Offset, row - 6, (4 + co2Offset + maxYear - minYear)).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 5 + index), 2 + co2Offset).Address); var seriesEmission = chartEmission.Series.Add(dataAddress, xAddress); seriesEmission.HeaderAddress = legendAddress; } chartEmission.Style = eChartStyle.Style2; chartEmission.YAxis.Format = "#,##0 [$t];[RED] -#,##0 [$t]"; ExcelChart chartEnergieEntwicklung = worksheet.Drawings.AddChart("Energieffizienzentwicklung", eChartType.Line); chartEnergieEntwicklung.Title.Text = "Energieentwicklung"; chartEnergieEntwicklung.SetPosition(row + 24, 0, 1, 0); chartEnergieEntwicklung.SetSize(305 + (40 * (maxYear - minYear)), 315); for (int index = 0; index < 4; index++) { var dataAddress = new ExcelAddress((row + 24 - 5 + index), 4, (row + 24 - 5 + index), (4 + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 6, 4, row - 6, (4 + maxYear - minYear)).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 24 - 5 + index), 2).Address); var seriesEnergieEntwicklung = chartEnergieEntwicklung.Series.Add(dataAddress, xAddress); seriesEnergieEntwicklung.HeaderAddress = legendAddress; } chartEnergieEntwicklung.Style = eChartStyle.Style2; ExcelChart chartCO2entwicklung = worksheet.Drawings.AddChart("CO2-Emissionenentwicklung", eChartType.Line); chartCO2entwicklung.Title.Text = "CO2-Entwicklung"; chartCO2entwicklung.SetPosition(row + 24, 0, 1 + co2Offset, 0); chartCO2entwicklung.SetSize(305 + (40 * (maxYear - minYear)), 315); for (int index = 0; index < 4; index++) { var dataAddress = new ExcelAddress((row + 24 - 5 + index), 4 + co2Offset, (row + 24 - 5 + index), (4 + co2Offset + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 6, 4 + co2Offset, row - 6, (4 + co2Offset + maxYear - minYear)).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row + 24 - 5 + index), 2 + co2Offset).Address); var seriesCO2entwicklung = chartCO2entwicklung.Series.Add(dataAddress, xAddress); seriesCO2entwicklung.HeaderAddress = legendAddress; } chartCO2entwicklung.Style = eChartStyle.Style2; row += 47; ExcelChart chartAnteilGesamt = worksheet.Drawings.AddChart("Anteile am Gesamtenergieverbrauch", eChartType.ColumnStacked100); chartAnteilGesamt.Title.Text = "Anteile am Gesamtenergieverbrauch"; //chartAnteilGesamt.Legend.Position = eLegendPosition.Right; //chartAnteilGesamt.Legend.Add(); chartAnteilGesamt.SetPosition(row, 0, 1, 0); chartAnteilGesamt.SetSize(305 + (40 * (maxYear - minYear)), 315); for (int index = 0; index < 4; index++) { var dataAddress = new ExcelAddress((row - 4 + index), 4, (row - 4 + index), (4 + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 53, 4, row - 53, (4 + maxYear - minYear)).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 4 + index), 2).Address); var seriesAnteilGesamt = chartAnteilGesamt.Series.Add(dataAddress, xAddress); seriesAnteilGesamt.HeaderAddress = legendAddress; } //chart.DataLabel.ShowCategory = true; //chart.DataLabel.ShowPercent = true; //chart.DataLabel.ShowLeaderLines = true; //chartAnteilGesamt.Legend.Remove(); chartAnteilGesamt.Style = eChartStyle.Style2; //chartAnteilGesamt.YAxis.Format = "#,##0 [$kWh];[RED] -#,##0 [$kWh]"; ExcelChart chartAnteilGesEmis = worksheet.Drawings.AddChart("Anteile an den CO2-Emissionen", eChartType.ColumnStacked100); chartAnteilGesEmis.Title.Text = "Anteile an den CO2-Emissionen"; chartAnteilGesEmis.SetPosition(row, 0, 1 + co2Offset, 0); chartAnteilGesEmis.SetSize(305 + (40 * (maxYear - minYear)), 315); for (int index = 0; index < 4; index++) { var dataAddress = new ExcelAddress((row - 4 + index), 4 + co2Offset, (row - 4 + index), (4 + co2Offset + maxYear - minYear)).Address; var xAddress = new ExcelAddress(row - 53, 4 + co2Offset, row - 53, (4 + co2Offset + maxYear - minYear)).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress((row - 4 + index), 2 + co2Offset).Address); var seriesAnteilGesEmis = chartAnteilGesEmis.Series.Add(dataAddress, xAddress); seriesAnteilGesEmis.HeaderAddress = legendAddress; } chartAnteilGesEmis.Style = eChartStyle.Style2; var pieChartsEnergyperYear = new ExcelChart[maxYear - minYear + 1]; var pieChartsCO2perYear = new ExcelChart[maxYear - minYear + 1]; for (int index = 0; index <= (maxYear - minYear); index++) { { pieChartsEnergyperYear[index] = worksheet.Drawings.AddChart(("Energieverbrauchsanteile in " + (minYear + index)), eChartType.Pie); pieChartsEnergyperYear[index].Title.Text = "Energieverbrauchsanteile in " + (minYear + index); pieChartsEnergyperYear[index].SetPosition(row + 17 + (17 * index), 0, 1, 0); pieChartsEnergyperYear[index].SetSize(305 + (40 * (maxYear - minYear)), 315); var dataAddress = new ExcelAddress(row - 4, 4 + index, row - 1, 4 + index).Address; var xAddress = new ExcelAddress(row - 4, 2, row - 1, 2).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress(row - 5, 2).Address); var seriespieChartEnergy = pieChartsEnergyperYear[index].Series.Add(dataAddress, xAddress); seriespieChartEnergy.HeaderAddress = legendAddress; } { pieChartsCO2perYear[index] = worksheet.Drawings.AddChart(("CO2-Emissionsanteile in " + (minYear + index)), eChartType.Pie); pieChartsCO2perYear[index].Title.Text = "CO2-Emissionsanteile in " + (minYear + index); pieChartsCO2perYear[index].SetPosition(row + 17 + (17 * index), 0, co2Offset + 1, 0); pieChartsCO2perYear[index].SetSize(305 + (40 * (maxYear - minYear)), 315); var dataAddress = new ExcelAddress(row - 4, co2Offset + 4 + index, row - 1, co2Offset + 4 + index).Address; var xAddress = new ExcelAddress(row - 4, co2Offset + 2, row - 1, co2Offset + 2).Address; var legendAddress = new ExcelAddress("'" + companyName + "'!" + new ExcelCellAddress(row - 5, co2Offset + 2).Address); var seriespieChartCO2 = pieChartsCO2perYear[index].Series.Add(dataAddress, xAddress); seriespieChartCO2.HeaderAddress = legendAddress; } } worksheet.Column(1). Width = 8.43; worksheet.Column(2). Width = 33.57; worksheet.Column(3). Width = 7.43; for (int index = 0; index <= (maxYear - minYear); index++) { worksheet.Column(4 + index). Width = 10; } worksheet.Column(5 + maxYear - minYear). Width = 0.83; worksheet.Column(6 + maxYear - minYear). Width = 9.29; worksheet.Column(co2Offset + 1). Width = 8.43; worksheet.Column(co2Offset + 2). Width = 16.43; worksheet.Column(co2Offset + 3). Width = 6.43; for (int index = 0; index <= (maxYear - minYear); index++) { worksheet.Column(co2Offset + 4 + index). Width = 10; } worksheet.Column(co2Offset + 5 + maxYear - minYear). Width = 0.83; worksheet.Column(co2Offset + 6 + maxYear - minYear). Width = 10; row += 17; return (row); } }
public ExcelPackage ExportCompanyMeasures(NetworkCompany networkCompany) { var package = new ExcelPackage(); int measuresCount = 0; package.Workbook.Properties.Author = "EnergieNetwork"; package.Workbook.Properties.Title = networkCompany.Company.Name + " Maßnahmen"; 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 = networkCompany.Company.Name + " Maßnahmen"; worksheet.Row(row++). Height = 23; 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); rangeTitles.Style.WrapText = true; 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; } return package; }