public void Write(ISpreadsheetWriter spreadsheetWriter)
{
spreadsheetWriter.CurrentPosition = StartPosition;
var scoresStartPosition = new Point(StartOfStudentsColumn, AchievedScoresRow);
var scoresEndPosition = new Point(StartOfStudentsColumn + NumberOfStudents, AchievedScoresRow);
spreadsheetWriter
.SetFontBold(true)
.Write("Gemiddelde aantal punten")
.SetFontBold(false)
.MoveRight()
.PlaceStandardFormula(scoresStartPosition, scoresEndPosition, FormulaType.AVERAGE)
.NewLine();
var gradesStartPosition = new Point(StartOfStudentsColumn, GradesRow);
var gradesEndPosition = new Point(StartOfStudentsColumn + NumberOfStudents, GradesRow);
spreadsheetWriter
.SetFontBold(true)
.Write("Gemiddelde cijfer")
.SetFontBold(false)
.MoveRight()
.PlaceStandardFormula(gradesStartPosition, gradesEndPosition, FormulaType.AVERAGE);
}
public void Write(ISpreadsheetWriter spreadsheetWriter)
{
spreadsheetWriter.CurrentPosition = StartPoint;
spreadsheetWriter
.SetFontBold(true)
.SetBorder(BorderStyle.Double, BorderDirection.Bottom, Color.Black)
.Write("Totaal")
.SetFontBold(false);
int columnsBetweenFirstPoint = ScoreTopPoint.X - spreadsheetWriter.CurrentPosition.X;
for (int i = 0; i < columnsBetweenFirstPoint; i++)
{
spreadsheetWriter.MoveRight();
spreadsheetWriter.Write(string.Empty);
}
const int maximumPointsColumn = 1;
int numberOfScoreColumns = NumberOfStudents + maximumPointsColumn;
for (int columnIndex = 0; columnIndex < numberOfScoreColumns; columnIndex++)
{
var startPosition = new Point(spreadsheetWriter.CurrentPosition.X, ScoreTopPoint.Y);
var lastQuestionRowOffset = 1;
var endPosition = new Point(spreadsheetWriter.CurrentPosition.X, spreadsheetWriter.CurrentPosition.Y - lastQuestionRowOffset);
spreadsheetWriter
.PlaceStandardFormula(startPosition, endPosition, FormulaType.SUM)
.MoveRight();
}
}
private static void WriteHeaderRow(ISpreadsheetWriter writer, IList <DisplayField> transactionColumns)
{
foreach (var column in transactionColumns)
{
writer.WriteField(column.GetHeaderValue());
}
writer.NextRecord();
}
private static void WriteTransaction(ISpreadsheetWriter writer, SqlLedgerEntry transaction, IEnumerable <DisplayField> transactionColumns)
{
foreach (var column in transactionColumns)
{
writer.WriteField(column.GetDisplayValue(transaction));
}
writer.NextRecord();
}
public void ResetStyling_WithValidWriter_ReturnsInstanceOfSut()
{
// Arrange
// Act
ISpreadsheetWriter result = _sut.ResetStyling();
// Assert
result.Should().Be(_sut);
}
public void SetFontBold_WithValidValue_ReturnsInstanceOfSut()
{
// Arrange
bool value = _fixture.Create <bool>();
// Act
ISpreadsheetWriter result = _sut.SetFontBold(value);
// Assert
result.Should().Be(_sut);
}
private static void AddTotalPointsRow(ISpreadsheetWriter spreadsheetWriter,
Point scorePointTop,
int numberOfStudents)
{
var totalPointsBlock = new TotalScoresBlock(
spreadsheetWriter.CurrentPosition,
scorePointTop,
numberOfStudents);
totalPointsBlock.Write(spreadsheetWriter);
}
public void SetFontColor_WithValidColor_ReturnsInstanceOfSut()
{
// Arrange
Color value = _fixture.Create <Color>();
// Act
ISpreadsheetWriter result = _sut.SetFontColor(value);
// Assert
result.Should().Be(_sut);
}
public void SetFontSize_WithValidSize_ReturnsInstanceOfSut()
{
// Arrange
float value = _fixture.Create <float>();
// Act
ISpreadsheetWriter result = _sut.SetFontSize(value);
// Assert
result.Should().Be(_sut);
}
public void SetTextRotation_WithValidRotation_ReturnsInstanceOfSut()
{
// Arrange
int value = 10;
// Act
ISpreadsheetWriter result = _sut.SetTextRotation(value);
// Assert
result.Should().Be(_sut);
}
public void SetFormat_WithValidFormat_ReturnsInstanceOfSut()
{
// Arrange
string value = _fixture.Create <string>();
// Act
ISpreadsheetWriter result = _sut.SetFormat(value);
// Assert
result.Should().Be(_sut);
}
public void PlaceStandardFormula_WithValidValue_ReturnsInstanceOfSut()
{
// Arrange
var startPosition = new Point(_fixture.Create <short>(), _fixture.Create <short>());
var endPosition = new Point(_fixture.Create <short>(), _fixture.Create <short>());
var formulaType = _fixture.Create <FormulaType>();
// Act
ISpreadsheetWriter result = _sut.PlaceStandardFormula(startPosition, endPosition, formulaType);
// Assert
result.Should().Be(_sut);
}
private static ConfigurationBlock AddConfiguration(Test test, ISpreadsheetWriter spreadsheetWriter)
{
var configurationBlock = new ConfigurationBlock(spreadsheetWriter.CurrentPosition,
test.StandardizationFactor,
test.MinimumGrade);
spreadsheetWriter
.NewLine()
.NewLine();
configurationBlock.Write(spreadsheetWriter);
return(configurationBlock);
}
public ExcelFile(string fileLocation, Metadata metadata)
{
var fileInfo = new FileInfo(fileLocation);
_excelPackage = new ExcelPackage(fileInfo);
_excelPackage.Workbook.Properties.Author = metadata.Author;
_excelPackage.Workbook.Properties.Title = metadata.Title;
_excelPackage.Workbook.Properties.Subject = metadata.Subject;
_excelPackage.Workbook.Properties.Created = metadata.Created;
ExcelWorksheet worksheet = _excelPackage.Workbook.Worksheets.Add(metadata.Title);
_writer = new ExcelSpreadsheetWriter(worksheet);
}
public void PlaceCustomFormula_WithValidValue_ReturnsInstanceOfSut()
{
// Arrange
var expectedFormula = _fixture.Create <string>();
var formulaBuilder = new Mock <IFormulaBuilder>();
formulaBuilder.Setup(x => x.Build())
.Returns(expectedFormula);
// Act
ISpreadsheetWriter result = _sut.PlaceCustomFormula(formulaBuilder.Object);
// Assert
result.Should().Be(_sut);
}
private static void AddAverageResults(ISpreadsheetWriter spreadsheetWriter,
int achievedScoresRow,
int gradesRow,
int scoresStartColumn,
int numberOfStudents)
{
var averagesBlock = new StatisticsBlock(
spreadsheetWriter.CurrentPosition,
achievedScoresRow,
gradesRow,
scoresStartColumn,
numberOfStudents);
averagesBlock.Write(spreadsheetWriter);
}
public void PlaceCustomFormula_WithFormulaBuilder_PlacesFormulaToCurrentCell()
{
// Arrange
var expectedFormula = _fixture.Create <string>();
var formulaBuilder = new Mock <IFormulaBuilder>();
formulaBuilder.Setup(x => x.Build())
.Returns(expectedFormula);
// Act
ISpreadsheetWriter result = _sut.PlaceCustomFormula(formulaBuilder.Object);
// Assert
var cell = _worksheet.GetCell(_sut.CurrentPosition);
cell.Formula.Should().Be(expectedFormula);
}
public void Write(ISpreadsheetWriter spreadsheetWriter)
{
spreadsheetWriter.CurrentPosition = StartPoint;
spreadsheetWriter
.SetFontSize(TitleSize)
.Write(TestName.Value)
.ResetStyling()
.NewLine()
.SetFontBold(true)
.Write("Gemaakt op:")
.SetFontBold(false)
.MoveRight()
.Write(CreatedOn.ToString(DateFormat))
.NewLine()
.Write("Cifra:")
.MoveRight()
.Write(ApplicationVersion);
}
public void Setup()
{
var data = GetData(RowSize);
documentWriter = Spreadsheet
.New()
.WithSheet(
Specification.Sheet()
.WithName("benchmark")
.WithRows(data, (item, index) => Specification
.Row()
.OnIndex(index)
.WithCell(Specification.Cell().OnColumn(1).WithContent(item.Text))
.WithCell(Specification.Cell().OnColumn(2).WithContent(item.Number))
.WithCell(Specification.Cell().OnColumn(3).WithContent(item.Money))
.WithCell(Specification.Cell().OnColumn(4).WithContent(item.Date))
));
}
public void Write(ISpreadsheetWriter spreadsheetWriter)
{
for (int index = HeaderRow; index < TotalRow; index++)
{
var y = index + 1;
for (int columnIndex = 1; columnIndex <= MostRightColumn; columnIndex++)
{
spreadsheetWriter.CurrentPosition = new Point(columnIndex, y);
spreadsheetWriter.SetHorizontalAlignment(HorizontalAlignment.Center);
if (AssignmentBottomRows.Contains(y))
{
spreadsheetWriter.SetBorder(BorderStyle.Thin, BorderDirection.Bottom, Color.Black);
}
spreadsheetWriter.ApplyStyling();
spreadsheetWriter.ResetStyling();
}
}
}
private static void AddGradesRow(ISpreadsheetWriter spreadsheetWriter,
int achievedScoresRow,
int scoresStartColumn,
Point maximumPointsPosition,
Point minimumGradePosition,
Point standardizationfactorPosition,
int numberOfStudents,
IFormulaBuilderFactory formulaBuilderFactory)
{
var totalPointsBlock = new GradesBlock(
spreadsheetWriter.CurrentPosition,
formulaBuilderFactory,
achievedScoresRow,
scoresStartColumn,
maximumPointsPosition,
standardizationfactorPosition,
minimumGradePosition,
numberOfStudents);
totalPointsBlock.Write(spreadsheetWriter);
}
public async Task <Application.Models.Spreadsheet.SaveResult> CreateTestResultsSpreadsheetAsync(Class @class, Test test, Application.Models.Spreadsheet.Metadata metadata)
{
var libraryMetadata = metadata.MapToLibraryModel();
ISpreadsheetFile spreadsheetFile = _spreadsheetFileFactory.Create(_locationProvider.GetSpreadsheetDirectoryPath().Value, libraryMetadata);
ISpreadsheetWriter spreadsheetWriter = spreadsheetFile.GetSpreadsheetWriter();
AddTitle(test, metadata, spreadsheetWriter);
spreadsheetWriter.NewLine();
var configurationBlock = AddConfiguration(test, spreadsheetWriter);
PrintScoreSheet(
@class,
test,
spreadsheetWriter,
configurationBlock);
SaveResult saveResult = await spreadsheetFile.SaveAsync();
return(saveResult.MapToModel());
}
private int PrintAssignments(ISpreadsheetWriter spreadsheetWriter)
{
int lastQuestionRow = 0;
var totalAssignments = TotalAssignments.Count();
for (int assignmentIndex = 0; assignmentIndex < totalAssignments; assignmentIndex++)
{
Assignment assignment = TotalAssignments.ElementAt(assignmentIndex);
spreadsheetWriter.MoveDownTimes(assignment.NumberOfQuestions);
AssignmentBottomRows.Add(spreadsheetWriter.CurrentPosition.Y);
bool isLastAssignmentReached = assignmentIndex == totalAssignments - 1;
if (isLastAssignmentReached)
{
lastQuestionRow = spreadsheetWriter.CurrentPosition.Y;
}
}
return(lastQuestionRow);
}
public void Write(ISpreadsheetWriter spreadsheetWriter)
{
spreadsheetWriter.CurrentPosition = StartPosition;
spreadsheetWriter
.SetFontBold(true)
.SetBorder(BorderStyle.Double, BorderDirection.Bottom, Color.Black)
.Write("Cijfer")
.SetFontBold(false);
int columnsBetweenFirstPoint = ScoresStartColumn - spreadsheetWriter.CurrentPosition.X;
for (int i = 0; i < columnsBetweenFirstPoint; i++)
{
spreadsheetWriter.MoveRight();
spreadsheetWriter.Write(string.Empty);
}
ICellRange maximumScoreCell = spreadsheetWriter.GetCellRange(MaximumScorePosition);
ICellRange standardizationFactorCell = spreadsheetWriter.GetCellRange(StandardizationFactorPosition);
ICellRange minimumGradeCell = spreadsheetWriter.GetCellRange(MinimumGradePosition);
const int maximumPointsColumn = 1;
int numberOfScoreColumns = NumberOfStudents + maximumPointsColumn;
spreadsheetWriter.SetFormat("0.0");
for (int columnIndex = 0; columnIndex < numberOfScoreColumns; columnIndex++)
{
var achievedScorePosition = new Point(spreadsheetWriter.CurrentPosition.X, AchievedScoresRow);
ICellRange achievedScoreCell = spreadsheetWriter.GetCellRange(achievedScorePosition);
spreadsheetWriter
.PlaceLessThanRule(5.5, Color.Red)
.PlaceCustomFormula(SetupGradeFormula(achievedScoreCell,
maximumScoreCell,
standardizationFactorCell,
minimumGradeCell))
.MoveRight();
}
spreadsheetWriter.ResetStyling();
}
public void Write(ISpreadsheetWriter spreadsheetWriter)
{
spreadsheetWriter.CurrentPosition = StartPoint;
var questionNamesCollumns = NumberOfVersions;
spreadsheetWriter
.SetBorder(BorderStyle.Thin, BorderDirection.Bottom, Color.Black)
.SetHorizontalAlignment(HorizontalAlignment.Center)
.SetFontBold(true)
.Write("Opgave");
for (int i = 0; i < questionNamesCollumns; i++)
{
spreadsheetWriter.MoveRight();
spreadsheetWriter.Write(string.Empty);
}
spreadsheetWriter
.Write("Punten")
.ResetStyling();
ScoresHeaderPosition = spreadsheetWriter.CurrentPosition;
LastQuestionRow = PrintAssignments(spreadsheetWriter);
}
public void Write(ISpreadsheetWriter spreadsheetWriter)
{
spreadsheetWriter.CurrentPosition = StartPoint;
spreadsheetWriter
.SetFontBold(true)
.Write("Configuratie");
spreadsheetWriter
.NewLine()
.Write("Normering")
.SetFontBold(false)
.MoveRight()
.Write(StandardizationFactor.Value);
StandardizationfactorPosition = spreadsheetWriter.CurrentPosition;
spreadsheetWriter
.NewLine()
.SetFontBold(true)
.Write("Minimale cijfer")
.SetFontBold(false)
.MoveRight()
.Write(MinimumGrade.Value);
MinimumGradePosition = spreadsheetWriter.CurrentPosition;
}
public void Write(ISpreadsheetWriter spreadsheetWriter)
{
spreadsheetWriter.CurrentPosition = StartPoint;
IOrderedEnumerable <Student> orderedStudents = Students
.OrderBy(x => x.LastName.Value);
foreach (Student student in orderedStudents)
{
var nameBuilder = new StringBuilder(student.FirstName.Value);
if (student.Infix != null)
{
nameBuilder.Append(' ');
nameBuilder.Append(student.Infix);
}
nameBuilder.Append(' ');
nameBuilder.Append(student.LastName.Value);
spreadsheetWriter
.SetTextRotation(40)
.SetBorder(BorderStyle.Thin, BorderDirection.Bottom, Color.Black)
.SetHorizontalAlignment(HorizontalAlignment.Center)
.Write(nameBuilder.ToString())
.ResetStyling();
for (int i = 0; i < _lastQuestionRow; i++)
{
spreadsheetWriter
.MoveDown()
.SetHorizontalAlignment(HorizontalAlignment.Center)
.Write(string.Empty);
}
spreadsheetWriter.CurrentPosition = new Point(spreadsheetWriter.CurrentPosition.X + 1, StartPoint.Y);
}
MostOuterColumn = spreadsheetWriter.CurrentPosition.X;
}
private static void WriteTransactions(IEnumerable <SqlLedgerEntry> transactions, IList <DisplayField> columns, ISpreadsheetWriter writer)
{
foreach (var transaction in transactions)
{
WriteTransaction(writer, transaction, columns);
}
}
private void PrintScoreSheet(
Class @class,
Test test,
ISpreadsheetWriter spreadsheetWriter,
ConfigurationBlock configurationBlock)
{
spreadsheetWriter
.NewLine()
.NewLine();
var assignmentsBlock = new AssignmentsBlock(spreadsheetWriter.CurrentPosition,
test.Assignments,
test.NumberOfVersions);
assignmentsBlock.Write(spreadsheetWriter);
spreadsheetWriter.CurrentPosition = assignmentsBlock.ScoresHeaderPosition;
spreadsheetWriter.MoveRight();
int studentNamesStartColumn = spreadsheetWriter.CurrentPosition.X;
var studentNamesBlock = new StudentNamesBlock(spreadsheetWriter.CurrentPosition, @class.Students, assignmentsBlock.LastQuestionRow);
studentNamesBlock.Write(spreadsheetWriter);
spreadsheetWriter.CurrentPosition = new Point(spreadsheetWriter.CurrentPosition.X, assignmentsBlock.LastQuestionRow);
spreadsheetWriter.NewLine();
var scoresTopRow = new Point(assignmentsBlock.ScoresHeaderPosition.X, assignmentsBlock.ScoresHeaderPosition.Y + 1);
AddTotalPointsRow(spreadsheetWriter,
scoresTopRow,
@class.Students.Count);
var achievedScoresRow = spreadsheetWriter.CurrentPosition.Y;
spreadsheetWriter.NewLine();
var numberOfStudents = @class.Students.Count;
AddGradesRow(spreadsheetWriter,
achievedScoresRow,
assignmentsBlock.ScoresHeaderPosition.X,
new Point(assignmentsBlock.ScoresHeaderPosition.X, achievedScoresRow),
configurationBlock.MinimumGradePosition,
configurationBlock.StandardizationfactorPosition,
numberOfStudents,
_formulaBuilderFactory);
var gradesRow = spreadsheetWriter.CurrentPosition.Y;
spreadsheetWriter.NewLine();
spreadsheetWriter.NewLine();
AddAverageResults(
spreadsheetWriter,
achievedScoresRow,
gradesRow,
studentNamesStartColumn,
numberOfStudents);
var borderBlock = new BorderBlock(
assignmentsBlock.ScoresHeaderPosition.Y,
assignmentsBlock.AssignmentBottomRows,
achievedScoresRow,
gradesRow,
studentNamesBlock.MostOuterColumn);
borderBlock.Write(spreadsheetWriter);
}
private static void WriteDescriptionRow(ISpreadsheetWriter writer, string description)
{
writer.WriteField(description);
writer.NextRecord();
}
