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