/// <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="gradelog">The <see cref="ExcelAllGrade.model.Gradelog"/> which should be added to the <c>Excel.Worksheeet</c>.</param>
/// <param name="index">The index of the <see cref="ExcelAllGrade.model.Gradelog"/>.</param>
private void addGradelogToLogtable(Excel.Worksheet sheet, Gradelog gradelog, int index)
{
Microsoft.Office.Interop.Excel.Range gradelogRange = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range("A" + (index + 1),
"E" + (index + 1));
gradelogRange.Cells[1, COLUMN_DATE].Value = gradelog.GradelogTimestamp.ToString();
gradelogRange.Cells[1, COLUMN_STUDENT].Value = gradelog.GradelogStudent;
gradelogRange.Cells[1, COLUMN_TYPE].Value = gradelog.GradelogType;
gradelogRange.Cells[1, COLUMN_GRADING].Value = gradelog.GradelogGrading;
gradelogRange.Cells[1, COLUMN_COMMENT].Value = gradelog.GradelogComment;
if (gradelog.GradelogGrading == 5)
{
gradelogRange.Cells[1, COLUMN_GRADING].Font.Bold = true;
}
if (gradelog.GradelogType.Equals(Gradelog.GradelogTypeEnum.Test.GetStringValue()))
{
gradelogRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(COLOR_TEST);
}
else if (gradelog.GradelogType.Equals(Gradelog.GradelogTypeEnum.SA.GetStringValue()))
{
gradelogRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(COLOR_SA);
}
else
{
gradelogRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.White);
}
}
/// <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);
}
}
