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