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