Python Doc & Data Automation

Fix openpyxl Number Format Not Applied

The code sets a format, the save succeeds, and the workbook opens with raw numbers:

>>> cell = worksheet["C2"]
>>> cell.number_format = "£#,##0.00"
>>> workbook.save("out/report.xlsx")
# Excel shows 1204.5, not £1,204.50

Reading the file back is the confusing part — the format is often there:

>>> load_workbook("out/report.xlsx")["Sheet1"]["C2"].number_format
'£#,##0.00'

Which means the assignment worked and something else is wrong: the format landed on a different cell than the one holding the value, or on a different object than the one that was saved, or the code string is not one Excel accepts.

Root Cause

number_format is a per-cell style attribute. Four things routinely break the link between the cell you styled and the cell the reader sees.

Formatting is applied to a worksheet obtained before pandas wrote to it, so the styled cells are overwritten by the write. Or the loop starts at row 1 and styles the header instead of the data. Or the workbook was opened in write_only or read_only mode, where cell styles are not retained. Or the format code itself is invalid — Excel silently falls back to General when it cannot parse a code, and openpyxl does not validate.

Where the format gets lost between assignment and save A chain from setting number format to Excel rendering it, with four failure points branching off. The first is styling a worksheet object that pandas later overwrites. The second is styling row one, the header, instead of the data rows. The third is opening the workbook in write only or read only mode where styles are not kept. The fourth is an invalid format code, which Excel silently renders as General. set number_format on a cell object workbook.save() styles serialised Excel renders £1,204.50 Stale worksheet pandas overwrote it Header styled loop began at row 1 write_only mode styles not retained Invalid code falls back to General The value is always written correctly — only the presentation is lost so the diagnostic is to read the saved file back and compare formats cell by cell

Minimal Diagnostic

Read the saved file and print what each cell actually carries. This separates "the format was never written" from "the format is on the wrong cells".

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

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

def format_map(xlsx_path: Path, sheet: str | None = None, rows: int = 6) -> None:
    """Print value and number_format for the first rows of every column."""
    if not xlsx_path.exists():
        raise FileNotFoundError(f"No such workbook: {xlsx_path}")
    try:
        book = load_workbook(xlsx_path)          # not read_only: styles 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 row in worksheet.iter_rows(min_row=1, max_row=rows):
        for cell in row:
            if cell.value is None:
                continue
            print(f"{cell.coordinate:>5}  {str(cell.value)[:22]:<24} {cell.number_format}")
    book.close()

if __name__ == "__main__":
    format_map(OUT)

   A1  booked_on                General
   B1  amount                   General
   A2  2026-07-15 00:00:00      General      <- date written, format missing
   B2  1204.5                   £#,##0.00    <- format landed here correctly

load_workbook(..., read_only=True) returns cells without style information, so the diagnostic must open the file normally — using read-only mode here reports General for everything and sends you chasing a bug that is not there.

Fix: Style After Writing, and Skip the Header

The most common case by far is pandas plus openpyxl. Grab the worksheet from the writer after to_excel has run, and start the loop at row 2.

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

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

FORMATS = {
    "booked_on": "dd/mm/yyyy",
    "amount": "£#,##0.00",
    "share": "0.0%",
    "account": "@",              # text: preserves leading zeros
}

def write_with_formats(frame: pd.DataFrame, dest: Path, sheet: str = "Data") -> Path:
    """Write a frame, then apply per-column number formats to the data rows."""
    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

        for position, column in enumerate(frame.columns, start=1):
            code = FORMATS.get(column)
            if code is None:
                continue
            for (cell,) in worksheet.iter_rows(
                min_col=position, max_col=position,
                min_row=2,                        # row 1 is the header
                max_row=worksheet.max_row,
            ):
                cell.number_format = code
    return dest

if __name__ == "__main__":
    df = pd.DataFrame({
        "booked_on": pd.to_datetime(["2026-07-15", "2026-07-16"]),
        "amount": [1204.5, 98.0],
        "share": [0.125, 0.4],
        "account": ["00417", "00992"],
    })
    print(write_with_formats(df, OUT))

Two ordering rules make this reliable. Obtain the worksheet from writer.sheets after the data is written, because a worksheet captured earlier refers to an object pandas then replaces. And style by column position derived from frame.columns, not by a hard-coded letter, so an added column upstream cannot silently shift the currency format onto the date.

Variant Fix 1: write_only Mode Discards Styles

Workbook(write_only=True) streams rows for memory efficiency and does not keep a cell grid, so attributes set on a cell after appending are lost. Build styled cells before appending them:

# pip install "openpyxl>=3.1,<4"
from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell

book = Workbook(write_only=True)
sheet = book.create_sheet("Data")

for amount in [1204.5, 98.0]:
    cell = WriteOnlyCell(sheet, value=amount)
    cell.number_format = "£#,##0.00"     # set BEFORE append, on a WriteOnlyCell
    sheet.append([cell])

book.save("out/stream.xlsx")

For large sheets, xlsxwriter is usually the better answer — it applies a format to an entire column in one call and is faster on the write path than styling cells individually:

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

with pd.ExcelWriter("out/big.xlsx", engine="xlsxwriter") as writer:
    frame.to_excel(writer, sheet_name="Data", index=False)
    book, sheet = writer.book, writer.sheets["Data"]
    money = book.add_format({"num_format": "£#,##0.00"})
    sheet.set_column("B:B", 14, money)     # width and format for the whole column

Variant Fix 2: The Format Code Is Not Valid

Excel accepts a specific grammar. Codes copied from documentation, from a locale-specific dialog, or written with the wrong separators fall back to General without complaint.

IntentValid codeCommon mistake
Currency£#,##0.00£#.##0,00 (continental separators)
Thousands#,##0#.##0
Percentage0.0%%0.0
Datedd/mm/yyyyDD/MM/YYYY in some locales
Text@"text"
Negatives in red#,##0.00;[Red]-#,##0.00#,##0.00;[RED]-#,##0.00
Accounting_-£* #,##0.00_-;-£* #,##0.00_-;_-£* "-"??_-;_-@_-truncating the four sections

A format string has up to four semicolon-separated sections — positive, negative, zero, text — and supplying two means the second applies to negatives, not to zeros. If a code renders correctly for positive values and oddly for zeros, that is the cause.

# pip install "openpyxl>=3.1,<4"
from openpyxl.styles.numbers import BUILTIN_FORMATS

# The built-in ids Excel guarantees; use one when portability matters more than exact styling
for index, code in sorted(BUILTIN_FORMATS.items())[:12]:
    print(index, code)

Variant Fix 3: The Value Is Text, So No Numeric Format Can Apply

A number stored as a string ignores every numeric format — the cell is left-aligned and shows exactly what was written.

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

book = load_workbook("out/report.xlsx")
sheet = book.active
for row in sheet.iter_rows(min_row=2, min_col=2, max_col=2):
    for cell in row:
        if isinstance(cell.value, str):
            # A numeric format on text does nothing — convert the value first
            try:
                cell.value = float(cell.value.replace(",", "").replace("£", ""))
                cell.number_format = "£#,##0.00"
            except ValueError:
                pass                    # genuinely textual, leave it alone
book.save("out/report.xlsx")

The inverse case matters just as much: identifiers like 00417 must stay text with the @ format, or Excel strips the leading zeros the moment the file is opened. The date-side equivalent of this whole class of problem is covered in fix Excel dates showing as numbers in pandas.

The order that makes formats stick Three ordered steps inside the ExcelWriter context. First to_excel writes the frame and creates the worksheet. Second the worksheet is fetched from writer.sheets, after the write rather than before. Third the loop styles data rows from row two to max_row. A crossed-out variant shows fetching the worksheet before to_excel, which styles an object that is then replaced. Inside the ExcelWriter block 1. to_excel(...) creates the worksheet 2. writer.sheets[name] fetch after the write 3. style min_row=2 skip the header row Fetching the worksheet before to_excel styles an object pandas then replaces — nothing survives min_row=1 formats the header text

Verification

Assert on the saved file, not on the in-memory objects — that is the only check that reflects what a recipient sees.

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

def assert_formats(xlsx_path: Path, expected: dict[str, str], sheet: str = "Data") -> None:
    """Every data cell in a named column must carry the expected format code."""
    book = load_workbook(xlsx_path)               # styles need a normal open
    try:
        worksheet = book[sheet]
        headers = {cell.value: cell.column for cell in worksheet[1]}
        for column, code in expected.items():
            assert column in headers, f"no column named {column!r}"
            position = headers[column]
            wrong = [
                cell.coordinate
                for (cell,) in worksheet.iter_rows(min_col=position, max_col=position, min_row=2)
                if cell.value is not None and cell.number_format != code
            ]
            assert not wrong, (
                f"{column}: {len(wrong)} cell(s) not formatted as {code!r}, e.g. {wrong[0]}"
            )
        print(f"{len(expected)} column format(s) verified in {xlsx_path.name}")
    finally:
        book.close()

if __name__ == "__main__":
    assert_formats(Path("out/report.xlsx"),
                   {"booked_on": "dd/mm/yyyy", "amount": "£#,##0.00", "share": "0.0%"})

Reading the header row to find column positions, rather than hard-coding letters, keeps the assertion honest when a column is inserted upstream. Wire it into the same job that produces the report — the reporting workflow in automating Excel report generation is where a silent formatting regression is most likely to reach an audience.

The four sections of a format code A format code is split by semicolons into up to four sections, applied to positive values, negative values, zero, and text in that order. Supplying two sections means the second applies to negatives and zero falls back to the positive form. A worked example shows a currency format with a red negative section and a dash for zero. positive ; negative ; zero ; text £#,##0.00 positive values [Red]-£#,##0.00 negative values "-" zero @ text values Supply only two sections and zero falls back to the positive form A code that looks right for positives and odd for zeros is almost always missing its third section

FAQ

Why does the format appear when I read the file back but not in Excel? Almost always because the format is on cells that are empty or on the header row, while the populated data cells still have General. The diagnostic prints value and format together, which makes that obvious at a glance.

Does cell.style do the same thing?cell.style assigns a named style covering font, fill, border and number format together. Mixing it with cell.number_format means whichever is set last wins, so pick one mechanism per cell.

Can I set a format for a whole column with openpyxl? Not in a way Excel honours for existing cells — column_dimensions[...].number_format applies to new cells only. Loop the cells, or switch to xlsxwriter and set_column, which writes a genuine column format.

Why do my currency symbols look wrong on another machine? Currency and date codes are interpreted against the viewer's locale. Use unambiguous codes (yyyy-mm-dd), or the locale-neutral built-in ids, when the recipient's regional settings are unknown.

Formats survive but conditional colours do not — why? Conditional formatting is a separate feature stored per worksheet, not a cell attribute, and openpyxl needs worksheet.conditional_formatting.add(...). Setting number_format will never produce colour rules.

Part of Working with Excel Dates and Number Formats.

/html>