def merge_region(sheet: Worksheet, min_row, max_row, min_col, max_col):
assert max_row >= min_row > 0 and max_col >= min_col > 0
merged_region = (
f"{get_column_letter(min_col)}{min_row}:{get_column_letter(max_col)}{max_row}"
)
sheet.merge_cells(merged_region)
def _insert_sheet_title(self, sheet: Worksheet, month_name_and_year: str):
current_row = str(self.__CURRENT_ROW)
start_cell = self.__LEFT_COLUMNS[0] + str(current_row)
end_cell = self.__RIGHT_COLUMNS[3] + str(current_row)
sheet[start_cell] = self.language_pack.meeting_name + ' – ' + month_name_and_year
sheet.merge_cells(start_cell + ':' + end_cell)
self.__CURRENT_ROW += 2
self._style_sheet_title(sheet)
def add_headers_and_title(sheet: Worksheet):
headers = ('序号', '考试日期', '考试时间', '考试名称', '开课院系', '考试地点', '班级', '考生人数',
'考生院系')
for index, header in enumerate(headers):
sheet.cell(2, index + 1, header)
title = '东北大学考试日程表'
sheet.merge_cells(start_row=1, end_row=1, start_column=1, end_column=9)
cell = sheet['A1']
cell.value = title
cell.alignment = Alignment(horizontal="center", vertical="center")
def insert_heading(self, ws: Worksheet):
# This can only be run after the table has been populated and styled
# given it prepends rows to the sheet. Ugh.
ws.insert_rows(0)
ws["A1"] = ws.title
ws.merge_cells("A1:{}1".format(get_column_letter(ws.max_column)))
# Style merged cells using the top left cell reference
ws["A1"].style = "Headline 1"
# alignment is overwritten by style, so set it afterwards
ws["A1"].alignment = Alignment(horizontal="center", vertical="center")
no_border = Side(border_style=None)
ws["A1"].border = Border(
left=no_border, right=no_border, top=no_border, outline=False
)
def write_variants_sheet(sheet: Worksheet, df_variants: pd.DataFrame):
"""
Write and format variants sheet in workbook
"""
# write data
for r in dataframe_to_rows(df_variants, header=True, index=False):
sheet.append(r)
sheet.insert_rows(1)
# apply conditional filling depending of variant presence
for row in sheet.iter_rows(
min_col=8,
max_col=sheet.max_column,
min_row=3,
max_row=sheet.max_row,
):
for cell in row:
if cell.value == 1:
cell.fill = PatternFill(start_color="3bbf97",
fill_type="solid")
cell.value = ""
# improve style (common columns)
column_widths = [8, 8, 8, 18, 10, 14, 14]
for col, w in zip(sheet.iter_cols(max_row=2, max_col=7), column_widths):
colname = col[0].column_letter
col[0].value = col[1].value
col[0].font = Font(name="Calibri", bold=True)
col[0].border = Border(
bottom=Side(border_style="medium", color="000000"))
col[0].alignment = Alignment(horizontal="center")
sheet.column_dimensions[colname].width = w
sheet.merge_cells(f"{colname}1:{colname}2")
# improve style (samples columns)
sheet.row_dimensions[2].height = 60
sheet["H1"].value = "samples"
sheet["H1"].font = Font(name="Calibri", bold=True)
sheet["H1"].alignment = Alignment(horizontal="center")
sheet.merge_cells(start_row=1,
end_row=1,
start_column=8,
end_column=sheet.max_column)
for col in sheet.iter_cols(min_row=2, max_row=2, min_col=8):
col[0].font = Font(name="Calibri", bold=True)
col[0].border = Border(
bottom=Side(border_style="medium", color="000000"))
col[0].alignment = Alignment(horizontal="center", text_rotation=90)
sheet.column_dimensions[col[0].column_letter].width = 3
def _insert_section_title(self, meeting_section: MeetingSection, sheet: Worksheet):
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[0] + current_row
sheet[start_cell] = meeting_section.title
# by default the right most cell at which merging cells ends is on the 5th column (including the offset)
end_cell = self.__ACTIVE_COLUMNS[3] + current_row
# but if the section is 'IMPROVE_IN_MINISTRY' and the user requests hall-dividing
# rows then it changes to the 3rd row
if (meeting_section.section_kind == SectionKind.IMPROVE_IN_MINISTRY) and \
self.excel_config.INSERT_HALL_DIVISION_LABELS:
end_cell = self.__ACTIVE_COLUMNS[1] + current_row
self._insert_hall_divider(sheet, meeting_section.section_kind)
sheet.merge_cells(start_cell + ':' + end_cell)
self._style_section_title(self.__CURRENT_ROW, sheet, meeting_section.section_kind)
self.__CURRENT_ROW += 1
def merge_cells(sheet: Worksheet, beg_row: int, end_row: int, beg_col: int,
end_col: int) -> None:
"""
merge cells by index
:param sheet: sheet object
:param beg_row: begin index row
:param end_row: end index row
:param beg_col: begin index column
:param end_col: end index column
:return:
"""
sheet.merge_cells(start_row=beg_row + 1,
end_row=end_row + 1,
start_column=beg_col + 1,
end_column=end_col + 1)
return
def _generate_cells(self, sheet: Worksheet) -> CellGenerator:
for row_index, row in enumerate(self.parser.rows):
col_index = 0
for html_cell in row.find("td"):
target_cell, col_index = self._find_free_cell(
col_index, row_index, sheet)
colspan = int(html_cell.attrs.get("colspan", 1))
rowspan = int(html_cell.attrs.get("rowspan", 1))
if colspan > 1 or rowspan > 1:
cell_range_str = create_cell_range_str(
col_index, colspan, row_index, rowspan)
sheet.merge_cells(cell_range_str)
yield html_cell, None, sheet[cell_range_str]
else:
yield html_cell, target_cell, None
col_index += colspan
def _insert_header_content(self, week_span: str, sheet: Worksheet):
# week span
week_span_row = self.__CURRENT_ROW
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[0] + current_row
end_cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[start_cell] = re.sub('[-|–]', ' – ', week_span)
sheet.merge_cells(start_cell + ':' + end_cell)
self.__CURRENT_ROW += 1
# chairman
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[0] + current_row
end_cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[start_cell] = self.language_pack.chairman
sheet.merge_cells(start_cell + ':' + end_cell)
self.__CURRENT_ROW += 1
# opening prayer
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[start_cell] = self.language_pack.opening_prayer
self.__CURRENT_ROW += 1
self._style_header_content(week_span_row, sheet)
def _write_match_to_sheet(sheet: Worksheet, match_data: MatchData):
assert match_data.round_time != 0 and match_data.timeout_time != 0 and match_data.suspend_time != 0
no_players = len(
match_data.rounds[0].team1.players
) # no. players of first team to know the height of the table
# Merge cells
sheet.merge_cells(start_row=12 + no_players,
start_column=1,
end_row=13 + no_players,
end_column=1)
# Insert data
sheet.cell(12 + no_players, 1, "Round")
sheet.cell(12 + no_players, 2, "Time")
sheet.cell(12 + no_players, 3, "Sus. time")
sheet.cell(12 + no_players, 4, "TO time")
sheet.cell(13 + no_players, 2, match_data.round_time)
sheet.cell(13 + no_players, 3, match_data.suspend_time)
sheet.cell(13 + no_players, 4, match_data.timeout_time)
# Apply styling
for row in range(12 + no_players, 14 + no_players):
for col in range(1, 5):
sheet.cell(row, col).alignment = ALIGNMENT
sheet.cell(12 + no_players, 1).border = BORDER_TOP_LEFT
sheet.cell(12 + no_players, 2).border = BORDER_TOP
sheet.cell(12 + no_players, 3).border = BORDER_TOP
sheet.cell(12 + no_players, 4).border = BORDER_TOP_RIGHT
sheet.cell(13 + no_players, 4).border = BORDER_BOTTOM_RIGHT
sheet.cell(13 + no_players, 3).border = BORDER_BOTTOM
sheet.cell(13 + no_players, 2).border = BORDER_BOTTOM
sheet.cell(13 + no_players, 1).border = BORDER_BOTTOM_LEFT
def _insert_section(self, meeting_section: MeetingSection, sheet: Worksheet):
self._insert_section_title(meeting_section, sheet)
bible_reading = self.language_pack.bible_reading
for presentation in meeting_section.presentations:
if meeting_section.section_kind == SectionKind.TREASURES:
if (bible_reading in presentation) and self.excel_config.INSERT_HALL_DIVISION_LABELS:
self._insert_hall_divider(sheet, meeting_section.section_kind)
self.__CURRENT_ROW += 1
current_row = str(self.__CURRENT_ROW)
start_cell = self.__ACTIVE_COLUMNS[1] + current_row
if (meeting_section.section_kind == SectionKind.IMPROVE_IN_MINISTRY) and\
self.excel_config.INSERT_HALL_DIVISION_LABELS:
end_cell = self.__ACTIVE_COLUMNS[1] + current_row
else:
end_cell = self.__ACTIVE_COLUMNS[2] + current_row
sheet[start_cell] = presentation
if (bible_reading not in presentation) or \
(bible_reading in presentation and not self.excel_config.INSERT_HALL_DIVISION_LABELS):
sheet.merge_cells(start_cell + ':' + end_cell)
self._style_section(self.__CURRENT_ROW, sheet)
self.__CURRENT_ROW += 1
def save_to_sheet(campus: Campus, sheet: Worksheet):
add_headers_and_title(sheet)
adjust_column_style(sheet)
start_pointer = 3
end_pointer = 3
dates = list(campus.dates.keys())
dates.sort()
for date in dates:
date_obj = campus.dates[date]
sessions = list(date_obj.sessions.keys())
sessions.sort()
for session in sessions:
session_obj = date_obj.sessions[session]
for course, course_obj in session_obj.courses.items():
places = list(course_obj.places.values())
places.sort()
place_pointer = start_pointer
student_college_pointer = start_pointer
temp_student_college = None
for place in places:
place_obj = course_obj.places[place.name]
array = list(place_obj.clazzes.values())
array.sort()
for clazz_obj in array:
if clazz_obj.name == "重修":
total_amount = 0
finial_college_name = []
for student_college_obj in clazz_obj.student_colleges.values(
):
finial_college_name.append(
student_college_obj.name)
total_amount += student_college_obj.student_amount
temp_student_college = '+'.join(
finial_college_name) + '学院'
insert_row(sheet, end_pointer, date, session,
course, course_obj.college, place.name,
clazz_obj.name, total_amount,
temp_student_college)
student_college_pointer = end_pointer
end_pointer += 1
else:
for student_college_obj in clazz_obj.student_colleges.values(
):
insert_row(sheet, end_pointer, date, session,
course, course_obj.college,
place.name, clazz_obj.name,
student_college_obj.student_amount,
student_college_obj.name)
if temp_student_college is None:
temp_student_college = student_college_obj.name
elif temp_student_college != clazz_obj.student_colleges:
sheet.merge_cells(
start_column=9,
end_column=9,
start_row=student_college_pointer,
end_row=end_pointer - 1)
temp_student_college = student_college_obj.name
student_college_pointer = end_pointer
end_pointer += 1
if place_pointer != end_pointer - 1:
sheet.merge_cells(start_row=place_pointer,
end_row=end_pointer - 1,
start_column=6,
end_column=6)
place_pointer = end_pointer
sheet.merge_cells(start_row=start_pointer,
end_row=end_pointer - 1,
start_column=5,
end_column=5)
sheet.merge_cells(start_row=start_pointer,
end_row=end_pointer - 1,
start_column=4,
end_column=4)
sheet.merge_cells(start_row=start_pointer,
end_row=end_pointer - 1,
start_column=3,
end_column=3)
if student_college_pointer < end_pointer - 1:
sheet.merge_cells(start_row=student_college_pointer,
end_row=end_pointer - 1,
start_column=9,
end_column=9)
start_pointer = end_pointer
def __writeBDBRMatrix(sheet: Worksheet,
data_refs: SummaryRefType,
order: List[str] = None,
*,
write_bdbr: bool,
write_bits: bool,
write_psnr: bool,
write_time: bool,
**other: dict):
from .TestSuite import _PSNR, _KBS, _KB, _TIME
seq_ref = __flip_dict(
data_refs) # transform data_refs to seq_ref[<seq>][<test_name>] order
order = order if order else list(seq_ref.keys())
#print(seq_ref)
# For each sequence generate the comparison matrix
sheet.cell(row=1, column=1).value = __S_HEADER
#for (seq,ref) in sorted(seq_ref.items()):
for seq in order:
ref = seq_ref[seq]
tests = sorted(ref.keys())
row = sheet.max_row + 2
brow = row
prow = row
trow = row
col = 1 #sheet.max_column + 1
sheet.cell(row=row, column=col).value = __S_SEQ_HEADER.format(
seq) #Write sequence header
# write bdrate matrix
if write_bdbr:
sheet.merge_cells(start_column=col,
start_row=row,
end_column=col + len(tests),
end_row=row)
(row, col) = __writeSummaryMatrixHeader(sheet, tests, row + 1, col)
__writeSummaryDataMatrix(sheet,
ref,
row,
col,
data_func=lambda data, test: data[test][
_KBS] + data[test][_PSNR],
data_format=__S_BDRATE_FORMAT,
number_format='0.00%',
color_scale_rule=ColorScaleRule(
start_type='percentile',
start_value=90,
start_color='63BE7B',
mid_type='num',
mid_value=0,
mid_color='FFFFFF',
end_type='percentile',
end_value=10,
end_color='F8696B'))
# write bit matrix
if write_bits:
if 'bcol' not in locals():
bcol = sheet.max_column + 2
sheet.cell(row=brow, column=bcol).value = __S_BIT_HEADER
sheet.merge_cells(start_column=bcol,
start_row=brow,
end_column=bcol + len(tests),
end_row=brow)
(brow, col) = __writeSummaryMatrixHeader(sheet, tests, brow + 1,
bcol)
__writeSummaryDataMatrix(
sheet,
ref,
brow,
colb,
data_func=lambda data, test: data[test][_KB],
data_format=__S_BIT_FORMAT,
color_scale_rule=ColorScaleRule(start_type='min',
start_color='4F81BD',
mid_type='num',
mid_value=1,
mid_color='FFFFFF',
end_type='percentile',
end_value=80,
end_color='F8696B'))
# write psnr matrix
if write_psnr:
if 'pcol' not in locals():
pcol = sheet.max_column + 2
sheet.cell(row=prow, column=pcol).value = __S_PSNR_HEADER
sheet.merge_cells(start_column=pcol,
start_row=prow,
end_column=pcol + len(tests),
end_row=prow)
(prow, col) = __writeSummaryMatrixHeader(sheet, tests, prow + 1,
pcol)
__writeSummaryDataMatrix(
sheet,
ref,
prow,
colb,
data_func=lambda data, test: data[test][_PSNR],
data_format=__S_PSNR_FORMAT,
number_style='Comma',
def_val=0,
color_scale_rule=ColorScaleRule(start_type='percentile',
start_value=90,
start_color='63BE7B',
mid_type='num',
mid_value=0,
mid_color='FFFFFF',
end_type='percentile',
end_value=10,
end_color='F8696B'))
# write time matrix
if write_time:
if 'tcol' not in locals():
tcol = sheet.max_column + 2
sheet.cell(row=trow, column=tcol).value = __S_TIME_HEADER
sheet.merge_cells(start_column=tcol,
start_row=trow,
end_column=tcol + len(tests),
end_row=trow)
(trow, col) = __writeSummaryMatrixHeader(sheet, tests, trow + 1,
tcol)
__writeSummaryDataMatrix(
sheet,
ref,
trow,
col,
data_func=lambda data, test: data[test][_TIME],
data_format=__S_TIME_FORMAT,
color_scale_rule=ColorScaleRule(
start_type='min',
start_color='9BDE55', #'63BE7B',
mid_type='num',
mid_value=1,
mid_color='FFFFFF',
end_type='percentile',
end_value=80,
end_color='00BBEF'))
# Make columns wider
for col in range(sheet.max_column):
sheet.column_dimensions[get_column_letter(col +
1)].width = getMaxLength(
list(data_refs.keys()))
def _write_team_to_sheet(sheet: Worksheet, match_data: MatchData, team: int):
no_rounds = len(
match_data.rounds) # no. rounds to know the width of the table
no_players = len(match_data.rounds[0].team1.players) if team == 1 else \
len(match_data.rounds[0].team2.players) # no. players to know the height of the table
# Merge top cells first table, but not rounds
sheet.merge_cells(start_row=2,
start_column=1,
end_row=2,
end_column=2 + no_rounds * 2) # end col - 1
sheet.merge_cells(start_row=3, start_column=1, end_row=4, end_column=1)
sheet.merge_cells(start_row=3, start_column=2, end_row=4, end_column=2)
for col in range(1, 3 + len(match_data.rounds) * 2):
sheet.merge_cells(start_row=5,
start_column=col,
end_row=6,
end_column=col)
# Merge top cells second table, but not rounds
sheet.merge_cells(start_row=8,
start_column=1,
end_row=8,
end_column=2 + no_rounds * 2) # end col - 1
sheet.merge_cells(start_row=9, start_column=1, end_row=10, end_column=1)
sheet.merge_cells(start_row=9, start_column=2, end_row=10, end_column=2)
# For every match round merge the cells and enter the data
match_round: RoundData
for r, match_round in enumerate(match_data.rounds): # r starts from 0
round_team_score: int = match_data.get_team_round_score(team, r + 1)
# Merge the corresponding cells
sheet.merge_cells(start_row=4,
start_column=3 + r * 2,
end_row=4,
end_column=4 + r * 2)
sheet.merge_cells(start_row=10,
start_column=3 + r * 2,
end_row=10,
end_column=4 + r * 2)
# First table
sheet.cell(4, 3 + r * 2, "Round " + str(r + 1))
sheet.cell(5, 3 + r * 2, round_team_score)
if team == 1:
sheet.cell(5, 4 + r * 2, match_round.team1.time_out_requests)
else:
sheet.cell(5, 4 + r * 2, match_round.team2.time_out_requests)
# Second table
sheet.cell(10, 3 + r * 2, "Round " + str(r + 1))
for i, player in enumerate(match_round.team1.players) if team == 1 else \
enumerate(match_round.team2.players):
if not player.disqualified:
sheet.cell(11 + i, 3 + r * 2, player.scores)
sheet.cell(11 + i, 4 + r * 2,
f"{player.yellow_cards}, {player.red_cards}")
else:
sheet.cell(11 + i, 3 + r * 2, "n/a")
sheet.cell(11 + i, 4 + r * 2, "n/a")
# Insert the rest
sheet.cell(3, 3 + r * 2, "Score")
sheet.cell(3, 4 + r * 2, "TO reqs")
sheet.cell(9, 3 + r * 2, "Scores")
sheet.cell(9, 4 + r * 2, "Cards")
# Insert the rest of the data
sheet.cell(2, 1, "Team")
sheet.cell(3, 1, "Name")
sheet.cell(3, 1, "Name")
sheet.cell(3, 2, "NO. players")
if team == 1:
sheet.cell(
5, 1, match_data.rounds[0].team1.name
) # doesn't matter which round, because it should stay constant
sheet.cell(5, 2, str(len(match_data.rounds[0].team1.players)))
else:
sheet.cell(5, 1, match_data.rounds[0].team2.name)
sheet.cell(5, 2, str(len(match_data.rounds[0].team2.players)))
sheet.cell(8, 1, "Players")
sheet.cell(9, 1, "Number")
sheet.cell(9, 2, "Name")
if team == 1:
for i, player in enumerate(match_data.rounds[0].team1.players):
sheet.cell(11 + i, 1, "{:02d}".format(player.number))
sheet.cell(11 + i, 2, player.name)
else:
for i, player in enumerate(match_data.rounds[0].team2.players):
sheet.cell(11 + i, 1, "{:02d}".format(player.number))
sheet.cell(11 + i, 2, player.name)
# Apply styling
##################################################
# Center all cells
for row in range(2, 11 + no_players):
for col in range(1, 3 + no_rounds * 2):
sheet.cell(row, col).alignment = ALIGNMENT
# Bold font to first table
for row in range(2, 5):
for col in range(1, 3 + no_rounds * 2):
sheet.cell(row, col).font = BOLD_FONT
# Bold font to second table
for row in range(8, 11):
for col in range(1, 3 + no_rounds * 2):
sheet.cell(row, col).font = BOLD_FONT
# Set a larger width for columns A and B
sheet.column_dimensions["A"].width = 15
sheet.column_dimensions["B"].width = 15
# Add borders
sheet.cell(2, 1).border = BORDER_TOP_LEFT
sheet.cell(2, 2 + no_rounds * 2).border = BORDER_RIGHT
sheet.cell(3, 2 + no_rounds * 2).border = BORDER_RIGHT
sheet.cell(4, 2 + no_rounds * 2).border = BORDER_RIGHT
sheet.cell(5, 2 + no_rounds * 2).border = BORDER_RIGHT
for col in range(1, 3 + no_rounds * 2):
sheet.cell(6, col).border = BORDER_BOTTOM
sheet.cell(3, 1).border = BORDER_LEFT
sheet.cell(5, 1).border = BORDER_LEFT
sheet.cell(8, 1).border = BORDER_TOP_LEFT
sheet.cell(8, 2 + no_rounds * 2).border = BORDER_RIGHT
sheet.cell(9, 2 + no_rounds * 2).border = BORDER_RIGHT
sheet.cell(10, 2 + no_rounds * 2).border = BORDER_RIGHT
for row in range(11, 10 + no_players):
sheet.cell(row, 2 + no_rounds * 2).border = BORDER_RIGHT
sheet.cell(10 + no_players, 2 + no_rounds * 2).border = BORDER_BOTTOM_RIGHT
for col in range(2, 2 + no_rounds * 2):
sheet.cell(10 + no_players, col).border = BORDER_BOTTOM
sheet.cell(10 + no_players, 1).border = BORDER_BOTTOM_LEFT
sheet.cell(9, 1).border = BORDER_LEFT
for row in range(11, 10 + no_players):
sheet.cell(row, 1).border = BORDER_LEFT