def writeSemesterGroupRows(worksheet, semesterGroup, timeTableSolution: Solution, orm: ORM, row) -> int: """ Writes the timetable for the given semestergroup to the excel file in the given worksheet. Args: worksheet: The worksheet to write the timetables in. semesterGroup: The semesterGroup to write the timetable of. timeTableSolution: The timetable Solution. orm: The ORM file with all objects of the timetable. row: The current row in the worksheet to start with the timetables. Returns: The next row after the written timetables. """ # Make background for the semester number row. makeBackground(worksheet, len(orm.getTimeslots()) + 3, row, formatMap["semesterNumberFormat"]) # Write semester number. worksheet.write_string(row, 0, "%d. Semester" % semesterGroup.semester, formatMap["semesterNumberFormat"]) worksheet.set_row(row, 14) # Row height. row += 1 # Split the lessons in the course types and write rows with the lessons for each type. courseTypes = list( set(map(lambda l: l.course.type, semesterGroup.getLessons()))) for courseType in courseTypes: row = writeCourseTypeRow(worksheet, courseType, timeTableSolution, semesterGroup, orm, row) return row
def writeStudyCourseRows(worksheet, semesterGroups, timeTableSolution: Solution, orm: ORM, row) -> int: """ Writes the timetables for the given semesterGroups to the excel file in the given worksheet. Args: worksheet: The worksheet to write the timetables in. semesterGroups: The semesterGroups to write the timetables of. timeTableSolution: The timetable Solution. orm: The ORM file with all objects of the timetable. row: The current row in the worksheet to start with the timetables. Returns: The next row after the written timetables. """ # Make background for the study course name row. makeBackground(worksheet, len(orm.getTimeslots()) + 3, row, formatMap["mainHeaderFormat"]) worksheet.write_string(row, 0, semesterGroups[0].study_course, formatMap["mainHeaderFormat"]) worksheet.set_row(row, 27.75) # Row height. row += 1 # Write timetables of the semester groups of the given study course. for semesterGroup in semesterGroups: row = writeSemesterGroupRows(worksheet, semesterGroup, timeTableSolution, orm, row) return row + 1
def writeDayAndTimeLine(worksheet, semesterName, orm, row) -> int: """ Write the rows with the timetable's weekdays and Timeslots. Args: worksheet: The worksheet to add the rows in. semesterName: The name of the semester. orm: The ORM file with all objects of the timetable. row: The current row to write the content in. Returns: The next row after the written timetables. """ dayList = orm.getTimeslotsPerDay() dayMap = {} # Translate weekday names. for li in dayList: if li[0].weekday == Timeslot.MONDAY: dayMap["Montag"] = li elif li[0].weekday == Timeslot.TUESDAY: dayMap["Dienstag"] = li elif li[0].weekday == Timeslot.WEDNESDAY: dayMap["Mittwoch"] = li elif li[0].weekday == Timeslot.THURSDAY: dayMap["Donnerstag"] = li elif li[0].weekday == Timeslot.FRIDAY: dayMap["Freitag"] = li # Merge cells for semester name and writes the semester's name into it. worksheet.merge_range(row, 0, row + 2, 2, semesterName, formatMap["semesterNameFormat"]) column = 3 worksheet.set_row(row, 26) for day, timeslots in dayMap.items(): # Write the Timeslots for each day. dayLength = len(timeslots) # Merge cells for weekday names and write the weekday's name into it. worksheet.merge_range(row, column, row, column + dayLength - 1, day, formatMap["weekdayFormat"]) for timeslot in timeslots: # Write Timeslots numbers and times. worksheet.write_string(row + 1, column, "%d." % timeslot.number, formatMap["timeslotNumberFormat"]) worksheet.write_string( row + 2, column, "%s - %s" % (timeslot.from_time, timeslot.to_time), formatMap["timeslotTimeFormat"]) column += 1 return row + 3
def writeToMultipleCells(self, worksheet, cell_range, input_list, input_type, axis=0): """write to Multiple Cells Args: cell_range: "A1:A10" input_strings_list: ["asd", "awasd", ..., "nju"] axis: 0 means write to next row first (default) 1 means write to next column first Usage: self.writeToMultipleCells( worksheet, "C6:G6", ["Total CPOPs", "4G", "3G+4G", "2G Only", "Unconnected"], "string", axis=1 ) self.writeToMultipleCells( worksheet, "C6:G6", [1, 2, 3, ..., 10], "number", axis=1 ) """ # get number from cell range (start_row, start_col), (end_row, end_col) = self.getNumberFromCellsRange(cell_range) # strings counter i = 0 # next row first if axis == 0: for col in range(start_col, end_col + 1): for row in range(start_row, end_row + 1): cell = utility.xl_rowcol_to_cell(row, col) if input_type == "string": worksheet.write_string(cell, input_list[i]) elif input_type == "number": worksheet.write_number(cell, input_list[i]) else: assert input_type in [ "string", "number" ], "Please choose from 'string' or 'number' as input_type" i += 1 # next column first elif axis == 1: for row in range(start_row, end_row + 1): for col in range(start_col, end_col + 1): cell = utility.xl_rowcol_to_cell(row, col) if input_type == "string": worksheet.write_string(cell, input_list[i]) elif input_type == "number": worksheet.write_number(cell, input_list[i]) else: assert input_type in [ "string", "number" ], "Please choose from 'string' or 'number' as input_type" i += 1
def writeTableFrame(self, worksheet, table_type): """Write Table Frame Args: table_type: 1: Competition Analysis 2: Country TAM Analysis-PER 3: Network Analysis """ if table_type == 1: # first line of table worksheet.write_string("B5", "Operator Summary") self.mergeCellsAndWrite(worksheet, "C5:G5", "Pops") self.mergeCellsAndWrite(worksheet, "H5:L5", "Settlements") # second line of table self.writeToMultipleCells( worksheet, "C6:G6", ["Total CPOPs", "4G", "3G+4G", "2G Only", "Unconnected"], "string", axis=1) self.writeToMultipleCells( worksheet, "H6:L6", ["Total", "4G", "3G+4G", "2G Only", "Unconnected"], "string", axis=1) elif table_type == 2: # first line of table self.mergeCellsAndWrite(worksheet, "C4:D4", "PER") self.mergeCellsAndWrite(worksheet, "E4:K4", "Settlements") # second line of table self.writeToMultipleCells( worksheet, "C5:K5", [ "Population", "%", "Total", "5000+", "3000->5000", "1000->3000", "500->1000", "300->500", "300->5000" ], "string", axis=1) # third to eighth line of table self.writeToMultipleCells( worksheet, "B6:B11", [ "Total Count", "Total Pops", "Existing CPOPS", "Total 4G CPOPS", "Total 3G CPOPS", "Total Fixed/WIFI CPOPS" ], "string", axis=0) # ninth line of table # worksheet.set_row(11, None, None, {'collapsed': 1, 'hidden': True}) # tenth to thirteenth line of table self.writeToMultipleCells( worksheet, "B12:B15", [ "3G-Only CPOPS", "2G-Only CPOPS", "Uncovered POPs", "Total Opportunity POPs" ], "string", axis=0) elif table_type == 3: # first line of table self.writeToMultipleCells( worksheet, "B4:I4", [ "Capex/cpop Summary", "Total", "<$10/cpop", "$10<$20/cpop", "$20<$40/cpop", "$40<$60/cpop", "$60<$80/cpop", ">$80/cpop" ], "string", axis=1) # second to thirteen line of table self.writeToMultipleCells( worksheet, "B5:B16", [ 'Total Opportunity POPs', 'Greenfield Opportunity POPs', '2G Overlay Opportunity POPs', '3G Overlay Opportunity POPs', 'Total RAN CPOPs', 'Greenfield RAN CPOPs', '2G Overlay RAN CPOPs', '3G Overlay RAN CPOPs', 'Total Sites', 'Greenfield Sites', '2G Overlay Sites', '3G Overlay Sites' ], "string", axis=0) # fourteenth to eighteenth line of table self.writeToMultipleCells( worksheet, "B17:B21", [ "Capex/cpop", "Capex/site", "Greenfield Capex/site", "2G Overlay Capex/site", "3G Overlay Capex/site" ], "string", axis=0) # ninteenth to 22th line of table self.writeToMultipleCells( worksheet, "B22:B25", [ "Total CapEx", "Total CapEx- Greenfield Sites", "Total CapEx- 2G Overlay", "Total CapEx- 3G Overlay" ], "string", axis=0)
def writeTableFrame(self, worksheet, table_type): """Write Table Frame Args: table_type: 1: Competition Analysis 2: Country TAM Analysis-PER 3: Network Analysis """ if table_type == 1: # first line of table worksheet.write_string("B5", "Operator Summary") self.mergeCellsAndWrite(worksheet, "C5:G5", "Pops") self.mergeCellsAndWrite(worksheet, "H5:L5", "Settlements") # second line of table worksheet.write_string("C6", "Total CPOPs") worksheet.write_string("D6", "4G") worksheet.write_string("E6", "3G+4G") worksheet.write_string("F6", "2G Only") worksheet.write_string("G6", "Unconnected") worksheet.write_string("H6", "Total") worksheet.write_string("I6", "4G") worksheet.write_string("J6", "3G+4G") worksheet.write_string("K6", "2G Only") worksheet.write_string("L6", "Unconnected") elif table_type == 2: # first line of table self.mergeCellsAndWrite(worksheet, "C4:D4", "PER") self.mergeCellsAndWrite(worksheet, "E4:K4", "Settlements") # second line of table worksheet.write_string("C5", "Population") worksheet.write_string("D5", "%") worksheet.write_string("E5", "Total") worksheet.write_string("F5", "5000+") worksheet.write_string("G5", "3000->5000") worksheet.write_string("H5", "1000->3000") worksheet.write_string("I5", "500->1000") worksheet.write_string("J5", "300->500") worksheet.write_string("K5", "300->5000") # third line of table worksheet.write_string("B6", "Total Count") # fourth line of table worksheet.write_string("B7", "Total Pops") # fifth line of table worksheet.write_string("B8", "Existing CPOPS") # sixth line of table worksheet.write_string("B9", "Total 4G CPOPS") # seventh line of table worksheet.write_string("B10", "Total 3G CPOPS") # eighth line of table worksheet.write_string("B11", "Total Fixed/WIFI CPOPS") # ninth line of table worksheet.set_row(12, None, None, {'collapsed': 1, 'hidden': True}) # tenth line of table worksheet.write_string("B13", "3G-Only CPOPS") # eleventh line of table worksheet.write_string("B14", "2G-Only CPOPS") # twelfth line of table worksheet.write_string("B15", "Uncovered POPs") # thirteenth line of table worksheet.write_string("B16", "Total Opportunity POPs") elif table_type == 3: # first line of table worksheet.write_string("B4", "Capex/cpop Summary") worksheet.write_string("C4", "Total") worksheet.write_string("D4", "<$10/cpop") worksheet.write_string("E4", "$10<$20/cpop") worksheet.write_string("F4", "$20<$40/cpop") worksheet.write_string("G4", "$40<$60/cpop") worksheet.write_string("H4", "$60<$80/cpop") worksheet.write_string("I4", ">$80/cpop") # second to thirteen line of table suffixes = ["Opportunity POPs", "RAN CPOPs", "Sites"] categories = ["Total", "Greenfield", "2G Overlay", "3G Overlay"] row = 5 for suffix in suffixes: for category in categories: worksheet.write_string("B{0}".format(row), "{0} {1}".format(category, suffix)) row += 1 # fourteenth to eighteenth line of table worksheet.write_string("B{}".format(row), "Capex/cpop") row += 1 worksheet.write_string("B{}".format(row), "Capex/site") row += 1 suffixes = ["Capex/site"] categories = ["Greenfield", "2G Overlay", "3G Overlay"] for suffix in suffixes: for category in categories: worksheet.write_string("B{0}".format(row), "{0} {1}".format(category, suffix)) row += 1 # ninteenth to 22th line of table worksheet.write_string("B{}".format(row), "Total CapEx") row += 1 prefixes = ["Total CapEx"] categories = ["Greenfield Sites", "2G Overlay", "3G Overlay"] for prefix in prefixes: for category in categories: worksheet.write_string("B{0}".format(row), "{0}- {1}".format(prefix, category)) row += 1
def writeTeacherGroupRow(worksheet, solution, teacher, teacherGroup, lessons, row, orm) -> int: """ Writes the needed timetable rows with all given Lessons. Args: worksheet: The worksheet to write the timetables in. solution: The timetable Solution. teacher: The teacher to write its timetable. teacherGroup: A group of Teachers that teach all of the given Lessons. lessons: The Lessons to write in the row. row: The current row in the worksheet to start with the timetables. orm: The ORM file with all objects of the timetable. Returns: The next row after the written timetables. """ columnOffset = 3 lessonsMap = { } # A dictionary with all Timeslots as keys and the corresponding Lessons as values for Lessons that matches the courseType. for timeslot in orm.getTimeslots(): lessonsAtTimeslot = list( filter(lambda l: l in lessons, solution.getLessonsAtTimeslot(timeslot))) if lessonsAtTimeslot: lessonsMap[timeslot] = lessonsAtTimeslot teacherGroup = [x for x in teacherGroup if x is not teacher] teacherGroup = [ teacher ] + teacherGroup # Put the Teacher to the front of the list. # Iterate while Lessons are left in the dictionary because there can be more than one Lesson per Timeslot and additional rows are needed for these extra Lessons. while lessonsMap: # Write teacher abbreviation to the start of the rows. worksheet.merge_range( row, 2, row + 2, 2, "/".join(list(map(lambda t: t.abbreviation, teacherGroup))), formatMap["teacherAbbreviationFormat"]) # Make borders around all lesson cells. Write a thicker border on the left side for cells in the row of the first timeslot of a day (...FirstFormat). for timeslot in orm.getTimeslots(): worksheet.write_blank( row, timeslot.id - 1 + columnOffset, None, formatMap["lessonContentTopFormat"] if timeslot.number > 1 else formatMap["lessonContentTopFirstFormat"]) worksheet.write_blank( row + 1, timeslot.id - 1 + columnOffset, None, formatMap["lessonContentMidFormat"] if timeslot.number > 1 else formatMap["lessonContentMidFirstFormat"]) worksheet.write_blank( row + 2, timeslot.id - 1 + columnOffset, None, formatMap["lessonContentBottomFormat"] if timeslot.number > 1 else formatMap["lessonContentBottomFirstFormat"]) # Write Lesson contents into the rows. for timeslot, lessonList in dict(lessonsMap).items( ): # Iterate over copy of the dictionary to delete keys from it during the iteration. lesson = lessonList.pop(0) if not lessonList: # Id list for this timeslot is empty afterwards, remove the list from the dictionary. lessonsMap.pop(timeslot) # Write Course name. worksheet.write_string( row, timeslot.id - 1 + columnOffset, lesson.course.abbreviation, formatMap["lessonContentTopFormat"] if timeslot.number > 1 else formatMap["lessonContentTopFirstFormat"]) # Write Teacher name. worksheet.write_string( row + 1, timeslot.id - 1 + columnOffset, "/".join( map(lambda sg: sg.abbreviation, lesson.course.semester_groups)), formatMap["lessonContentMidFormat"] if timeslot.number > 1 else formatMap["lessonContentMidFirstFormat"]) # Write Room name. worksheet.write_string( row + 2, timeslot.id - 1 + columnOffset, solution.getRoomOfLesson(lesson).name, formatMap["lessonContentBottomFormat"] if timeslot.number > 1 else formatMap["lessonContentBottomFirstFormat"]) row += 3 return row
def writeCourseTypeRow(worksheet, courseType, solution: Solution, semesterGroup, orm, row) -> int: """ Writes the rows for a given course type and the given semester group. Args: worksheet: The worksheet to write the timetables in. courseType: The course type. solution: The timetable Solution. semesterGroup: The semesterGroup to write the timetable of. orm: The ORM file with all objects of the timetable. row: The current row in the worksheet to start with the timetables. Returns: The next row after the written timetables. """ columnOffset = 3 lessonsMap = { } # A dictionary with all Timeslots as keys and the corresponding Lessons as values for Lessons that matches the courseType. for timeslot in orm.getTimeslots(): # Filter for lessons at the current timeslot, the given course type and semester group. lessonsAtTimeslot = list( filter( lambda l: l.course.type == courseType and semesterGroup in l.course.semester_groups, solution.getLessonsAtTimeslot(timeslot))) if lessonsAtTimeslot: lessonsMap[timeslot] = lessonsAtTimeslot # Iterate while Lessons are left in the dictionary because there can be more than one Lesson # per Timeslot and additional rows are needed for these extra Lessons. while lessonsMap: # Write SemesterGroup abbreviation and course type to the start of the rows. worksheet.write(row, 1, semesterGroup.abbreviation, formatMap["sgAbbreviationFormat"]) worksheet.write(row + 1, 1, semesterGroup.abbreviation, formatMap["sgAbbreviationFormat"]) worksheet.write(row + 2, 1, semesterGroup.abbreviation, formatMap["sgAbbreviationFormat"]) worksheet.write(row + 1, 2, courseType, formatMap["sgAbbreviationFormat"]) # Make borders around all lesson cells. Write a thicker border on the left side for cells in the row of the first timeslot of a day (...FirstFormat). for timeslot in orm.getTimeslots(): worksheet.write_blank( row, timeslot.id - 1 + columnOffset, None, formatMap["lessonContentTopFormat"] if timeslot.number > 1 else formatMap["lessonContentTopFirstFormat"]) worksheet.write_blank( row + 1, timeslot.id - 1 + columnOffset, None, formatMap["lessonContentMidFormat"] if timeslot.number > 1 else formatMap["lessonContentMidFirstFormat"]) worksheet.write_blank( row + 2, timeslot.id - 1 + columnOffset, None, formatMap["lessonContentBottomFormat"] if timeslot.number > 1 else formatMap["lessonContentBottomFirstFormat"]) # Write Lesson contents into the rows. for timeslot, lessonList in dict(lessonsMap).items( ): # Iterate over copy of the dictionary to delete keys from it during the iteration. lesson = lessonList.pop(0) if not lessonList: # Id list for this timeslot is empty afterwards, remove the list from the dictionary. lessonsMap.pop(timeslot) # Write Course name. worksheet.write_string( row, timeslot.id - 1 + columnOffset, lesson.course.abbreviation, formatMap["lessonContentTopFormat"] if timeslot.number > 1 else formatMap["lessonContentTopFirstFormat"]) # Write Teacher name. worksheet.write_string( row + 1, timeslot.id - 1 + columnOffset, "/".join(map(lambda t: t.abbreviation, lesson.teachers)), formatMap["lessonContentMidFormat"] if timeslot.number > 1 else formatMap["lessonContentMidFirstFormat"]) # Write Room name. worksheet.write_string( row + 2, timeslot.id - 1 + columnOffset, solution.getRoomOfLesson(lesson).name, formatMap["lessonContentBottomFormat"] if timeslot.number > 1 else formatMap["lessonContentBottomFirstFormat"]) row += 3 return row