Ejemplo n.º 1
0
        /// <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);
            }
        }