private static void AddCellInfo(Worksheet worksheet, CellIndex cellIndex, string teacherName, string weeks) { var prevWeeks = worksheet.Cells[cellIndex.x, cellIndex.y + 1].Text.ToString(); var hasPrevWeeks = !string.IsNullOrEmpty(prevWeeks); if (!hasPrevWeeks) { worksheet.Cells[cellIndex.x, cellIndex.y] = teacherName; worksheet.Cells[cellIndex.x, cellIndex.y + 1] = weeks; return; } var prevName = worksheet.Cells[cellIndex.x, cellIndex.y].Text.ToString(); if (prevName != teacherName) { worksheet.Cells[cellIndex.x, cellIndex.y] = prevName + "/\n" + teacherName; worksheet.Cells[cellIndex.x, cellIndex.y + 1] = prevWeeks + "/\n" + weeks; } else { worksheet.Cells[cellIndex.x, cellIndex.y + 1] = prevWeeks + ", " + weeks; } List <int> prevWeeksList = Utils.Utils.ParseWeeks(prevWeeks); List <int> currWeeksList = Utils.Utils.ParseWeeks(weeks); if (prevWeeksList.Intersect(currWeeksList).Any()) { GenericExcelExport.SetCellBackground(worksheet, cellIndex, new CellIndex { x = cellIndex.x, y = cellIndex.y + 1 }, XlRgbColor.rgbRed); } }
public static void SetCellBackground(Worksheet worksheet, CellIndex from, CellIndex to, XlRgbColor color) { worksheet.Range[worksheet.Cells[from.x, from.y], worksheet.Cells[to.x, to.y]].Interior.Color = color; }
private static void FillData(Worksheet worksheet, DataTable dataTable) { var currentDayName = string.Empty; int currentLessonTimeNumber = -1; string currentLessonTime = string.Empty; var currentDayTimeCell = new CellIndex(4, 1); string currentClassRoom = string.Empty; var currentClassRoomCell = new CellIndex(4, 2); string currerntTeacher = string.Empty; var currentTeacherCell = new CellIndex(4, 3); foreach (DataRow dataRow in dataTable.Rows) { var dayTimeWasChanged = false; var dayName = dataRow[1].ToString(); var lessonTimeNumber = int.Parse(dataRow[2].ToString()); if (string.IsNullOrEmpty(currentDayName) || currentDayName != dayName) { dayTimeWasChanged = true; currentDayName = dayName; } if (currentLessonTimeNumber != lessonTimeNumber) { dayTimeWasChanged = true; currentLessonTimeNumber = lessonTimeNumber; currentLessonTime = LessonTimeDto.GetPeriodFromNumber(lessonTimeNumber); } if (dayTimeWasChanged) { worksheet.Range[worksheet.Cells[currentDayTimeCell.x, currentDayTimeCell.y], worksheet.Cells[currentDayTimeCell.x, lastWeekYIndex - 1]].Cells.Borders[XlBordersIndex.xlEdgeBottom].Weight = 2d; worksheet.Cells[currentDayTimeCell.x, currentDayTimeCell.y] = currentDayName + "\n" + currentLessonTime; currentDayTimeCell.x++; } var classRoom = dataRow[3].ToString(); if (currentClassRoom != classRoom || dayTimeWasChanged) { worksheet.Range[worksheet.Cells[currentClassRoomCell.x, currentClassRoomCell.y], worksheet.Cells[currentClassRoomCell.x, lastWeekYIndex - 1]].Cells.Borders[XlBordersIndex.xlEdgeBottom].Weight = 2d; if (currentClassRoomCell.x % 2 == 0) { worksheet.Range[worksheet.Cells[currentClassRoomCell.x, currentClassRoomCell.y], worksheet.Cells[currentClassRoomCell.x, lastWeekYIndex - 1]] .Interior.Color = XlRgbColor.rgbLightGray; } currentClassRoom = classRoom; worksheet.Cells[currentClassRoomCell.x, currentClassRoomCell.y] = currentClassRoom; currentClassRoomCell.x++; // if dayTime Cell was not changed we need to merge cells if (!dayTimeWasChanged) { var previousXCoord = currentDayTimeCell.x - 1; worksheet.Range[worksheet.Cells[previousXCoord, currentDayTimeCell.y], worksheet.Cells[currentDayTimeCell.x++, currentDayTimeCell.y]].Merge(); } } var teacher = dataRow[4].ToString() + " " + dataRow[5].ToString(); if (currerntTeacher != teacher || dayTimeWasChanged) { currerntTeacher = teacher; worksheet.Cells[currentTeacherCell.x, currentTeacherCell.y] = currerntTeacher; lastXIndex = currentTeacherCell.x; currentTeacherCell.x++; } var weekNumber = dataRow[0].ToString(); var weekXCoord = currentTeacherCell.x - 1; var weekYCoord = WeekNumberCellIndex[weekNumber].y; worksheet.Cells[weekXCoord, weekYCoord] = dataRow[6].ToString() + " " + dataRow[7].ToString(); // Console.WriteLine($"{dataRow[0]}, {dataRow[1]}, {dataRow[2]}, {dataRow[3]}, {dataRow[4]}, {dataRow[5]}, {dataRow[6]}"); dayTimeWasChanged = false; } }