Python Doc & Data Automation

Fix openpyxl Column Width Not Applied

The width is set, the file saves, and Excel shows the default 8.43 characters everywhere:

>>> sheet.column_dimensions[2].width = 30      # nothing happens
>>> sheet.column_dimensions["B"].width = 30    # works
>>> worksheet.columns[1].width = 30
AttributeError: 'tuple' object has no attribute 'width'

Or the width applies and is visibly wrong — a column asked for 30 comes out about four times wider than a 30-pixel guess would suggest, because the unit is not pixels.

Root Cause

column_dimensions is a dictionary keyed by column letter, not by index. Assigning to column_dimensions[2] creates an entry under the integer key 2, which Excel never looks at, and no error is raised because the mapping accepts any key.

The unit is the second surprise. Excel measures column width in characters of the default font at the default size — roughly 7 pixels per unit at 11 pt Calibri. A width of 30 is about 30 characters wide, or 215 pixels, not 30 pixels.

Two related failures complete the set. Excel has no stored autofit for a column written by a library, so a column keeps whatever width it was given regardless of content. And a width set on a worksheet object fetched before pandas wrote to it is lost, because pandas replaces the sheet.

Addressing a column, and what the width unit means On the left, three addressing attempts. Indexing column dimensions by the integer two silently creates an unused entry. Indexing worksheet columns by position returns a tuple of cells with no width attribute. Indexing column dimensions by the letter B is the only form Excel reads. On the right, the width unit is shown as characters of the default font, so a width of thirty is about two hundred and fifteen pixels, not thirty. Addressing the column What "width = 30" means column_dimensions[2].width = 30 no error, no effect — integer key is never read worksheet.columns[1].width = 30 AttributeError — columns yields tuples of cells column_dimensions["B"].width = 30 the only form Excel honours 8.43 = the default width 1 unit ≈ one character at 11pt 30 units ≈ 215 pixels Changing the default font changes every width the unit is relative, not absolute Widths are advisory in the file — Excel applies them verbatim and never recalculates

Minimal Diagnostic

Read the saved file back and print what each column actually carries.

# pip install "openpyxl>=3.1,<4"
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

XLSX = Path("out/report.xlsx")

def width_report(xlsx_path: Path, sheet: str | None = None) -> None:
    """Show the stored width for each populated column, and the widest value in it."""
    if not xlsx_path.exists():
        raise FileNotFoundError(f"No such workbook: {xlsx_path}")
    try:
        book = load_workbook(xlsx_path)          # not read_only: dimensions are needed
    except Exception as exc:
        raise RuntimeError(f"Could not open {xlsx_path}: {exc}") from exc

    worksheet = book[sheet] if sheet else book.active
    for index in range(1, worksheet.max_column + 1):
        letter = get_column_letter(index)
        dimension = worksheet.column_dimensions.get(letter)
        stored = getattr(dimension, "width", None)
        longest = max(
            (len(str(cell.value)) for (cell,) in
             worksheet.iter_rows(min_col=index, max_col=index) if cell.value is not None),
            default=0,
        )
        header = worksheet.cell(row=1, column=index).value
        print(f"{letter}: width={stored!s:<8} longest_value={longest:<4} header={header!r}")
    book.close()

if __name__ == "__main__":
    width_report(XLSX)

A: width=None     longest_value=10   header='booked_on'
B: width=None     longest_value=28   header='supplier'
C: width=12.0     longest_value=9    header='amount'

width=None means no width was stored, so Excel uses its default — the assignment never reached the sheet. A stored width far below longest_value is why the column shows ##### for numbers or truncates text.

The Fix: Address by Letter, Size From Content

# pip install "openpyxl>=3.1,<4" "pandas>=2.2,<3"
from pathlib import Path

import pandas as pd
from openpyxl.utils import get_column_letter

OUT = Path("out/report.xlsx")

def autofit_columns(worksheet, min_width: float = 8.0, max_width: float = 60.0,
                    padding: float = 2.0) -> None:
    """Set each column's width from the longest value it contains."""
    for index in range(1, worksheet.max_column + 1):
        longest = 0
        for (cell,) in worksheet.iter_rows(min_col=index, max_col=index):
            if cell.value is None:
                continue
            # Count the rendered length: a date renders shorter than its repr
            text = cell.value.strftime("%d/%m/%Y") if hasattr(cell.value, "strftime") else str(cell.value)
            longest = max(longest, max(len(line) for line in text.split("\n")))
        width = min(max(longest + padding, min_width), max_width)
        worksheet.column_dimensions[get_column_letter(index)].width = width

def write_report(frame: pd.DataFrame, dest: Path, sheet: str = "Data") -> Path:
    """Write a frame and size its columns to the content."""
    dest.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(dest, engine="openpyxl") as writer:
        frame.to_excel(writer, sheet_name=sheet, index=False)
        worksheet = writer.sheets[sheet]         # AFTER to_excel, not before
        autofit_columns(worksheet)
        worksheet.freeze_panes = "A2"            # header stays visible while scrolling
    return dest

if __name__ == "__main__":
    df = pd.DataFrame({
        "booked_on": pd.to_datetime(["2026-07-15", "2026-07-16"]),
        "supplier": ["Gamma Partners LLP", "Acme"],
        "amount": [1204.50, 98.00],
    })
    print(write_report(df, OUT))

Three details make the autofit believable. Dates are measured by their rendered length, not str(datetime), which is 19 characters and would produce absurdly wide columns. The max_width cap stops one long free-text value forcing a 200-character column. And min_width keeps a column of two-character codes from collapsing to something unreadable.

Fetching the worksheet after to_excel is the same ordering rule that governs number formats — see fix openpyxl number format not applied.

Variant Fix 1: Accounting for the Header Font

A bold header is wider than the same string in the body font, so a column sized on raw character counts clips its own heading. Weight the header row.

# pip install "openpyxl>=3.1,<4"
BOLD_FACTOR = 1.12          # bold Calibri is roughly 12% wider than regular

def autofit_with_header(worksheet, padding: float = 2.0, max_width: float = 60.0) -> None:
    """Size columns from content, allowing extra room for a bold header."""
    from openpyxl.utils import get_column_letter

    for index in range(1, worksheet.max_column + 1):
        header_cell = worksheet.cell(row=1, column=index)
        header_len = len(str(header_cell.value or ""))
        if header_cell.font and header_cell.font.bold:
            header_len *= BOLD_FACTOR

        body_len = max(
            (len(str(cell.value)) for (cell,) in
             worksheet.iter_rows(min_col=index, max_col=index, min_row=2)
             if cell.value is not None),
            default=0,
        )
        width = min(max(header_len, body_len) + padding, max_width)
        worksheet.column_dimensions[get_column_letter(index)].width = width

Variant Fix 2: Hiding and Grouping Instead of Widening

Some columns should not be wide — they should not be visible. column_dimensions carries both.

# pip install "openpyxl>=3.1,<4"
worksheet.column_dimensions["G"].hidden = True          # working column, keep out of sight
worksheet.column_dimensions["H"].outlineLevel = 1       # collapsible group
worksheet.sheet_properties.outlinePr.summaryRight = False

Hiding rather than deleting keeps formulas that reference the column intact, which matters when the sheet ships with live formulas — the constraint discussed in fix openpyxl formulas not calculating.

Variant Fix 3: xlsxwriter for Speed and Simplicity

Writing a fresh file with many columns is faster and less code with xlsxwriter, which applies a width to a whole column in one call.

# pip install xlsxwriter "pandas>=2.2,<3"
import pandas as pd

def write_with_xlsxwriter(frame: pd.DataFrame, dest: str, sheet: str = "Data") -> None:
    """Column widths and formats in one call per column."""
    with pd.ExcelWriter(dest, engine="xlsxwriter") as writer:
        frame.to_excel(writer, sheet_name=sheet, index=False)
        book, worksheet = writer.book, writer.sheets[sheet]
        header_fmt = book.add_format({"bold": True, "bg_color": "#EFF6FF", "border": 1})

        for index, column in enumerate(frame.columns):
            longest = max(frame[column].astype(str).map(len).max(), len(str(column)))
            worksheet.set_column(index, index, min(longest + 2, 60))
            worksheet.write(0, index, str(column), header_fmt)

        worksheet.freeze_panes(1, 0)
        worksheet.autofilter(0, 0, len(frame), len(frame.columns) - 1)

The limitation is that xlsxwriter cannot modify an existing workbook, so it suits generation and not editing. For a report produced fresh each run — the usual case in automating Excel report generation — that is no restriction at all.

How a column width is decided Four steps. Measure the rendered length of every value in the column, using the display form for dates rather than the Python representation. Compare against the header length, scaled up if the header is bold. Clamp the result between a minimum of about eight and a maximum of about sixty characters. Apply it to column dimensions keyed by the column letter. 1. Measure rendered length dates as displayed 2. Header x1.12 if bold take the larger 3. Clamp min 8, max 60 plus padding 4. Apply by column LETTER never by index There is no autofit in the file — you compute it or Excel uses its default A max width matters: one long free-text value would otherwise produce a 200-character column

Verification

Assert on the saved file that every populated column has a stored width, and that the width can hold the content.

# pip install "openpyxl>=3.1,<4"
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

def assert_widths_set(xlsx_path: Path, sheet: str = "Data",
                      min_width: float = 6.0, tolerance: float = 1.0) -> None:
    """Every populated column must carry a width wide enough for its longest value."""
    book = load_workbook(xlsx_path)
    try:
        worksheet = book[sheet]
        problems = []
        for index in range(1, worksheet.max_column + 1):
            letter = get_column_letter(index)
            dimension = worksheet.column_dimensions.get(letter)
            width = getattr(dimension, "width", None)
            if width is None:
                problems.append(f"{letter}: no width stored")
                continue
            if width < min_width:
                problems.append(f"{letter}: width {width:.1f} below the minimum {min_width}")

            longest = max(
                (len(str(cell.value)) for (cell,) in
                 worksheet.iter_rows(min_col=index, max_col=index, min_row=2)
                 if cell.value is not None),
                default=0,
            )
            if longest and width + tolerance < min(longest, 60):
                problems.append(
                    f"{letter}: width {width:.1f} cannot hold {longest}-character values"
                )
        assert not problems, "column width problems:\n  " + "\n  ".join(problems)
        print(f"{worksheet.max_column} column width(s) verified in {xlsx_path.name}")
    finally:
        book.close()

if __name__ == "__main__":
    assert_widths_set(Path("out/report.xlsx"))

The "cannot hold" check is what catches the real complaint from recipients — numbers showing as ##### and text truncated at the cell boundary — before the file is sent. Run it in the same job that builds the report, alongside the number-format assertions.

Measure what Excel draws, not what Python prints The same timestamp measured three ways. The Python repr of the datetime is thirty seven characters. Its str form is nineteen. What Excel actually draws under a dd slash mm slash yyyy format is ten. Sizing a column from the repr or the str form produces a column three to four times wider than it needs to be. One date value, three measured lengths repr() 37 chars str() 19 chars as rendered 10 chars — 15/07/2026 Autofit from the rendered form or every date column comes out absurdly wide

FAQ

Why is the width in characters rather than pixels? Because Excel's column width is defined relative to the workbook's default font. It is stored as a character count so that changing the default font rescales the sheet coherently — which also means the same numeric width looks different in a workbook set in Calibri 11 and one set in Arial 10.

Can I make Excel autofit on open? No. Autofit is an editor action, not a stored property, so a library must compute the widths itself. bestFit = True on a column dimension is written to the file but Excel does not act on it for library-generated content.

Why did my width disappear after pandas wrote the sheet? The worksheet object was captured before to_excel replaced it. Fetch it from writer.sheets after the write.

How do I size a column holding a formula? Measure the expected result, not the formula text, because that is what Excel displays. openpyxl stores only the formula, so estimate from the data feeding it.

Should I set row heights too? Only when wrapping text: worksheet.row_dimensions[n].height = 30 with alignment=Alignment(wrap_text=True). Left alone, Excel does adjust row height for wrapped content on open, which is the one place it does recalculate.

A Note on Very Wide Sheets

Autofitting sixty columns produces a sheet nobody can read without scrolling sideways. Past roughly a dozen columns it is worth capping the total width to the printable page and letting the longest free-text column wrap instead, which keeps the sheet usable on screen and on paper.

Part of Automating Excel Report Generation.