Test Excel Output with pytest

A report script that has never been tested fails in the same three ways: a sheet gets renamed, a header row shifts because someone added a title, or a filter quietly drops every row and writes an empty summary. All three are cheap to catch, because the workbook the script saved is a file you can reopen and interrogate — no Excel installation, no screenshots, no manual check.

This guide is the testing half of Testing and Packaging Excel Automation Scripts. It builds fixtures inside the test, asserts on the saved file, and covers the two things that make Excel tests annoying if you meet them by surprise: floating-point totals and formatting assertions.

Prerequisites

pip install pytest pandas openpyxl

You also need a report script with the transform separated from the file handling — the summarise() / build_report() split described in the parent guide. Everything below assumes a report.py at the project root and tests in tests/.

Step 1: Generate the input inside the test

Resist the urge to check a sample workbook into the repository. A fixture built in code is readable, contains no real customer data, and can be varied per test:

# tests/conftest.py
import pandas as pd
import pytest


@pytest.fixture
def orders_frame():
    return pd.DataFrame({
        "Order_ID": [1, 2, 3, 4, 5],
        "Region": ["North", "South", "North", "East", "South"],
        "Amount": [100.0, 250.5, 50.25, 75.0, 24.25],
        "Date": pd.to_datetime(
            ["2026-07-01", "2026-07-02", "2026-07-02", "2026-07-05", "2026-07-09"]
        ),
    })


@pytest.fixture
def orders_file(orders_frame, tmp_path):
    """The same frame, saved as a real .xlsx the code under test can read."""
    path = tmp_path / "orders.xlsx"
    orders_frame.to_excel(path, sheet_name="Orders", index=False)
    return path

tmp_path is a per-test directory that pytest creates and cleans up, so tests never collide and never write into your working tree. Two fixtures rather than one is deliberate: transform tests take the frame and skip the file entirely, and only the tests that exercise reading need the workbook.

Step 2: Assert on the saved workbook

Reopen the file the code wrote and check the things a reader would notice first:

# tests/test_report.py
from openpyxl import load_workbook

from report import build_report


def test_report_structure(orders_file, tmp_path):
    target = build_report(orders_file, tmp_path / "report.xlsx")

    wb = load_workbook(target)
    assert wb.sheetnames == ["Summary", "Detail"]

    ws = wb["Summary"]
    header = [cell.value for cell in ws[1]]
    assert header == ["Region", "Amount", "Share"]

    regions = [ws.cell(row=r, column=1).value for r in range(2, ws.max_row + 1)]
    assert regions == ["South", "North", "East"]     # sorted by amount, descending
    assert ws.max_row == 4                            # header + three regions

Four assertions, four real failure modes. sheetnames catches a renamed or missing tab. The header comparison catches index=False being dropped, which silently adds an unnamed first column and shifts everything right. The region list catches a broken sort or a filter that removed a group. max_row catches the empty-summary case, which is the failure that survives longest in production because an empty report still arrives on time and still looks like a report.

Step 3: Compare numbers without false failures

Excel stores numbers as IEEE doubles, and pandas sums in floating point, so an exact == on a total is a test that fails for reasons that have nothing to do with your code:

import pytest


def test_totals(orders_file, tmp_path):
    target = build_report(orders_file, tmp_path / "report.xlsx")
    wb = load_workbook(target, data_only=True)
    ws = wb["Summary"]

    totals = {ws.cell(row=r, column=1).value: ws.cell(row=r, column=2).value
              for r in range(2, ws.max_row + 1)}

    assert totals["North"] == pytest.approx(150.25)
    assert totals["South"] == pytest.approx(274.75)
    assert sum(totals.values()) == pytest.approx(500.0, abs=0.01)

pytest.approx handles the ordinary case. The abs=0.01 on the grand total is a different decision: for money, an absolute tolerance of one cent expresses the real requirement better than a relative one, because it stays meaningful whether the total is 500 or 500,000.

data_only=True matters if the report writes formulas. openpyxl does not evaluate them — it returns the cached value Excel stored, or None if the file has never been opened in Excel. If your report writes =SUM(B2:B4) rather than a computed number, assert on the formula string instead, and do the arithmetic check on the DataFrame the transform returned. Read Formula Results with openpyxl data_only explains the caching rules that make this behave the way it does.

Step 4: Test the formatting that carries meaning

Not all formatting is worth a test. The number format on a currency column is, because a report showing 1234.5 where the finance team expects £1,234.50 is wrong in the way that gets it sent back. openpyxl exposes every style attribute it wrote, so the assertion is direct:

def test_currency_column_is_formatted(orders_file, tmp_path):
    target = build_report(orders_file, tmp_path / "report.xlsx")
    ws = load_workbook(target)["Summary"]

    assert ws["A1"].font.bold is True                 # header stands out
    assert ws["B2"].number_format == '"£"#,##0.00'    # money reads as money
    assert ws["C2"].number_format == "0.0%"           # share reads as a percentage
    assert ws.freeze_panes == "A2"                    # header stays visible

Keep these assertions to the handful that change the meaning of the page. Testing every fill colour produces a suite that fails whenever someone adjusts a shade, which trains people to ignore it. The rules behind these formats are in Applying Number and Date Formats in Excel.

Step 5: Test the failure path too

The most valuable test in a report suite is often the one asserting that bad input is rejected, because a job that writes a plausible-looking report from broken input is worse than a job that stops:

import pytest


def test_missing_column_is_reported_clearly(orders_frame, tmp_path):
    broken = orders_frame.drop(columns=["Region"])
    source = tmp_path / "broken.xlsx"
    broken.to_excel(source, sheet_name="Orders", index=False)

    with pytest.raises(ValueError, match="Region"):
        build_report(source, tmp_path / "out.xlsx")

    assert not (tmp_path / "out.xlsx").exists()       # nothing half-written

The match="Region" is the point: it pins not just that the code raised, but that the message names the missing column, which is the difference between a five-second diagnosis and a debugging session. The final assertion pins that a failed run leaves no partial file behind for someone to email by mistake.

Common pitfalls and gotchas

Performance and scale notes

Excel output tests are fast when the fixtures are small: writing a five-row workbook and reopening it costs a few milliseconds, so a suite of thirty runs in under a second. The cost appears when fixtures grow — a 50,000-row fixture written per test turns a one-second suite into a minute. Keep one realistic-size test, mark it @pytest.mark.slow, and run it separately.

If the report writes large files, test the streaming path rather than the volume: assert that the code uses write-only or chunked mode by checking peak memory with a small file, not by writing a real one. The techniques themselves are covered in Working with Large Excel Files in Python.

Conclusion

Testing an Excel report means reopening the file and asking the four questions a reader would ask: are the sheets there, is the header right, are there rows, and do the totals add up. Build fixtures inside the test with tmp_path, use pytest.approx for anything computed, assert the handful of formats that carry meaning, and pin the error messages for bad input. That suite is small, runs in a second, and catches the three failures that otherwise reach the person waiting for the report.

Frequently asked questions

Should I compare the whole file against a golden workbook? No. Two .xlsx files written from identical data differ in zip timestamps and internal ordering, so a byte comparison fails constantly. Compare the values you care about — sheet names, headers, a total, a row count — by reopening the file.

Why do my float assertions fail by a tiny amount? Excel stores numbers as IEEE doubles and pandas sums in floating point, so a total can land on 149.99999999999997. Use pytest.approx or round before comparing.

How do I test formatting rather than values? Reopen with openpyxl and read the style attributes — cell.number_format, cell.font.bold, cell.fill.start_color.rgb. openpyxl exposes what was written, so the assertion is direct.

Do the tests need Microsoft Excel installed? No. openpyxl, pandas and xlsxwriter write and read the file format directly, so the whole suite runs on a headless CI runner with no Excel anywhere.

Related

Up to the parent guide:

• Testing and Packaging Excel Automation Scripts — where the transform/I-O split that makes these tests possible comes from.

Related guides:

• Validate an Excel Report Before Sending It — the same assertions, run in production instead of in CI.
• Build a Command-Line Tool for Excel Reports with argparse — making parse_args testable without a subprocess.
• Compare Two Excel Files for Differences with Python — when you do want to diff two workbooks.
• Read Formula Results with openpyxl data_only — why a formula cell reads back as None in a test.