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.

Column widths must unfortunately be **always** be calculated. Setting `bestWidth=True` has no effect. — Charlie Clark, Feb 16 '20 at 17:52
Useful answer [here](https://stackoverflow.com/a/14450572/7232335). — Nathaniel Jones, Jun 19 '20 at 19:27
Here is the original bug ticket: https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1275 TLDR; `WONTFIX` — kevinarpe, Feb 08 '22 at 13:22

Mounesh · Answer 1 · 2022-08-17T09:25:20.530

13

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

edited Aug 17 '22 at 09:25

answered Oct 24 '21 at 16:10

Mounesh

561
5
18

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. – flywire Oct 29 '21 at 05:32
Hi Sir, Could you please elaborate on your question? I didn't get it. Didn't this solution help you? – Mounesh Nov 12 '21 at 09:36
"After many hours of research finally, I found it." Can you link the reference? – flywire Nov 13 '21 at 04:22
Sorry I didn't get the link. I will paste here If I get – Mounesh Aug 11 '22 at 10:01

score 4 · Answer 2 · answered Mar 19 '21 at 16:15

4

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

answered Mar 19 '21 at 16:15

Виктор

49
3

no difference with auto_size – Demetry Pascal Mar 20 '22 at 11:59

score 0 · Answer 3 · answered Oct 28 '22 at 07:43

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")

score -1 · Answer 4 · answered Jan 30 '21 at 23:41

-1

I used some like this:

from openpyxl.worksheet.dimensions import ColumnDimension

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

answered Jan 30 '21 at 23:41

Eduardo Garbin

1

How to set column width to bestFit in openpyxl

4 Answers4

Linked