/// <summary>
/// Adds a single <see cref="ExcelAllGrade.model.Gradelog"/> to the <c>Excel.Worksheet</c>.
/// </summary>
/// <param name="sheet">The <c>Excel.Worksheet</c> to write the <see cref="ExcelAllGrade.model.Gradelog"/>s in.</param>
/// <param name="range">The <c>Excel.Range</c> in which the <see cref="ExcelAllGrade.model.Gradelog"/> can be written.</param>
/// <param name="gradelog">The <see cref="ExcelAllGrade.model.Gradelog"/> which should be added to the <c>Excel.Worksheeet</c>.</param>
/// <param name="studentCount">The amount of <see cref="ExcelAllGrade.model.Student"/>s in the <c>Excel.Worksheet</c>.</param>
private void addGradelogToSpreadsheet(Excel.Worksheet sheet, Excel.Range range, Gradelog gradelog, int studentCount)
{
// Gets all cells with the timestamp and the GradelogType of the gradelog
List<Excel.Range> dateRangeColumns = SpreadsheetUtil.findAllDateOccurences(sheet.get_Range("A1", headerRange + "1"), gradelog.GradelogTimestamp.Date.ToString().Split(' ')[0], gradelog.GradelogType);
string comment = "";
try
{
comment = dateRangeColumns.ElementAt(0).Comment.Text();
}
catch (ArgumentOutOfRangeException) { }
// If dateColumn == null or if the comment is different from the gradelog-type, create a new column
if (dateRangeColumns.Count == 0 || (dateRangeColumns.Count > 0 && !gradelog.GradelogType.Equals(comment) && !String.IsNullOrEmpty(comment)))
{
headerRange = headerRange.ExcelIncrementByOne();
Excel.Range dateColumn = sheet.get_Range(headerRange + "1", headerRange + (studentCount + 1));
dateColumn.set_Item(1, 1, gradelog.GradelogTimestamp.Date.ToString().Split(' ')[0]);
setColumnColor(dateColumn, gradelog.GradelogType);
sheet.get_Range(headerRange + "1").AddComment(gradelog.GradelogType);
}
// Get the location of the new gradelog-cell
Excel.Range nameRangeRow = range.Find(gradelog.GradelogStudent);
string address = SpreadsheetUtil.rangeAddress(nameRangeRow);
string row = new String(address.Where(Char.IsDigit).ToArray());
dateRangeColumns = SpreadsheetUtil.findAllDateOccurences(sheet.get_Range("A1", headerRange + "1"), gradelog.GradelogTimestamp.Date.ToString().Split(' ')[0], gradelog.GradelogType);
address = SpreadsheetUtil.rangeAddress(dateRangeColumns.ElementAt(0));
string column = new String(address.Where(c => c != '-' && (c < '0' || c > '9')).ToArray());
Excel.Range newGradingCell = sheet.get_Range(column + row);
// If a grading already exists, it means that there are 2 or more gradings for that timestamp
// A new column has to be inserted if all cells in a date-column are already filled
int index = 0;
while (!String.IsNullOrWhiteSpace(newGradingCell.Text))
{
if (index < dateRangeColumns.Count)
{
address = SpreadsheetUtil.rangeAddress(dateRangeColumns.ElementAt(index));
column = new String(address.Where(c => c != '-' && (c < '0' || c > '9')).ToArray());
newGradingCell = sheet.get_Range(column + row);
index++;
}
else
{
Excel.Range columnNew = newGradingCell.EntireColumn;
columnNew.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftToRight, false);
Excel.Range temporaryRange = sheet.Cells[1, columnNew.Column - 1];
temporaryRange.set_Item(1, 1, gradelog.GradelogTimestamp.Date.ToString().Split(' ')[0]);
temporaryRange.AddComment(gradelog.GradelogType);
setColumnColor(sheet.Range[sheet.Cells[1, temporaryRange.Column], sheet.Cells[studentCount+1, temporaryRange.Column]], gradelog.GradelogType);
newGradingCell = sheet.Cells[Convert.ToInt32(row), newGradingCell.Column - 1];
headerRange = headerRange.ExcelIncrementByOne();
}
}
// Sets the content of the cell
newGradingCell.set_Item(1, 1, gradelog.GradelogGrading);
if (gradelog.GradelogGrading == 5)
{
newGradingCell.Font.Bold = true;
}
if (!String.IsNullOrEmpty(gradelog.GradelogComment))
{
newGradingCell.AddComment(gradelog.GradelogComment);
}
}