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.

Bethanybethe

Column widths must unfortunately be always be calculated. Setting bestWidth=True has no effect. – Janeth

Useful answer here. – Godewyn

Here is the original bug ticket: foss.heptapod.net/openpyxl/openpyxl/-/issues/1275 TLDR; WONTFIX – Drain

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 😆

Opinionative

How about linking the reference you found? Auto-adjust column width is normally fine with fixed-width fonts but poor with proportional fonts, especially if zoom is applied to the sheet. – Geese

Hi Sir, Could you please elaborate on your question? I didn't get it. Didn't this solution help you? – Opinionative

"After many hours of research finally, I found it." Can you link the reference? – Geese

Sorry I didn't get the link. I will paste here If I get – Opinionative

Answer 2

I have edited your function. It should work now.

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:
            ws.column_dimensions[column_letter].bestFit = True

Gravure

no difference with auto_size – Safari

Answer 3

Referring to @Mounesh answer, it seems like the workbook isn't saved after setting the column width. This would work.

# 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


workbook = load_workbook("excel_name.xlsx")
for sheet_name in workbook.sheetnames:
  for column_cells in workbook[sheet_name].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:
            workbook[sheet_name].column_dimensions[new_column_letter].width = new_column_length*1.23
workbook.save("excel_name.xlsx")

Burtie

Answer 4

This achieves the closest to what I wish for.
"bestfit" appears to be nonfunctional.
Excel Version 2308, Build 16731.20716

The code is copied from somewhere, can't remember where I found it.
As I open the result .xlsx - I still can do CTRL-A, CTRL-A and doubleclick a column border to get a better fit.
(e.g. One of the columns goes from 149 to 94 pixels)

    import openpyxl
    wb=openpyxl.Workbook()
    ws=wb.active
    
    # ... create info in ws (worksheet)
    
    for column_cells in ws.columns:
      new_column_length = max(len(str(cell.value)) for cell in column_cells)
      new_column_letter = (chr(64+(column_cells[0].column)))
      if new_column_length > 0:
        ws.column_dimensions[new_column_letter].width = new_column_length # *1.10

Auscultate

Answer 5

I used some like this:

from openpyxl.worksheet.dimensions import ColumnDimension

wb = Workbook()
ws = wb.active
ColumnDimension(ws, bestFit=True)

Expose