public void SetPreviousAverage(string name, double average)
{
if (PeopleWithScores.Any(person => person.MemberName == name))
{
var memberWithPreviousScore = PeopleWithScores.First(person => person.MemberName == name);
memberWithPreviousScore.SetPreviousAverage(average);
}
}
/// <summary>
/// Export to excel. Returns the filename.
/// </summary>
/// <returns></returns>
public string ExportToExcel()
{
using (ExcelPackage package = new ExcelPackage())
{
package.Workbook.Worksheets.Add(SportName + " Scores");
var sheet = package.Workbook.Worksheets[1];
int minimumRowIndex = 1;
//headers
sheet.Row(minimumRowIndex).Style.Font.Bold = true;
sheet.Cells[minimumRowIndex, ExcelPreviousAverageColumnIndex].Value = "Average";
sheet.Cells[minimumRowIndex, ExcelPreviousAverageColumnIndex].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
sheet.Cells[minimumRowIndex, ExcelPreviousAverageColumnIndex, minimumRowIndex, ExcelCurrentAverageColumnIndex].Merge = true;
sheet.Cells[minimumRowIndex++, ExcelCategoryNameColumnIndex].Value = ExcelWorkSheetTitle;
sheet.Cells[minimumRowIndex, ExcelCategoryNameColumnIndex].Value = "Category";
sheet.Cells[minimumRowIndex, ExcelNameColumnIndex].Value = "Name";
sheet.Cells[minimumRowIndex, ExcelPreviousAverageColumnIndex].Value = "Previous";
sheet.Cells[minimumRowIndex, ExcelCurrentAverageColumnIndex].Value = "Current";
sheet.Cells[minimumRowIndex, ExcelDifferenceColumnIndex].Value = "Difference";
sheet.Cells[minimumRowIndex, ExcelScoresMinimumColumnIndex].Value = "Scores";
//set bold
sheet.Column(ExcelCategoryNameColumnIndex).Style.Font.Bold = true;
sheet.Column(ExcelDifferenceColumnIndex).Style.Font.Bold = true;
sheet.Row(minimumRowIndex++).Style.Font.Bold = true;
//each category
var categories = PeopleWithScores.Select(s => s.Category).Distinct().OrderBy(categoryName => categoryName);
foreach (var category in categories)
{
//Category
sheet.Cells[minimumRowIndex++, ExcelCategoryNameColumnIndex].Value =
PeopleWithScores.First(member => member.Category == category).ReadableCategory;
var membersInCategory = PeopleWithScores
.Where(member => member.Category == category)
.OrderByDescending(member => member.AdjustedAverage);
foreach (var person in membersInCategory)
{
sheet.Cells[minimumRowIndex, ExcelNameColumnIndex].Value = person.MemberName;
sheet.Cells[minimumRowIndex, ExcelCurrentAverageColumnIndex].Value = person.AdjustedAverage;
sheet.Cells[minimumRowIndex, ExcelPreviousAverageColumnIndex].Value = person.PreviousAverage;
sheet.Cells[minimumRowIndex, ExcelDifferenceColumnIndex].Formula =
$"= IF(C{minimumRowIndex}>0,D{minimumRowIndex}-C{minimumRowIndex},\" \")";
for (int i = 0; i < person.Scores.Length; i++)
{
sheet.Cells[minimumRowIndex, ExcelScoresMinimumColumnIndex + i].Value
= person.Scores[i];
if (person.ErroneousScores.Contains(person.Scores[i]))
{
sheet.Cells[minimumRowIndex, ExcelScoresMinimumColumnIndex + i]
.Style.Font.Bold = true;
}
}
minimumRowIndex++;
}
}
sheet.Cells.AutoFitColumns();
sheet.Column(ExcelCurrentAverageColumnIndex).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
sheet.Column(ExcelCurrentAverageColumnIndex).Style.Numberformat.Format =
sheet.Column(ExcelPreviousAverageColumnIndex).Style.Numberformat.Format =
sheet.Column(ExcelDifferenceColumnIndex).Style.Numberformat.Format = "0.00";
sheet.Column(ExcelCategoryNameColumnIndex).Width = 19;
for (int i = ExcelScoresMinimumColumnIndex; i <= sheet.Dimension.End.Column; i++)
{
sheet.Column(i).Width = 4;
}
//rule for difference in averages
ExcelAddress cells = new ExcelAddress(1, ExcelDifferenceColumnIndex, sheet.Dimension.End.Row, ExcelDifferenceColumnIndex);
var cfRule = sheet.ConditionalFormatting.AddThreeColorScale(cells);
cfRule.HighValue.Type =
cfRule.MiddleValue.Type =
cfRule.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
cfRule.HighValue.Color = Color.FromArgb(198, 239, 206);
cfRule.MiddleValue.Color = Color.White;
cfRule.LowValue.Color = Color.FromArgb(255, 199, 206);
cfRule.HighValue.Value = 7.5;
cfRule.MiddleValue.Value = 0;
cfRule.LowValue.Value = -10;
var fileInfo = GetNewFileFileInfo();
package.SaveAs(fileInfo);
return(fileInfo.FullName);
}
}
