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)