How to set column width to bestFit in openpyxl

Question

I have filled a worksheet with some data and I'm trying to make column widths to assume their best fit, as in here. Basically the kind of autofit that happens when you double-click the column width adjustment separator.

This is my minimal example, which, as far as my understanding of openpyxl documentation goes, should work:

import openpyxl
from typing import NoReturn

def columns_best_fit(ws: openpyxl.worksheet.worksheet.Worksheet) -> NoReturn:
    """
    Make all columns best fit
    """
    column_letters = tuple(openpyxl.utils.get_column_letter(col_number + 1) for col_number in range(ws.max_column))
    for column_letter in column_letters:
        dim = openpyxl.worksheet.dimensions.ColumnDimension(ws, index=column_letter, bestFit=True, customWidth=True)
        ws.column_dimensions[column_letter] = dim


wb = openpyxl.Workbook()
ws = wb.active
ws.append(("Long Column Header 1", "Even Longer Column Header 2"))
ws.append(("some data", "more data"))
columns_best_fit(ws)
wb.save("column_width_test.xlsx")

However, when I open the resulting file, the columns are just slightly wider, but certainly not best fit.

Answer 1

After many hours of research finally, I found it.

NOTE : In the below code, sheet is the worksheet name. Usually in the documentation, we can see it as ws. Please don't forget to change the worksheet name.

# Imorting the necessary modules
try:
        from openpyxl.cell import get_column_letter
except ImportError:
        from openpyxl.utils import get_column_letter
        from openpyxl.utils import column_index_from_string
from openpyxl import load_workbook
import openpyxl
from openpyxl import Workbook



for column_cells in sheet.columns:
    new_column_length = max(len(str(cell.value)) for cell in column_cells)
    new_column_letter = (get_column_letter(column_cells[0].column))
    if new_column_length > 0:
        sheet.column_dimensions[new_column_letter].width = new_column_length*1.23

UPDATE : This code doesn't work for all, but don't hesitate to try it..