def adjust_column_and_row_sizes(worksheet: Worksheet, rows: List[Tuple[str,
...]],
num_columns: int) -> None:
"""
Rows height:
- Title row is thicker than data row
- blank separator row is thinner than data row
Columns width:
- Each column width is wider enough all the text in cells it contains
:param worksheet: worksheet we operate on
:param rows: list of tuples where each tuple represents a row in worksheet
:param num_columns: number of columns in worksheet
:return:
"""
num_rows = len(rows)
# adjust row sizes
column_max_sizes = [0] * num_columns
worksheet.set_row(0, 30)
for i in range(num_rows):
if rows[i][0] != " ":
worksheet.set_row(i + 1, 20)
else:
worksheet.set_row(i + 1, 10)
for j in range(num_columns):
column_max_sizes[j] = max(column_max_sizes[j], len(rows[i][j]) + 5)
# adjust column sizes
for j in range(num_columns):
worksheet.set_column(j, j, column_max_sizes[j])
worksheet.set_column(num_columns, num_columns, 20)
def set_column_autowidth(worksheet: Worksheet, column: int):
"""
Set the width automatically on a column in the `Worksheet`.
!!! Make sure you run this function AFTER having all cells filled in
the worksheet!
"""
maxwidth = get_column_width(worksheet=worksheet, column=column)
if maxwidth is None:
return
worksheet.set_column(first_col=column, last_col=column, width=maxwidth)
def _set_columns_width(workbook: Workbook, worksheet: Worksheet, columns,
fields: List[str]):
cell_format_text_wrap = workbook.add_format()
cell_format_text_wrap.set_text_wrap()
for idx, field in enumerate(fields, start=0):
if columns[field][MAX_WIDTH_KEY] > MAX_WIDTH:
worksheet.set_column(idx, idx, MAX_WIDTH,
cell_format_text_wrap)
else:
worksheet.set_column(idx, idx, columns[field][MAX_WIDTH_KEY])
def adjust_columns_width(cls,
df: pd.DataFrame,
wb: Workbook,
worksheet: Worksheet):
# wrap_format = wb.add_format({'shrink': True})
for idx, col in enumerate(df):
series = df[col]
max_len = max((
series.astype(str).map(len).max(), # len of largest item
len(str(series.name)) # len of column name/header
)) + 1 # adding a little extra space
max_len = min(max_len, cls.MAX_COL_WIDTH)
worksheet.set_column(idx, idx, max_len)
def _static_upper_part(self, worksheet: Worksheet):
# set widths
worksheet.set_column('A:A', 9)
worksheet.set_column('B:B', 36)
worksheet.set_column('C:C', 18)
worksheet.set_column('D:D', 18)
worksheet.write('A1', 'Name des Betriebs', self.fonts.bg)
worksheet.write('D1', 'Musterstrasse 1', self.fonts.rechts)
worksheet.write('D2', 'PLZ und Ort', self.fonts.rechts)
worksheet.write('D3', 'E-Mail: [email protected]', self.fonts.rechtsd)
worksheet.write('A3', 'Telefon: 012 345 67 89', self.fonts.dd)
worksheet.write('B3', '', self.fonts.dd)
worksheet.write('C3', '', self.fonts.dd)
def _adjust_each_column_width(rows: list, worksheet: Worksheet,
excel_longest_word: int) -> None:
if rows:
for i, v in enumerate(rows[0]):
worksheet.set_column(i, i + 1, excel_longest_word + 1)
