void SetColoredHeaders(ExcelWorksheet ws, int row, string name, int startCol,
int stopCol, Color color)
{
ws.Cells[row, startCol].Value = name;
ExcelRange cols = ws.Cells[row, startCol, row, stopCol];
cols.Style.Fill.PatternType = ExcelFillStyle.Solid;
cols.Style.Fill.BackgroundColor.SetColor(color);
cols.Dispose();
}
void SetColoredHeaders(ExcelWorksheet ws, int row, string name, int startCol,
int stopCol, Color color, bool withSum)
{
ws.Cells[row, startCol].Value = name;
if (withSum)
{
ws.Cells[row, stopCol].Formula = String.Format("Sum({0})",
new ExcelAddress(row, TIMELINE_START, row, TIMELINE_START + duration).Address);
}
ExcelRange cols = ws.Cells[row, startCol, row, stopCol];
cols.Style.Fill.PatternType = ExcelFillStyle.Solid;
cols.Style.Fill.BackgroundColor.SetColor(color);
cols.Dispose();
}
int FillInfoData(ExcelWorksheet ws, int row, string desc, string val)
{
ExcelRange cols = ws.Cells[row, 2, row, 5];
cols.Style.Fill.PatternType = ExcelFillStyle.Solid;
cols.Style.Fill.BackgroundColor.SetColor(Color.Yellow);
cols.Dispose();
ws.Cells[row, 2].Value = desc;
ws.Cells[row, 2, row, 3].Merge = true;
ws.Cells[row, 4].Value = val;
ws.Cells[row, 4, row, 5].Merge = true;
row++;
return(row);
}
int FillTeamsData(ExcelWorksheet ws, int row, ProjectStats stats)
{
ExcelRange cols = ws.Cells[row, 6, row, 10];
cols.Style.Fill.PatternType = ExcelFillStyle.Solid;
cols.Style.Fill.BackgroundColor.SetColor(Color.Red);
cols.Dispose();
ws.Cells[row, 6].Value = Catalog.GetString("Total");
ws.Cells[row, 7].Value = stats.LocalTeam;
ws.Cells[row, 9].Value = stats.VisitorTeam;
ws.Cells[row, 7, row, 8].Merge = true;
ws.Cells[row, 9, row, 10].Merge = true;
row++;
return(row);
}
public byte[] GetGeneralReport()
{
ExcelPackage excelPackage = new ExcelPackage();
var workSheet = excelPackage.Workbook.Worksheets.Add("Отчет по тестам");
int testerCount = Database.TestResultRepository.GetAll().Count();
using (ExcelRange testerCountER = workSheet.Cells["A1:C1"])
{
testerCountER["A1"].Value = $"Кол-во тестируемых: {testerCount}";
workSheet.Cells["A1:C1"].Merge = true;
}
ExcelRange tableHeader = workSheet.Cells["A2:C2"];
tableHeader.Style.Border.Left.Style = ExcelBorderStyle.Thin;
tableHeader.Style.Border.Right.Style = ExcelBorderStyle.Thin;
tableHeader.Style.Border.BorderAround(ExcelBorderStyle.Thin);
tableHeader["A2"].Value = "Вопрос";
tableHeader["B2"].Value = "Правильно";
tableHeader["C2"].Value = "Неправильно";
ExcelRange tableBody = workSheet.Cells["A3:C7"];
tableBody.Style.Border.Left.Style = ExcelBorderStyle.Thin;
tableBody.Style.Border.Right.Style = ExcelBorderStyle.Thin;
tableBody.Style.Border.BorderAround(ExcelBorderStyle.Thin);
if (testerCount != 0)
{
int num = 0;
foreach (var question in Database.QuestionRepository.GetAllWithInclude(q => q.TestQuestionAnswers))
{
num++;
tableBody[$"A{num + 2}"].Value = num;
int rightAnswerCount = question.TestQuestionAnswers.Count(qa => qa.AnswerNum.Equals(question.RightAnswerNum));
tableBody[$"B{num + 2}"].Value = rightAnswerCount;
int notRightAnswerCount = question.TestQuestionAnswers.Count(qa => !qa.AnswerNum.Equals(question.RightAnswerNum));
tableBody[$"C{num + 2}"].Value = notRightAnswerCount;
}
}
workSheet.Column(tableBody.End.Column - 1).AutoFit();
workSheet.Column(tableBody.End.Column).AutoFit();
ExcelBarChart chart = workSheet.Drawings.AddChart("barChart", eChartType.ColumnStacked) as ExcelBarChart;
chart.SetSize(500, 300);
chart.SetPosition(tableHeader.Start.Row, -2, tableHeader.End.Column, 10);
chart.Title.Text = workSheet.Name;
string serie = "B3:B7";
string xSerie = "A3:A7";
var serie1 = chart.Series.Add(serie, xSerie);
serie1.Header = "Правильно";
serie1.Fill.Color = Color.FromArgb(79, 129, 189);
serie = "C3:C7";
xSerie = "A3:A7";
var serie2 = chart.Series.Add(serie, xSerie);
serie2.Header = "Неправильно";
serie2.Fill.Color = Color.FromArgb(192, 80, 77);
tableHeader.Dispose();
tableBody.Dispose();
return(excelPackage.GetAsByteArray());
}
