Auto adjust column width for multisheet Excel

Question

I'm trying to auto adjust the column width for multi-sheet excel file and I've stumbled upon this result:

Is there a way to auto-adjust Excel column widths with pandas.ExcelWriter?

It works nicely for a single sheet excel file, but everytime I use writer.save() it writes over the multi-sheet excel file, making it a single sheet.

My Code:

excel_path = os.path.join(temp_dir, file_name)
for i, (df, sheet_name) in enumerate(zip(dataframe_list, sheet_names)):
    if i == 0:
        df.to_excel(excel_path, sheet_name=sheet_name)
    else:
        with pd.ExcelWriter(excel_path, mode='a', engine='openpyxl') as writer:
            df.to_excel(writer, sheet_name=sheet_name)

Is there a way to fix this issue?

Answer 1

I know this isn't pretty, but found a solution to this problem where I parse worksheets of xlsx file and find the max length of each column.

import os
import openpyxl
from openpyxl.worksheet.dimensions import ColumnDimension, DimensionHolder
from openpyxl.utils import get_column_letter

def excel_autoadjust_col(path, target_excel, padding):
    target_file = os.path.join(path, target_excel)
    wb = openpyxl.load_workbook(target_file)
    sheets = [sheet for sheet in wb.get_sheet_names()]

    for sheet in sheets:
        ws = wb[sheet]
        dim_holder = DimensionHolder(worksheet=ws)

        for col in range(ws.min_column, ws.max_column + 1):
            width = 0
            for row in range(ws.min_row, ws.max_row + 1):
                cell_value = ws.cell(column=col, row=row).value
                if cell_value:
                    cell_len = len(str(cell_value))
                    if cell_len > width:
                        width = cell_len + padding

            dim_holder[get_column_letter(col)] = ColumnDimension(ws, min=col, max=col, width=width)

        ws.column_dimensions = dim_holder

    wb.save(target_file)
    print("Completed adjustments for {}".format(target_excel))

this is a Frankenstein of everything I found on stackoverflow (ty community).