/// <summary>
/// Form a list of students to be expelled, formed by group.
/// </summary>
/// <param name="data_table">List contains <see cref="ToBeExpelledStudentTable"/></param>
/// <param name="filePath">The path to the file</param>
/// <param name="open_after_creation">Open file after creation</param>
public static void CreateReportFile(IEnumerable <ToBeExpelledStudentTable> data_table, string filePath, bool open_after_creation = false)
{
FileWorker.DeleteFileIfExists(filePath);
FileInfo file = new FileInfo(filePath);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage excelPackage = new ExcelPackage(file))
{
ExcelWorkbook excelWorkBook = excelPackage.Workbook;
foreach (var data in data_table)
{
ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add(data.GroupName);
SetHeaderStyle(workSheet, 1);
SetColumnWidth(workSheet, 1, 4, 14, 16, 16, 10);
SetSheetStyle(workSheet, 1, 1, 5, 1);
SetColumnNames(workSheet, 1, 1, "№", "Surname", "Name", "Middlename", "Education forms");
var list = data.deductibleStudents.ToList();
int i = 2, num = 1;
foreach (var item in list)
{
SetRowStyle(workSheet, 1, i, 5, i);
workSheet.Cells[i, 1].Value = num++;
workSheet.Cells[i, 2].Value = item.Surname;
workSheet.Cells[i, 3].Value = item.Name;
workSheet.Cells[i, 4].Value = item.MiddleName;
workSheet.Cells[i, 5].Value = item.EducationForm;
i++;
}
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
}
excelPackage.Save();
if (open_after_creation)
{
FileWorker.Open(filePath);
}
}
}
/// <summary>
/// Saving in xlsx format session results for each group in table form.
/// </summary>
/// <param name="data_table">List contains <see cref="SessionResultTable"/></param>
/// <param name="filePath">The path to the file</param>
/// <param name="open_after_creation">Open file after creation</param>
public static void CreateReportFile(IEnumerable <SessionResultTable> data_table, string filePath, bool open_after_creation = false)
{
FileWorker.DeleteFileIfExists(filePath);
FileInfo file = new FileInfo(filePath);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage excelPackage = new ExcelPackage(file))
{
ExcelWorkbook excelWorkBook = excelPackage.Workbook;
foreach (var data in data_table)
{
ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add(data.GroupName);
SetHeaderStyle(workSheet, 1);
SetColumnWidth(workSheet, 1, 4, 30, 25, 10, 12, 10);
SetSheetStyle(workSheet, 1, 1, 6, 1);
SetColumnNames(workSheet, 1, 1, "№", "Student", "Subject", "Mark", "Date", "Test form");
var list = data.sessionResults.ToList();
int i = 2, num = 1;
foreach (var item in list)
{
SetRowStyle(workSheet, 1, i, 6, i);
workSheet.Cells[i, 1].Value = num++;
workSheet.Cells[i, 2].Value = $"{item.Surname} {item.Name} {item.MiddleName}";
workSheet.Cells[i, 3].Value = item.Subject;
workSheet.Cells[i, 4].Value = item.Mark;
workSheet.Cells[i, 5].Value = item.Date.ToString("dd.MM.yyyy");
workSheet.Cells[i, 6].Value = item.TestForm;
i++;
}
workSheet.Cells["A" + i.ToString() + ":F" + i.ToString()].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
}
excelPackage.Save();
if (open_after_creation)
{
FileWorker.Open(filePath);
}
}
}
/// <summary>
/// For each session, display the xlsx joint table with the average / minimum / maximum score for each group.
/// </summary>
/// <param name="data_table">List contains <see cref="PointsByGroupTable"/></param>
/// <param name="filePath">The path to the file</param>
/// <param name="open_after_creation">Open file after creation</param>
public static void CreateReportFile(IEnumerable <PointsByGroupTable> data_table, string filePath, bool open_after_creation = false)
{
FileWorker.DeleteFileIfExists(filePath);
FileInfo file = new FileInfo(filePath);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage excelPackage = new ExcelPackage(file))
{
ExcelWorkbook excelWorkBook = excelPackage.Workbook;
foreach (var data in data_table)
{
ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add(data.SessionPeriod);
SetHeaderStyle(workSheet, 1);
SetColumnWidth(workSheet, 1, 4, 15, 10, 10, 10);
SetSheetStyle(workSheet, 1, 1, 5, 1);
SetColumnNames(workSheet, 1, 1, "№", "Group Name", "Minimum Score", "Average Score", "Maximum Score");
var list = data.pointsByGroups.ToList();
int i = 2, num = 1;
foreach (var item in list)
{
SetRowStyle(workSheet, 1, i, 5, i);
workSheet.Cells[i, 1].Value = num++;
workSheet.Cells[i, 2].Value = item.GroupName;
workSheet.Cells[i, 3].Value = item.MinimumScore;
workSheet.Cells[i, 4].Value = item.AverageScore;
workSheet.Cells[i, 5].Value = item.MaximumScore;
i++;
}
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
}
excelPackage.Save();
if (open_after_creation)
{
FileWorker.Open(filePath);
}
}
}
/// <summary>
/// Get the dynamics of changes in the average score for each subject over the years within all sessions.
/// </summary>
/// <param name="data_table">List contains <see cref="DynamicsOfChangeInTheAverageScoreTable"/></param>
/// <param name="filePath">The path to the file</param>
/// <param name="open_after_creation">Open file after creation</param>
public static void CreateReportFile(IEnumerable <DynamicsOfChangeInTheAverageScoreTable> data_table, string filePath, bool open_after_creation = false)
{
FileWorker.DeleteFileIfExists(filePath);
FileInfo file = new FileInfo(filePath);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage excelPackage = new ExcelPackage(file))
{
ExcelWorkbook excelWorkBook = excelPackage.Workbook;
foreach (var data in data_table)
{
ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add(data.SubjectName);
SetHeaderStyle(workSheet, 1);
SetColumnWidth(workSheet, 1, 4, 35, 10);
SetSheetStyle(workSheet, 1, 1, 3, 1);
SetColumnNames(workSheet, 1, 1, "№", "Session Period", "Average ball");
var list = data.table_rows.ToList();
int i = 2, num = 1;
foreach (var item in list)
{
SetRowStyle(workSheet, 1, i, 3, i);
workSheet.Cells[i, 1].Value = num++;
workSheet.Cells[i, 2].Value = item.SessionPeriod;
workSheet.Cells[i, 3].Value = item.AverageBall.ToString("f2");
i++;
}
workSheet.Cells["A" + i.ToString() + ":C" + i.ToString()].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
}
excelPackage.Save();
if (open_after_creation)
{
FileWorker.Open(filePath);
}
}
}
/// <summary>
/// Form a list of students to be expelled, formed by group.
/// </summary>
/// <param name="data_table">List contains <see cref="ToBeExpelledStudentTable"/></param>
/// <param name="filePath">The path to the file</param>
public static void CreateReportFile(IEnumerable <ToBeExpelledStudentTable> data_table, string filePath, bool open_after_creation = false)
{
FileWorker.DeleteFileIfExists(filePath);
FileInfo file = new FileInfo(filePath);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage excelPackage = new ExcelPackage(file))
{
ExcelWorkbook excelWorkBook = excelPackage.Workbook;
foreach (var data in data_table)
{
ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add(data.GroupName);
SetHeaderStyle(workSheet, 1);
workSheet.Column(1).Width = 4;
workSheet.Column(2).Width = 14;
workSheet.Column(3).Width = 16;
workSheet.Column(4).Width = 16;
workSheet.Column(5).Width = 10;
workSheet.Cells["A1:E1"].Style.Font.Size = 10;
workSheet.Cells["A1:E1"].Style.Font.Name = "Arial Cyr";
workSheet.Cells["A1:E1"].Style.Font.Bold = true;
workSheet.Cells["A1:E1"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
workSheet.Cells["B1:D1"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells["B1:D1"].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells[1, 1].Value = "№ ";
workSheet.Cells[1, 2].Value = "Surname";
workSheet.Cells[1, 3].Value = "Name";
workSheet.Cells[1, 4].Value = "Middlename";
workSheet.Cells[1, 5].Value = "Education forms";
var list = data.deductibleStudents.ToList();
int i = 2;
for (int j = 0; j < data.deductibleStudents.Count(); i++, j++)
{
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Font.Size = 10;
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Font.Name = "Arial Cyr";
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.WrapText = true;
workSheet.Cells["E" + i.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
workSheet.Cells[i, 1].Value = i - 1;
workSheet.Cells[i, 2].Value = list[j].Surname;
workSheet.Cells[i, 3].Value = list[j].Name;
workSheet.Cells[i, 4].Value = list[j].MiddleName;
workSheet.Cells[i, 5].Value = list[j].EducationForm;
}
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
}
excelPackage.Save();
if (open_after_creation)
{
FileWorker.Open(filePath);
}
}
}
/// <summary>
/// Saving in xlsx format session results for each group in table form.
/// </summary>
/// <param name="data_table">List contains <see cref="SessionResultTable"/></param>
/// <param name="filePath">The path to the file</param>
public static void CreateReportFile(IEnumerable <SessionResultTable> data_table, string filePath, bool open_after_creation = false)
{
FileWorker.DeleteFileIfExists(filePath);
FileInfo file = new FileInfo(filePath);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage excelPackage = new ExcelPackage(file))
{
ExcelWorkbook excelWorkBook = excelPackage.Workbook;
foreach (var data in data_table)
{
ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add(data.GroupName);
SetHeaderStyle(workSheet, 1);
workSheet.Column(1).Width = 4;
workSheet.Column(2).Width = 30;
workSheet.Column(3).Width = 25;
workSheet.Column(4).Width = 10;
workSheet.Column(5).Width = 12;
workSheet.Column(6).Width = 10;
workSheet.Cells["A1:F1"].Style.Font.Size = 10;
workSheet.Cells["A1:F1"].Style.Font.Name = "Arial Cyr";
workSheet.Cells["A1:F1"].Style.Font.Bold = true;
workSheet.Cells["A1:F1"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
workSheet.Cells["B1:E1"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells["B1:E1"].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells[1, 1].Value = "№ ";
workSheet.Cells[1, 2].Value = "Student";
workSheet.Cells[1, 3].Value = "Subject";
workSheet.Cells[1, 4].Value = "Mark";
workSheet.Cells[1, 5].Value = "Date";
workSheet.Cells[1, 6].Value = "Test form";
var list = data.sessionResults.ToList();
int i = 2;
for (int j = 0; j < data.sessionResults.Count(); i++, j++)
{
workSheet.Cells["A" + i.ToString() + ":F" + i.ToString()].Style.Font.Size = 10;
workSheet.Cells["A" + i.ToString() + ":F" + i.ToString()].Style.Font.Name = "Arial Cyr";
workSheet.Cells["A" + i.ToString() + ":F" + i.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells["A" + i.ToString() + ":F" + i.ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells["A" + i.ToString() + ":F" + i.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells["A" + i.ToString() + ":F" + i.ToString()].Style.WrapText = true;
workSheet.Cells["F" + i.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
workSheet.Row(i).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
workSheet.Row(i).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
workSheet.Cells[i, 1].Value = i - 1;
workSheet.Cells[i, 2].Value = $"{list[j].Surname} {list[j].Name} {list[j].MiddleName}";
workSheet.Cells[i, 3].Value = list[j].Subject;
workSheet.Cells[i, 4].Value = list[j].Mark;
workSheet.Cells[i, 5].Value = list[j].Date.ToString("dd.MM.yyyy");
workSheet.Cells[i, 6].Value = list[j].TestForm;
}
workSheet.Cells["A" + i.ToString() + ":F" + i.ToString()].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
}
excelPackage.Save();
if (open_after_creation)
{
FileWorker.Open(filePath);
}
}
}
/// <summary>
/// For each session, display the xlsx joint table with the average / minimum / maximum score for each group.
/// </summary>
/// <param name="data_table">List contains <see cref="PointsByGroupTable"/></param>
/// <param name="filePath">The path to the file</param>
public static void CreateReportFile(IEnumerable <PointsByGroupTable> data_table, string filePath, bool open_after_creation = false)
{
FileWorker.DeleteFileIfExists(filePath);
FileInfo file = new FileInfo(filePath);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (ExcelPackage excelPackage = new ExcelPackage(file))
{
ExcelWorkbook excelWorkBook = excelPackage.Workbook;
foreach (var data in data_table)
{
ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add(data.SessionPeriod);
SetHeaderStyle(workSheet, 1);
workSheet.Column(1).Width = 4;
workSheet.Column(2).Width = 15;
workSheet.Column(3).Width = 10;
workSheet.Column(4).Width = 10;
workSheet.Column(5).Width = 10;
workSheet.Cells["A1:E1"].Style.Font.Size = 10;
workSheet.Cells["A1:E1"].Style.Font.Name = "Arial Cyr";
workSheet.Cells["A1:E1"].Style.Font.Bold = true;
workSheet.Cells["A1:E1"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
workSheet.Cells["B1:D1"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells["B1:D1"].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells[1, 1].Value = "№ ";
workSheet.Cells[1, 2].Value = "Group Name";
workSheet.Cells[1, 3].Value = "Minimum Score";
workSheet.Cells[1, 4].Value = "Average Score";
workSheet.Cells[1, 5].Value = "Maximum Score";
var list = data.pointsByGroups.ToList();
int i = 2;
for (int j = 0; j < data.pointsByGroups.Count(); i++, j++)
{
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Font.Size = 10;
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Font.Name = "Arial Cyr";
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.WrapText = true;
workSheet.Cells["E" + i.ToString()].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
workSheet.Row(i).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
workSheet.Row(i).Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
workSheet.Cells[i, 1].Value = i - 1;
workSheet.Cells[i, 2].Value = list[j].GroupName;
workSheet.Cells[i, 3].Value = list[j].MinimumScore;
workSheet.Cells[i, 4].Value = list[j].AverageScore;
workSheet.Cells[i, 5].Value = list[j].MaximumScore;
}
workSheet.Cells["A" + i.ToString() + ":E" + i.ToString()].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
}
excelPackage.Save();
if (open_after_creation)
{
FileWorker.Open(filePath);
}
}
}