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 writeHeaderLine(worksheet, department, semesterName, subjectName, col1Width, col2Width, col3Width, orm: ORM, row) -> int: """ Writes the worksheet header with creation date and department name. Also sets the width of the first three columns. Args: worksheet: The worksheet to write the header in. department: The name of the timetable's department. semesterName: The name of the semester. subjectName: The subject of the worksheet's timetable. e.g. Teachers or Students. col1Width: Width of the first column. col2Width: Width of the second column. col3Width: Width of the third column. orm: The ORM file with all objects of the timetable. row: The last row in the worksheet. Returns: The next row after the written timetables. """ columnCount = len(orm.getTimeslots()) + 3 # Write row with the current date. worksheet.write(row, 0, "Stand:", formatMap["simpleBoldFormat"]) worksheet.write_datetime(row, 1, date.today(), formatMap["simpleBoldGermanDateFormat"]) row += 1 # Main header line. # Add a background to the header row. makeBackground(worksheet, columnCount, row, formatMap["mainHeaderFormat"]) # Set column widths. worksheet.set_column(0, 0, col1Width) worksheet.set_column(1, 1, col2Width) worksheet.set_column(2, 2, col3Width) worksheet.set_column( 3, columnCount, 7) # Width of the columns that contain the timetable Lessons. # Write main header contents. worksheet.write(row, 0, "Stundenplanung", formatMap["mainHeaderFormat"]) worksheet.write(row, 3, "Fachbereich " + department, formatMap["mainHeaderFormat"]) worksheet.write(row, 8, subjectName, formatMap["mainHeaderFormat"]) # Set row height. worksheet.set_row(row, 27.75) row += 1 # Weekday and timeslot line. row = writeDayAndTimeLine(worksheet, semesterName, orm, row) return row
def add_sheet(self, workbook, sheet: str, data: list): worksheet = workbook.add_worksheet(name=sheet) worksheet.autofilter(0, 0, len(data), len(data[0]) - 1) for index in range(len(data[0])): worksheet.set_column( index, index, width=self.get_column_width(data, index), ) bold = workbook.add_format({"bold": 1}) worksheet.set_row(0, cell_format=bold) self.write_rows(worksheet, data)
def writeTeachersTimeTable(timeTableSolution, workbook, departmentName, semesterName, orm): """ Creates a worksheet to the workbook and writes the timetable for all Teachers in the Solution. Args: timeTableSolution: The timetable Solution workbook: The XlsxWriter Workbook. departmentName: Name of the university department of the timetable. semesterName: Name of the semester, e.g. WS 2018-19 orm: The ORM file with all objects of the timetable. """ # Create a new worksheet. worksheet = workbook.add_worksheet("Lehrende") row = 0 # Row counter. row = writeHeaderLine(worksheet, departmentName, semesterName, "Lehrende", 18, 18, 8, orm, row) worksheet.freeze_panes(row, 0) teachers = orm.getTeachers() teachers.sort(key=lambda t: t.name ) # The Teacher's should be occur in alphabetical order. for teacher in teachers: # Add a black row for separating the teachers. for column in range(len(orm.getTimeslots()) + 3): worksheet.write_blank(row, column, "", formatMap["blackBackgroundFormat"]) worksheet.set_row(row, 2) row += 1 # Write the Teacher's rows. row = writeTeacherRows(worksheet, timeTableSolution, teacher, row, orm) # Add a black row after the last teacher. for column in range(len(orm.getTimeslots()) + 3): worksheet.write_blank(row, column, "", formatMap["blackBackgroundFormat"]) worksheet.set_row(row, 2)
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