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 ws_to_dict(ws: Worksheet):
wsdict = {}
for citer in ws.iter_cols():
v = citer[3].value
name = f'{v}'
if name != 'None':
wsdict[name] = [f'{e.value}' for e in citer[4:]]
print(name, wsdict[name])
return wsdict
def headers_writer(self, ws: Worksheet, columns: ColumnPattern):
"""Writes the headers from a columns ref table to a worksheet.
:param Worksheet ws: worksheet into write headers
:param ColumnPattern columns: column table
"""
# text
for k, v in columns.items():
if v.letter is None:
continue
ws["{}1".format(v.letter)] = self.tr.get(k, "Missing translation")
# styling
for row_cols in ws.iter_cols(min_row=1, max_row=1):
for cell in row_cols:
cell.style = "Headline 2"
def _get_measurements(sheet: Worksheet) -> list[tuple[int, ...]]:
"""Extract the measurements from the spreadsheet.
:param sheet: The spreadsheet to read the locations from.
:return: A list of tuples, with each tuple containing measurements for a date.
"""
col_iter = sheet.iter_cols(
min_row=FIRST_LOCATION_ROW,
max_row=LAST_LOCATION_ROW,
min_col=column_index_from_string(FIRST_DATE_COL),
max_col=sheet.max_column,
values_only=True,
)
measurements = []
for col in col_iter:
if not col[0]:
continue
measurements.append(tuple(
_cleanse_measurement(value) for value in col))
return measurements
