private static void CreateSkeleton(Worksheet worksheet, DataTable dataTable)
{
var classRoomNumbers = new HashSet <string>();
foreach (DataRow row in dataTable.Rows)
{
var number = row[2].ToString();
if (!classRoomNumbers.Contains(number))
{
classRoomNumbers.Add(number);
}
}
var len = classRoomNumbers.Count;
for (int i = 0; i < 7; i++)
{
var start = 4 + i * len;
worksheet.Range[worksheet.Cells[start, 1], worksheet.Cells[start + len - 1, 1]].Merge();
worksheet.Cells[start, 1] = i + 1;
worksheet.Range[worksheet.Cells[start, 2], worksheet.Cells[start + len - 1, 2]].Merge();
worksheet.Cells[start, 2] = LessonTimeDto.GetPeriodFromNumber(i + 1);
}
ClassRooms = classRoomNumbers.ToList();
}
private static void FillTable(DataTable dataTable, Worksheet worksheet)
{
List <TeacherWeeksWithCellIndex> teachersData = new List <TeacherWeeksWithCellIndex>();
var currentTime = -1;
foreach (DataRow row in dataTable.Rows)
{
var dayName = row[0].ToString();
var lessonTimeNumber = LessonTimeDto.GetNumberFromPeriod(row[1].ToString());
var classroom = row[2].ToString();
var cellIndex = GetIndexByDayLessonClassroom(dayName, lessonTimeNumber, classroom);
if (currentTime != lessonTimeNumber)
{
teachersData.Clear(); //we dont give a f**k if teacher has same classes on different lesson times
currentTime = lessonTimeNumber;
}
var teacherName = string.Format("{0} {1}", row[3], row[4]);
var weeks = row[5].ToString();
AddCellInfo(worksheet, cellIndex, teacherName, weeks);
TeacherWeeksWithCellIndex currentTeacherData = new TeacherWeeksWithCellIndex
{
CellIndex = cellIndex,
TeacherName = teacherName,
Weeks = Utils.Utils.ParseWeeks(weeks),
DayName = dayName
};
foreach (var teacherData in teachersData)
{
//teacher has more than one lesson in same time
if (teacherData.TeacherName == currentTeacherData.TeacherName &&
teacherData.DayName == currentTeacherData.DayName &&
teacherData.Weeks.Intersect(currentTeacherData.Weeks).Any())
{
GenericExcelExport.SetCellBackground(worksheet, teacherData.CellIndex,
new CellIndex {
x = teacherData.CellIndex.x, y = teacherData.CellIndex.y + 1
}, XlRgbColor.rgbIndianRed);
GenericExcelExport.SetCellBackground(worksheet, currentTeacherData.CellIndex,
new CellIndex {
x = currentTeacherData.CellIndex.x, y = currentTeacherData.CellIndex.y + 1
}, XlRgbColor.rgbIndianRed);
}
}
teachersData.Add(currentTeacherData);
}
}
private static void FillScheduleRecords()
{
var currentDay = string.Empty;
var currentTime = string.Empty;
DayDto currentDayDto = null;
for (int i = 9; i < _rows.Length; i++)
{
var nextDay = _rows[i][0].Value.ToString();
var nextTime = _rows[i][1].Value.ToString();
if (!string.IsNullOrEmpty(nextDay))
{
currentDay = nextDay;
currentDayDto = new DayDto {
DayNumber = DayDto.GetNumberByName(currentDay), DayName = currentDay
};
}
else if (LessonTimeDto.GetNumberFromPeriod(nextTime) == 1)
{
var curDayIndex = DayDto.GetNumberByName(currentDay);
currentDay = DayDto.DayNameByNumber[curDayIndex + 1];
currentDayDto = new DayDto {
DayNumber = curDayIndex + 1, DayName = currentDay
};
}
if (!string.IsNullOrEmpty(nextTime))
{
currentTime = nextTime;
}
if (currentDay != "" && currentTime != "")
{
AppendNewScheduleRecord(currentDayDto, currentTime, _rows[i]);
}
}
}
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;
}
}
private static void AppendNewScheduleRecord(DayDto dayDto, string time, Row row)
{
try
{
string subject = row[2].Value.ToString();
TeacherDto teacher = GetTeacherData(row[3].Value.ToString());
ClassRoomDto classRoom = new ClassRoomDto {
Number = row[6].Value.ToString().Replace(" ", String.Empty)
};
var weeksString = row[5].Value.ToString();
if (string.IsNullOrEmpty(teacher.LastName))
{
teacher.LastName = "Вакансія"; // in case of null
}
var groupStr = row[4].Value.ToString(); //either number or 'lecture'
if (string.IsNullOrEmpty(subject) || subject.Length <= 2)
{
return;
}
if (string.IsNullOrEmpty(classRoom.Number) ||
string.IsNullOrEmpty(weeksString) || string.IsNullOrEmpty(groupStr))
{
throw new InvalidInputException("Некоректні дані: " + dayDto.DayName + ", " + time + " у файлі " + _currentFilePath);
}
LessonTimeDto lessonTime = new LessonTimeDto {
Number = LessonTimeDto.GetNumberFromPeriod(time)
};
string group = string.Empty;
if (groupStr.Any(char.IsDigit))
{
group = groupStr.Replace(" ", "");
}
else
{
group = string.Empty;
}
LessonTypeDto lessonType = new LessonTypeDto();
lessonType.Id = LessonTypeDto.GetIdByType(group == string.Empty ? LessonType.Лекція : LessonType.Практика);
ScheduleRecordDto scheduleRecord = new ScheduleRecordDto
{
YearOfStudying = _yearOfStudying,
LessonTime = lessonTime,
Subject = subject,
LessonType = lessonType,
Group = group,
Day = dayDto,
Specialty = _specialty,
ClassRoom = classRoom,
Teacher = teacher,
Weeks = weeksString
};
var weeksStr = row[5].Value.ToString().Replace(".", ","); //replace 6.9 to 6,9
var weeksList = Utils.Utils.ParseWeeks(weeksStr);
_weekScheduleRecords.Add(scheduleRecord, new List <int>());
foreach (var weekNumber in weeksList)
{
_weekScheduleRecords[scheduleRecord].Add(weekNumber);
}
}
catch (InvalidInputException e)
{
Logger.LogException(e);
throw e;
}
catch (Exception e)
{
Logger.LogException(e);
}
}
