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; } }