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