public static void Main(string[] args)
{
//using (var stream = File.OpenRead("Test.xlsx"))
//{
// var result = AssessmentAnywhere.Excel.AssessmentParser.Parse(stream, 1, "A", "B", "C", 1);
//}
var assessment = new Assessment
{
Rows =
{
new AssessmentRow
{
Surname = "Bradley",
Forenames = "Daniel",
Result = 57m,
},
new AssessmentRow
{
Surname = "Bradley",
Forenames = "Sarah",
Result = 71m,
},
},
TotalMarks = 100m,
GradeBoundaries =
{
new GradeBoundary { Grade = "A", MinResult = 70 },
new GradeBoundary { Grade = "B", MinResult = 60 },
new GradeBoundary { Grade = "C", MinResult = 50 },
new GradeBoundary { Grade = "D", MinResult = 40 },
new GradeBoundary { Grade = "E", MinResult = 30 },
new GradeBoundary { Grade = "F", MinResult = 0 },
}
};
var exportStream = AssessmentExporter.Export(assessment);
exportStream.Position = 0;
using (var fileStream = File.Open("ExportTest.xlsx", FileMode.Create))
{
exportStream.CopyTo(fileStream);
fileStream.Flush();
}
}
public static Stream Export(Assessment assessment)
{
using (var package = new ExcelPackage())
{
// Create percent cell style
var percentageStyle = package.Workbook.Styles.CreateNamedStyle("Percent");
percentageStyle.BuildInId = 5;
percentageStyle.Style.Numberformat.Format = "0%";
// Create headings cell style
var headingsStyle = package.Workbook.Styles.CreateNamedStyle("Heading");
headingsStyle.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
headingsStyle.Style.Font.Bold = true;
var resultsWorksheet = package.Workbook.Worksheets.Add("Results");
resultsWorksheet.SetValue(1, 1, "Surname");
resultsWorksheet.Column(1).Width = 15;
resultsWorksheet.SetValue(1, 2, "Forenames");
resultsWorksheet.Column(2).Width = 15;
resultsWorksheet.SetValue(1, 3, "Result");
resultsWorksheet.Column(3).Width = 7;
for (int i = 0; i < assessment.Rows.Count; i++)
{
var row = assessment.Rows[i];
var rowNum = i + 2;
resultsWorksheet.SetValue(rowNum, 1, row.Surname);
resultsWorksheet.SetValue(rowNum, 2, row.Forenames);
if (row.Result.HasValue)
{
resultsWorksheet.SetValue(rowNum, 3, row.Result);
}
}
int lastResultsColumn = 3;
// Has percentages?
if (assessment.TotalMarks.HasValue)
{
var percentageColumn = ++lastResultsColumn;
// Definitions worksheet
var definitionsWorksheet = package.Workbook.Worksheets.Add("Definitions");
definitionsWorksheet.Column(1).Width = 14;
definitionsWorksheet.SetValue(1, 1, "Total Marks");
definitionsWorksheet.SetValue(1, 2, assessment.TotalMarks);
var totalMarksAddress = ExcelCellBase.GetFullAddress(
"Definitions", ExcelCellBase.GetAddress(1, 2, true));
// Create percentage column
resultsWorksheet.SetValue(1, percentageColumn, "Percentage");
resultsWorksheet.Column(percentageColumn).Width = 11;
var percentageCellsAddress = ExcelCellBase.GetAddress(
2, percentageColumn, resultsWorksheet.Dimension.End.Row, percentageColumn);
var percentageCells = resultsWorksheet.Cells[percentageCellsAddress];
percentageCells.StyleName = "Percent";
// Add percentage cells
for (int i = 0; i < assessment.Rows.Count; i++)
{
var rowNum = i + 2;
var formula = string.Format("=C{0}/({1})", rowNum, totalMarksAddress);
resultsWorksheet.Cells[rowNum, percentageColumn].Formula = formula;
}
}
// Has grades?
if (assessment.GradeBoundaries.Any())
{
var boundaries = assessment.GradeBoundaries.OrderBy(b => b.MinResult).ToList();
var gradeColumn = ++lastResultsColumn;
// Add worksheet
var gradeBoundariesWorksheet = package.Workbook.Worksheets.Add("Grade Boundaries");
gradeBoundariesWorksheet.SetValue(1, 1, "Minimum Mark");
gradeBoundariesWorksheet.SetValue(1, 2, "Grade");
gradeBoundariesWorksheet.Column(1).Width = 15;
gradeBoundariesWorksheet.Cells[1, 1, 1, 2].StyleName = "Heading";
for (int i = 0; i < boundaries.Count; i++)
{
var gradeBoundary = boundaries[i];
var rowNum = i + 2;
gradeBoundariesWorksheet.SetValue(rowNum, 1, gradeBoundary.MinResult);
gradeBoundariesWorksheet.SetValue(rowNum, 2, gradeBoundary.Grade);
}
resultsWorksheet.SetValue(1, gradeColumn, "Grade");
resultsWorksheet.Column(gradeColumn).Width = 7;
var gradeBoundariesRange = ExcelCellBase.GetAddress(2, 1, boundaries.Count + 1, 2, true);
var gradeBoundariesAddress = ExcelCellBase.GetFullAddress("Grade Boundaries", gradeBoundariesRange);
for (int i = 0; i < assessment.Rows.Count; i++)
{
var rowNum = i + 2;
var formula = string.Format("=VLOOKUP(C{0},{1},2,TRUE)", rowNum, gradeBoundariesAddress);
resultsWorksheet.Cells[rowNum, gradeColumn].Formula = formula;
}
}
resultsWorksheet.Cells[1, 1, 1, lastResultsColumn].StyleName = "Heading";
package.Save();
package.Stream.Position = 0;
return package.Stream;
}
}
