def find_or_create_row(self, rls_date: datetime.date, ws: Worksheet):
row = 1
while True:
row += 1
date = ws.cell(row, 1).value
if date == rls_date:
return row
if not date or date > rls_date:
ws.insert_rows(row)
ws.cell(row, 1, rls_date)
return row
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
