protected void SetNationalAverages(List <MedusaTest> testResults, MedusaKey key)
{
var gradeGroups = testResults.GroupBy(i => i.Grade);
foreach (var gradeGroup in gradeGroups)
{
NationalAverage.Add(gradeGroup.Key, gradeGroup.Average(i => i.GetCorrectAnswers(key)).ToString("F1"));
}
}
private void GenerateSchoolReport(string baseReportPath, MedusaKey key, string school, List <MedusaTest> testResults)
{
var filename = $"{baseReportPath}//{school}_MedusaReport.csv";
using (StreamWriter sw = new StreamWriter(filename))
{
// Header
sw.WriteLine("Student,Grade,Gender,Teacher,Raw Score,National Average,Award");
var sorted = testResults
.OrderBy(i => i.Grade)
.ThenBy(i => i.GetCorrectAnswers(key))
.ThenBy(i => i.Name)
.ToList();
foreach (var tr in sorted)
{
var nationalAvg = NationalAverage[tr.Grade];
string award = "N/A";
sw.WriteLine($"{tr.Name},{tr.Grade},{tr.Gender},{tr.Teacher},{tr.GetCorrectAnswers(key)},{nationalAvg},{award}");
}
}
}
private static List <ReportItem> CreateReportItemList(List <MedusaTest> list, MedusaKey key)
{
var reportItemList = new List <ReportItem>();
for (var index = 0; index <= 40; index++)
{
var reportItem = new ReportItem
{
Exactly = list.Count(i => i.GetCorrectAnswers(key) == index),
AtLeast = list.Count(i => i.GetCorrectAnswers(key) >= index)
};
reportItemList.Add(reportItem);
}
return(reportItemList);
}
public static void GenerateTestSummaryReport(string baseReportPath, MedusaKey key, List <MedusaTest> testResults)
{
var perfecto = testResults.Where(i => i.GetCorrectAnswers(key) >= 39);
foreach (var person in perfecto)
{
Console.WriteLine($"{person.Name} - {person.Gender} - {person.School}: {person.GetCorrectAnswers(key)} - Q9: {person.Answers[8]} - Q30: {person.Answers[29]}");
}
var middleSchoolTestResults = testResults
.Where(i => i.Grade == 6 || i.Grade == 7 || i.Grade == 8)
.ToList();
var ninthGradeTestResults = testResults.Where(i => i.Grade == 9).ToList();
var tenthGradeTestResults = testResults.Where(i => i.Grade == 10).ToList();
var eleventhGradeTestResults = testResults.Where(i => i.Grade == 11).ToList();
var twelvthGradeTestResults = testResults.Where(i => i.Grade == 12).ToList();
var middleSchoolReportList = CreateReportItemList(middleSchoolTestResults, key);
var ninthGradeReportList = CreateReportItemList(ninthGradeTestResults, key);
var tenthGradeReportList = CreateReportItemList(tenthGradeTestResults, key);
var eleventhGradeReportList = CreateReportItemList(eleventhGradeTestResults.ToList(), key);
var twelvthGradeReportList = CreateReportItemList(twelvthGradeTestResults.ToList(), key);
var allReportList = CreateReportItemList(testResults, key);
var all = CreateReportItemList(testResults, key);
using (StreamWriter sw = new StreamWriter($"{baseReportPath}\\Distribution.csv"))
{
var totalCount = middleSchoolReportList.Count;
var header = "# EX,# A.L.,PCT";
sw.WriteLine($"# Correct,{header},{header},{header},{header},{header},{header}");
for (var i = 0; i < totalCount; i++)
{
sw.Write($"{i},");
WriteItemFromList(sw, middleSchoolReportList, i, middleSchoolTestResults.Count);
WriteItemFromList(sw, ninthGradeReportList, i, ninthGradeTestResults.Count);
WriteItemFromList(sw, tenthGradeReportList, i, tenthGradeTestResults.Count);
WriteItemFromList(sw, eleventhGradeReportList, i, eleventhGradeTestResults.Count);
WriteItemFromList(sw, twelvthGradeReportList, i, twelvthGradeTestResults.Count);
WriteItemFromList(sw, allReportList, i, testResults.Count);
sw.WriteLine("");
}
// Write Totals
sw.Write($",,Total,{middleSchoolTestResults.Count},,");
sw.Write($"Total,{ninthGradeTestResults.Count},,");
sw.Write($"Total,{tenthGradeTestResults.Count},,");
sw.Write($"Total,{eleventhGradeTestResults.Count},,");
sw.Write($"Total,{twelvthGradeTestResults.Count},,");
sw.Write($"Total,{testResults.Count}");
sw.WriteLine("");
// Write Averages
sw.Write($",,Average,{Math.Round(middleSchoolTestResults.Average(i => i.GetCorrectAnswers(key)), 1)},,");
sw.Write($"Average,{Math.Round(ninthGradeTestResults.Average(i => i.GetCorrectAnswers(key)), 1)},,");
sw.Write($"Average,{Math.Round(tenthGradeTestResults.Average(i => i.GetCorrectAnswers(key)), 1)},,");
sw.Write($"Average,{Math.Round(eleventhGradeTestResults.Average(i => i.GetCorrectAnswers(key)), 1)},,");
sw.Write($"Average,{Math.Round(twelvthGradeTestResults.Average(i => i.GetCorrectAnswers(key)), 1)},,");
sw.Write($"Average,{Math.Round(testResults.Average(i => i.GetCorrectAnswers(key)), 1)}");
sw.WriteLine("");
// Item Analysis
//Question Correct Percent Correct
//Number Responses Correct Answer
sw.WriteLine("Question,Correct,Percent,Correct");
sw.WriteLine("Number,Responses,Correct,Answer");
for (var i = 0; i < 40; i++)
{
var numberCorrect = testResults.Count(test => test.IsAnswerCorrect(key, i));
var percentCorrect = ((decimal)numberCorrect / (decimal)testResults.Count).ToString("P1");
sw.WriteLine($"{i + 1},{numberCorrect},{percentCorrect},{key.Answers[i]}");
}
}
}
public override void GenerateSchoolReports(string baseReportPath, List <MedusaTest> testResults, MedusaKey key)
{
SetNationalAverages(testResults, key);
var schoolGroups = testResults.GroupBy(i => i.School);
foreach (var schoolGroup in schoolGroups)
{
GenerateSchoolReport(baseReportPath, key, schoolGroup.Key, schoolGroup.ToList());
}
}
public abstract void GenerateSchoolReports(string baseReportPath, List <MedusaTest> testResults, MedusaKey key);
private void GenerateSchoolReport(string baseReportPath, MedusaKey key, string school, List <MedusaTest> testResults)
{
using (var package = new ExcelPackage())
{
// Add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(school);
//Add the headers
worksheet.Cells[1, 1].Value = "Student";
worksheet.Cells[1, 2].Value = "Grade";
//worksheet.Cells[1, 3].Value = "Gender";
worksheet.Cells[1, 3].Value = "Teacher";
worksheet.Cells[1, 4].Value = "Raw Score";
worksheet.Cells[1, 4].Style.WrapText = true;
worksheet.Cells[1, 5].Value = "National Average";
worksheet.Cells[1, 5].Style.WrapText = true;
worksheet.Cells[1, 6].Value = "Award";
// Put border after header row
worksheet.Cells["A1:F1"].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells["A1:F1"].Style.Border.Bottom.Color.SetColor(Color.Black);
// This is super slow but could not find a way to not show the excess columns without this
for (int i = 7; i <= 16384; i++)
{
worksheet.Column(i).Hidden = true;
}
//Add some items...
var sorted = testResults
.OrderBy(i => i.Grade)
.ThenBy(i => i.GetCorrectAnswers(key))
.ThenBy(i => i.Name)
.ToList();
var row = 2;
foreach (var tr in sorted)
{
var nationalAvg = NationalAverage[tr.Grade];
worksheet.Cells[$"A{row}"].Value = tr.Name;
worksheet.Cells[$"B{row}"].Value = tr.Grade;
//worksheet.Cells[$"C{row}"].Value = tr.Gender;
worksheet.Cells[$"C{row}"].Value = tr.Teacher;
worksheet.Cells[$"D{row}"].Value = tr.GetCorrectAnswers(key);
worksheet.Cells[$"E{row}"].Value = nationalAvg;
worksheet.Cells[$"F{row}"].Value = tr.GetAward(key);
row++;
}
worksheet.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Column(4).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Column(5).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
using (ExcelRange r = worksheet.Cells["A1:F1"])
{
//r.Style.Font.SetFromFont(new Font("Britannic Bold", 22, FontStyle.Italic));
r.Style.Font.Bold = true;
r.Style.Font.Color.SetColor(Color.Black);
r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
//r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
//r.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
}
worksheet.Cells.AutoFitColumns(8); //Autofit columns for all cells
// Autofit fits with additional empty columns. The following code uses the full width
var totalWidth = worksheet.Column(1).Width +
worksheet.Column(2).Width +
worksheet.Column(3).Width +
worksheet.Column(4).Width +
worksheet.Column(5).Width +
worksheet.Column(6).Width;
var diff = 90 - totalWidth;
if (diff > 2)
{
int additional = (int)diff / 2;
// Split the available width between name and teacher column
worksheet.Column(1).Width = worksheet.Column(1).Width + additional;
worksheet.Column(3).Width = worksheet.Column(3).Width + additional;
}
// lets set the header text
worksheet.HeaderFooter.OddHeader.CenteredText = $"&24&\"Arial,Regular Bold\" {school}";
// add the page number to the footer plus the total number of pages
worksheet.HeaderFooter.OddFooter.RightAlignedText =
string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
// add the sheet name to the footer
worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;
// add the file path to the footer
//worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FileName;
worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:1"];
worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];
// Change the sheet view to show it in page layout mode
worksheet.View.PageLayoutView = true;
// set some document properties
package.Workbook.Properties.Title = $"School Report: {school}";
package.Workbook.Properties.Author = "Melissa Raffensperger";
package.Workbook.Properties.Comments = "Medusa 2018 Test";
// set some extended property values
package.Workbook.Properties.Company = "Raffy6Pack";
// set some custom property values
package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Cody Raffensperger");
package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
var filename = $"{baseReportPath}//{school}_MedusaReport.xlsx";
var xlFile = GetFileInfo(filename);
// save our new workbook in the output directory and we are done!
package.SaveAs(xlFile);
}
}
