public Form1(LoginController loginController, ParticipantController participantController, ProbaController probaController, ScorController scorController)
{
this.loginController = loginController;
this.participantController = participantController;
this.probaController = probaController;
this.scorController = scorController;
}
/// <summary>
/// Afiseaza probele in combobox
/// </summary>
public void fillProbeComboBox()
{
ProbaRepository probaRepository = new ProbaRepository();
ProbaService probaService = new ProbaService(probaRepository);
probaController = new ProbaController(probaService);
List <Proba> listaProbe = probaController.findAll();
foreach (Proba elem in listaProbe)
{
comboBoxProbe.DisplayMember = elem.Name;
comboBoxProbe.ValueMember = elem.Id.ToString();
comboBoxProbe.Items.Add(elem);
}
}
// Get Excel File From Report
public FileStreamResult GetExcelFileFromReport(List <IndexModel> listOfIndexes, ReportMonthViewModel report)
{
var controler = new ProbaController(); // Initialize new controller to use File Method for Excel File
var stream = new MemoryStream();
using (var pck = new ExcelPackage(stream))
{
// EXCEL WORKSHEET FOR INDEXES
ExcelWorksheet wsIndex = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} Indexe orare");
wsIndex.Cells["A1:Z1"].Style.Font.Bold = true;
wsIndex.Cells["A1"].Value = "Clock";
wsIndex.Cells["B1"].Value = "EDIS status";
wsIndex.Cells["C1"].Value = "Energy +A";
wsIndex.Cells["D1"].Value = "Energy -A";
wsIndex.Cells["E1"].Value = "Energy +Ri";
wsIndex.Cells["F1"].Value = "Energy +Rc";
wsIndex.Cells["G1"].Value = "Energy -Ri";
wsIndex.Cells["H1"].Value = "Energy -Rc";
int rowStart = 2;
foreach (var elem in listOfIndexes)
{
wsIndex.Cells[string.Format("A{0}", rowStart)].Value = elem.DataOra;
wsIndex.Cells[string.Format("B{0}", rowStart)].Value = elem.EdisStatus;
wsIndex.Cells[string.Format("C{0}", rowStart)].Value = elem.IndexEnergyPlusA;
wsIndex.Cells[string.Format("D{0}", rowStart)].Value = elem.IndexEnergyMinusA;
wsIndex.Cells[string.Format("E{0}", rowStart)].Value = elem.IndexEnergyPlusRi;
wsIndex.Cells[string.Format("F{0}", rowStart)].Value = elem.IndexEnergyPlusRc;
wsIndex.Cells[string.Format("G{0}", rowStart)].Value = elem.IndexEnergyMinusRi;
wsIndex.Cells[string.Format("H{0}", rowStart)].Value = elem.IndexEnergyMinusRc;
// Set background Yellow color for fix Hour when it starts a new day
if (elem.DataOra.Hour == 0)
{
wsIndex.Cells[string.Format("A{0}:Z{0}", rowStart)].Style.Fill.PatternType = ExcelFillStyle.Solid;
wsIndex.Cells[string.Format("A{0}:Z{0}", rowStart)].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.Yellow);
}
rowStart++;
}
wsIndex.Cells["A:AZ"].AutoFitColumns();
// Another SHEET REport
// EXCEL WORKSHEET FOR A+ Energii orare
ExcelWorksheet wsEnergyPlusA = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} A+ energii orare");
// Set Head of table header Style
wsEnergyPlusA.Cells["A1:AG1"].Style.Font.Bold = true;
// Set Values of Table Header
wsEnergyPlusA.Cells["A1"].Value = "Ora/ Zi";
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyPlusA.Cells[$"{GetCharStringsFromNumber(65 + i)}1"].Value = i;
}
// Set Values from report in excel file
for (int j = 0; j < 24; j++)
{
wsEnergyPlusA.Cells[$"A{j + 2}"].Style.Font.Bold = true;
wsEnergyPlusA.Cells[$"A{j + 2}"].Value = j + 1;
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyPlusA.Cells[$"{GetCharStringsFromNumber(65 + i)}{j + 2}"].Value = report.TabeleValori[0].Valori[i, j];
}
}
// Show Values per each day of mounth
wsEnergyPlusA.Cells[$"A{27}"].Value = " Total: ";
for (int i = 1; i <= (report.ZileInLuna); i++)
{
wsEnergyPlusA.Cells[$"{GetCharStringsFromNumber(65 + i)}27"].Value = report.TabeleValori[0].TotalperZi[i];
}
wsEnergyPlusA.Cells[$"A27:AG27"].Style.Font.Bold = true;
wsEnergyPlusA.Cells[$"{GetCharStringsFromNumber(66 + report.ZileInLuna)}27"].Value = $"{report.TabeleValori[0].TotalperLuna} kWh";
wsEnergyPlusA.Cells["A:AZ"].AutoFitColumns();
// Another SHEET REport
// EXCEL WORKSHEET FOR Ri+ Energii orare
ExcelWorksheet wsEnergyPlusRi = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} Ri+ energii orare");
// Set Head of table header Style
wsEnergyPlusRi.Cells["A1:AG1"].Style.Font.Bold = true;
// Set Values of Table Header
wsEnergyPlusRi.Cells["A1"].Value = "Ora/ Zi";
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyPlusRi.Cells[$"{GetCharStringsFromNumber(65 + i)}1"].Value = i;
}
// Set Values from report in excel file
for (int j = 0; j < 24; j++)
{
wsEnergyPlusRi.Cells[$"A{j + 2}"].Style.Font.Bold = true;
wsEnergyPlusRi.Cells[$"A{j + 2}"].Value = j + 1;
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyPlusRi.Cells[$"{GetCharStringsFromNumber(65 + i)}{j + 2}"].Value = report.TabeleValori[1].Valori[i, j];
}
}
// Show Values per each day of mounth
wsEnergyPlusRi.Cells[$"A{27}"].Value = " Total: ";
for (int i = 1; i <= (report.ZileInLuna); i++)
{
wsEnergyPlusRi.Cells[$"{GetCharStringsFromNumber(65 + i)}27"].Value = report.TabeleValori[1].TotalperZi[i];
}
wsEnergyPlusRi.Cells[$"A27:AG27"].Style.Font.Bold = true;
wsEnergyPlusRi.Cells[$"{GetCharStringsFromNumber(66 + report.ZileInLuna)}27"].Value = $"{report.TabeleValori[1].TotalperLuna} kVArh";
wsEnergyPlusRi.Cells["A:AZ"].AutoFitColumns();
// Another SHEET REport
// EXCEL WORKSHEET FOR Rc- Energii orare
ExcelWorksheet wsEnergyMinusRc = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} Rc- energii orare");
// Set Head of table header Style
wsEnergyMinusRc.Cells["A1:AG1"].Style.Font.Bold = true;
// Set Values of Table Header
wsEnergyMinusRc.Cells["A1"].Value = "Ora/ Zi";
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyMinusRc.Cells[$"{GetCharStringsFromNumber(65 + i)}1"].Value = i;
}
// Set Values from report in excel file
for (int j = 0; j < 24; j++)
{
wsEnergyMinusRc.Cells[$"A{j + 2}"].Style.Font.Bold = true;
wsEnergyMinusRc.Cells[$"A{j + 2}"].Value = j + 1;
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyMinusRc.Cells[$"{GetCharStringsFromNumber(65 + i)}{j + 2}"].Value = report.TabeleValori[2].Valori[i, j];
}
}
// Show Values per each day of mounth
wsEnergyMinusRc.Cells[$"A{27}"].Value = " Total: ";
for (int i = 1; i <= (report.ZileInLuna); i++)
{
wsEnergyMinusRc.Cells[$"{GetCharStringsFromNumber(65 + i)}27"].Value = report.TabeleValori[2].TotalperZi[i];
}
wsEnergyMinusRc.Cells[$"A27:AG27"].Style.Font.Bold = true;
wsEnergyMinusRc.Cells[$"{GetCharStringsFromNumber(66 + report.ZileInLuna)}27"].Value = $"{report.TabeleValori[2].TotalperLuna} kVArh";
wsEnergyMinusRc.Cells["A:AZ"].AutoFitColumns();
// Another SHEET REport
// EXCEL WORKSHEET FOR Cos Fi Inductiv Energii orare
ExcelWorksheet wsCosFiInductiv = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} Cos Fi inductiv");
// Set Head of table header Style
wsCosFiInductiv.Cells["A1:AG1"].Style.Font.Bold = true;
// Set Values of Table Header
wsCosFiInductiv.Cells["A1"].Value = "Ora/ Zi";
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsCosFiInductiv.Cells[$"{GetCharStringsFromNumber(65 + i)}1"].Value = i;
}
// Set Values from report in excel file
for (int j = 0; j < 24; j++)
{
wsCosFiInductiv.Cells[$"A{j + 2}"].Style.Font.Bold = true;
wsCosFiInductiv.Cells[$"A{j + 2}"].Value = j + 1;
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsCosFiInductiv.Cells[$"{GetCharStringsFromNumber(65 + i)}{j + 2}"].Value = report.TabeleValori[3].Valori[i, j];
}
}
// Show Values per each day of mounth
wsCosFiInductiv.Cells[$"A{27}"].Value = " Total: ";
for (int i = 1; i <= (report.ZileInLuna); i++)
{
wsCosFiInductiv.Cells[$"{GetCharStringsFromNumber(65 + i)}27"].Value = report.TabeleValori[3].TotalperZi[i];
}
wsCosFiInductiv.Cells[$"A27:AG27"].Style.Font.Bold = true;
wsCosFiInductiv.Cells[$"{GetCharStringsFromNumber(66 + report.ZileInLuna)}27"].Value = $"{report.TabeleValori[3].TotalperLuna}";
wsCosFiInductiv.Cells["A:AZ"].AutoFitColumns();
// Another SHEET REport
// EXCEL WORKSHEET FOR Cos Fi Capacitiv Energii orare
ExcelWorksheet wsCosFiCapacitiv = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} Cos Fi capacitiv");
// Set Head of table header Style
wsCosFiCapacitiv.Cells["A1:AG1"].Style.Font.Bold = true;
// Set Values of Table Header
wsCosFiCapacitiv.Cells["A1"].Value = "Ora/ Zi";
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsCosFiCapacitiv.Cells[$"{GetCharStringsFromNumber(65 + i)}1"].Value = i;
}
// Set Values from report in excel file
for (int j = 0; j < 24; j++)
{
wsCosFiCapacitiv.Cells[$"A{j + 2}"].Style.Font.Bold = true;
wsCosFiCapacitiv.Cells[$"A{j + 2}"].Value = j + 1;
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsCosFiCapacitiv.Cells[$"{GetCharStringsFromNumber(65 + i)}{j + 2}"].Value = report.TabeleValori[4].Valori[i, j];
}
}
// Show Values per each day of mounth
wsCosFiCapacitiv.Cells[$"A{27}"].Value = " Total: ";
for (int i = 1; i <= (report.ZileInLuna); i++)
{
wsCosFiCapacitiv.Cells[$"{GetCharStringsFromNumber(65 + i)}27"].Value = report.TabeleValori[4].TotalperZi[i];
}
wsCosFiCapacitiv.Cells[$"A27:AG27"].Style.Font.Bold = true;
wsCosFiCapacitiv.Cells[$"{GetCharStringsFromNumber(66 + report.ZileInLuna)}27"].Value = $"{report.TabeleValori[4].TotalperLuna}";
wsCosFiCapacitiv.Cells["A:AZ"].AutoFitColumns();
// Another SHEET REport
// EXCEL WORKSHEET FOR Ri+ Energii orare Facturare
ExcelWorksheet wsEnergyEnergiiFacturare = pck.Workbook.Worksheets.Add($"{report.An}.{report.Luna} Ri+ energii orare fact");
// Set Head of table header Style
wsEnergyEnergiiFacturare.Cells["A1:AG1"].Style.Font.Bold = true;
// Set Values of Table Header
wsEnergyEnergiiFacturare.Cells["A1"].Value = "Ora/ Zi";
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyEnergiiFacturare.Cells[$"{GetCharStringsFromNumber(65 + i)}1"].Value = i;
}
// Set Values from report in excel file
for (int j = 0; j < 24; j++)
{
wsEnergyEnergiiFacturare.Cells[$"A{j + 2}"].Style.Font.Bold = true;
wsEnergyEnergiiFacturare.Cells[$"A{j + 2}"].Value = j + 1;
for (int i = 1; i <= report.ZileInLuna; i++)
{
wsEnergyEnergiiFacturare.Cells[$"{GetCharStringsFromNumber(65 + i)}{j + 2}"].Value = report.TabeleValori[5].Valori[i, j];
}
}
// Show Values per each day of mounth
wsEnergyEnergiiFacturare.Cells[$"A{27}"].Value = " Total: ";
for (int i = 1; i <= (report.ZileInLuna); i++)
{
wsEnergyEnergiiFacturare.Cells[$"{GetCharStringsFromNumber(65 + i)}27"].Value = report.TabeleValori[5].TotalperZi[i];
}
wsEnergyEnergiiFacturare.Cells[$"A27:AG27"].Style.Font.Bold = true;
wsEnergyEnergiiFacturare.Cells[$"{GetCharStringsFromNumber(66 + report.ZileInLuna)}27"].Value = $"{report.TabeleValori[5].TotalperLuna} kVArh";
wsEnergyEnergiiFacturare.Cells["A:AZ"].AutoFitColumns();
// Final part Excel File
pck.Save();
}
stream.Position = 0;
string excelName = $"{report.An}.{report.Luna}_RaportEnergy.xlsx";
return(controler.File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName));
}
