pandas DataFrame to formatted Excel report with openpyxl styles and roundtrip read: working versions (tested 2026-10-15)

pandas==3.0.6
openpyxl==3.1.5
pytest==9.1.1

from pathlib import Path

import pandas as pd
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

HEADER_FILL = PatternFill("solid", start_color="DDEBF7")
MONEY_FORMAT = "#,##0.00"


def write_report(df: pd.DataFrame, path, sheet: str = "Report", money_cols=()) -> Path:
    path = Path(path)
    with pd.ExcelWriter(path, engine="openpyxl") as xw:
        df.to_excel(xw, sheet_name=sheet, index=False)
        ws = xw.sheets[sheet]  # styles applied before the file is saved on exit

        for cell in ws[1]:
            cell.font = Font(bold=True)
            cell.fill = HEADER_FILL
            cell.alignment = Alignment(horizontal="center")

        for idx, col in enumerate(df.columns, start=1):
            letter = get_column_letter(idx)
            longest = max([len(str(col))] + [len(str(v)) for v in df[col]])
            ws.column_dimensions[letter].width = longest + 2
            if col in money_cols:
                for (cell,) in ws.iter_rows(min_row=2, min_col=idx, max_col=idx):
                    cell.number_format = MONEY_FORMAT

        ws.freeze_panes = "A2"
        ws.auto_filter.ref = ws.dimensions
    return path


def read_report(path, sheet: str = "Report") -> pd.DataFrame:
    return pd.read_excel(path, sheet_name=sheet, engine="openpyxl")

import pandas as pd
import pytest
from openpyxl import load_workbook

from report import read_report, write_report


@pytest.fixture
def df():
    return pd.DataFrame({
        "name": ["widget", "gadget"],
        "qty": [3, 10],
        "amount": [1234.5, None],  # None becomes an empty cell, read back as NaN
        "when": [pd.Timestamp("2024-01-02"), pd.Timestamp("2024-02-03")],
    })


def test_roundtrip_values_and_dtypes(tmp_path, df):
    path = write_report(df, tmp_path / "r.xlsx", money_cols=("amount",))
    back = read_report(path)
    pd.testing.assert_frame_equal(back, df, check_dtype=False)
    assert pd.api.types.is_datetime64_any_dtype(back["when"])


def test_styles_applied(tmp_path, df):
    path = write_report(df, tmp_path / "r.xlsx", money_cols=("amount",))
    ws = load_workbook(path)["Report"]
    assert ws["A1"].font.bold is True
    assert ws["A1"].fill.start_color.rgb.endswith("DDEBF7")
    assert ws["C2"].number_format == "#,##0.00"
    assert ws["B2"].number_format == "General"
    assert ws.freeze_panes == "A2"
    assert ws.auto_filter.ref == "A1:D3"


def test_empty_cells_and_nan(tmp_path, df):
    path = write_report(df, tmp_path / "r.xlsx")
    ws = load_workbook(path)["Report"]
    assert ws["C3"].value is None
    assert pd.isna(read_report(path)["amount"].iloc[1])


def test_invalid_sheet_name_raises(tmp_path, df):
    with pytest.raises(ValueError):
        write_report(df, tmp_path / "r.xlsx", sheet="Q1/Totals")

[pytest]
pythonpath = .