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
};
}