Compare Excel Output to a Golden File in Tests

Unit tests check the parts of a report you thought to assert: this total, that header. A golden file test checks everything else too. The approved output is stored next to the tests, every run compares against it, and any change — a lost number format, a header that is no longer bold, a total that moved a row — fails with a diff that says exactly what changed. For Excel output the trick is choosing what to compare, because the workbook's bytes change every time it is saved. This guide, part of Testing and Packaging Excel Automation Scripts, builds a readable snapshot format, a pytest fixture with an update option and shows the diff a real regression produces.

Prerequisites

pip install openpyxl pytest

The report under test — a title, a generated-at timestamp, a styled header, data rows with a number format and a total row:

# report.py — the code under test
import datetime as dt

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill

def build_report(rows, path, generated=None):
    wb = Workbook()
    ws = wb.active
    ws.title = "Summary"
    ws["A1"] = "Regional sales"
    ws["A1"].font = Font(size=14, bold=True)
    ws["D1"] = generated or dt.datetime.now()
    ws.append([])
    ws.append(["Region", "Orders", "Revenue"])
    for cell in ws[3]:
        cell.font = Font(bold=True)
        cell.fill = PatternFill("solid", fgColor="FFDDEBF7")
    for region, orders, revenue in rows:
        ws.append([region, orders, revenue])
    total_row = ws.max_row + 1
    ws.cell(total_row, 1, "Total").font = Font(bold=True)
    ws.cell(total_row, 2, f"=SUM(B4:B{total_row - 1})")
    ws.cell(total_row, 3, f"=SUM(C4:C{total_row - 1})")
    for row in ws.iter_rows(min_row=4, min_col=3, max_col=3):
        row[0].number_format = "#,##0.00"
    ws.column_dimensions["A"].width = 18
    ws.freeze_panes = "A4"
    wb.save(path)

Step 1 — Do not compare bytes

The obvious golden test — save the expected .xlsx, compare files — fails on the second run. openpyxl sets the workbook's modified time to "now" in docProps/core.xml every time it saves, whatever the code does, so identical reports produce different bytes. Even if the bytes matched, a failure would only say "files differ", leaving you to open both in Excel and hunt.

XlsxWriter is different: with a fixed creation date its output is reproducible, and in a test two builds a second apart were byte-identical:

workbook = xlsxwriter.Workbook(buffer, {"in_memory": True})
workbook.set_properties({"created": datetime.datetime(2026, 1, 1)})

That makes a byte comparison possible for XlsxWriter reports, but it still cannot tell you what changed, and a library upgrade that reorders XML fails every test at once. A text snapshot solves both problems.

Step 2 — Describe the workbook as text

The snapshot lists each sheet's settings, then one line per non-empty cell with its value, number format, bold and fill. Floats are rounded to ten significant digits so harmless floating-point noise does not fail tests, and masked cells are replaced by a placeholder:

# snapshot.py — a readable, diffable description of a workbook
from openpyxl import load_workbook

def describe(cell):
    value = cell.value
    if isinstance(value, float):
        value = float(f"{value:.10g}")                   # ignore float noise
    parts = [repr(value.isoformat() if hasattr(value, "isoformat") else value)]
    if cell.number_format != "General":
        parts.append(f"fmt={cell.number_format}")
    if cell.font.b:
        parts.append("bold")
    if cell.fill.fill_type:
        parts.append(f"fill={cell.fill.fgColor.rgb}")
    return " ".join(parts)

def snapshot(path, mask=()):
    wb = load_workbook(path)
    lines = []
    for ws in wb.worksheets:
        widths = ",".join(f"{k}:{d.width:g}" for k, d in sorted(ws.column_dimensions.items()) if d.customWidth)
        merged = ",".join(sorted(str(r) for r in ws.merged_cells.ranges))
        lines.append(f"[{ws.title}] state={ws.sheet_state} freeze={ws.freeze_panes} widths={widths} merged={merged}")
        for row in ws.iter_rows():
            for cell in row:
                if cell.value is None and not cell.fill.fill_type:
                    continue
                key = f"{ws.title}!{cell.coordinate}"
                lines.append(f"{cell.coordinate} " + ("<masked>" if key in mask else describe(cell)))
    return "\n".join(lines) + "\n"

import datetime as dt

rows = [("North", 120, 18240.5), ("South", 95, 14195.0), ("East", 60, 9730.25)]
build_report(rows, "summary.xlsx", generated=dt.datetime(2026, 9, 23, 7, 30))
print(snapshot("summary.xlsx", mask={"Summary!D1"}))

[Summary] state=visible freeze=A4 widths=A:18 merged=
A1 'Regional sales' bold
D1 <masked>
A3 'Region' bold fill=FFDDEBF7
B3 'Orders' bold fill=FFDDEBF7
C3 'Revenue' bold fill=FFDDEBF7
D3 None bold fill=FFDDEBF7
A4 'North'
B4 120
C4 18240.5 fmt=#,##0.00
…
C7 '=SUM(C4:C6)' fmt=#,##0.00

The very first snapshot found something: D3 — an empty cell — carries the header's bold and fill. The header loop styles every cell in row 3, and the timestamp in D1 makes the sheet four columns wide. No unit test would have asserted on D3; the snapshot shows it because it records everything. Whether to fix it or accept it is a decision you now get to make.

Choose what to include deliberately. Values, formats, bold, fills, freeze panes, widths and merged ranges cover what readers notice; fonts, borders, conditional formatting or data validation can be added to describe when a report depends on them.

Step 3 — A pytest fixture with an update switch

The golden fixture compares a snapshot with tests/golden/<name>.txt. When no golden file exists yet it writes one and fails, so a new snapshot is always looked at before it is trusted. --update-golden rewrites the files for intended changes:

# conftest.py
import difflib
from pathlib import Path

import pytest

GOLDEN_DIR = Path(__file__).parent / "tests" / "golden"

def pytest_addoption(parser):
    parser.addoption("--update-golden", action="store_true",
                     help="rewrite golden snapshots from the current output")

@pytest.fixture
def golden(request):
    update = request.config.getoption("--update-golden")

    def check(name, actual):
        path = GOLDEN_DIR / f"{name}.txt"
        if update or not path.exists():
            path.parent.mkdir(parents=True, exist_ok=True)
            path.write_text(actual, encoding="utf-8")
            if not update:
                pytest.fail(f"created {path}; review it, commit it and rerun")
            return
        expected = path.read_text(encoding="utf-8")
        if actual != expected:
            diff = "".join(difflib.unified_diff(
                expected.splitlines(keepends=True), actual.splitlines(keepends=True),
                fromfile=f"golden/{name}.txt", tofile="current output"))
            pytest.fail(f"{name} differs from its golden snapshot:\n{diff}", pytrace=False)
    return check

# test_report.py
import datetime as dt

from report import build_report
from snapshot import snapshot

ROWS = [("North", 120, 18240.5), ("South", 95, 14195.0), ("East", 60, 9730.25)]

def test_summary_report_matches_golden(tmp_path, golden):
    out = tmp_path / "summary.xlsx"
    build_report(ROWS, out, generated=dt.datetime(2026, 9, 23, 7, 30))
    golden("summary_report", snapshot(out, mask={"Summary!D1"}))

Passing a fixed generated time and masking D1 is belt and braces: the fixed time keeps the report deterministic, and the mask keeps the test stable if someone later drops the parameter.

Step 4 — Read the diff when something changes

To see what a regression looks like, the header's bold was removed and the revenue format changed from #,##0.00 to #,##0. The test failed with:

--- golden/summary_report.txt
+++ current output
-A3 'Region' bold fill=FFDDEBF7
+A3 'Region' fill=FFDDEBF7
…
-C4 18240.5 fmt=#,##0.00
+C4 18240.5 fmt=#,##0

Every changed line names the cell and the property. If the change is a bug, fix the code. If it is intended — the new format was requested — run pytest --update-golden, check git diff tests/golden/, and commit the snapshot with the code change so the reviewer sees both. That review step is what turns snapshots from "tests that always pass after updating" into a record of every visible change to the report.

Common pitfalls

• Updating without reading. --update-golden makes any failure pass. Always read the resulting git diff before committing.
• Snapshots that are too big. A 10,000-row report makes a 10,000-line snapshot that nobody reviews. Snapshot a small, representative input, and test large inputs with targeted assertions.
• Library upgrades. A new pandas or openpyxl version can change defaults — pandas versions differ, for example, in whether to_excel makes the header bold. Expect snapshot diffs after upgrades and review them like any other change.
• Platform differences. Keep the snapshot free of absolute paths and locale-dependent formatting, or tests pass locally and fail in CI.

Conclusion

Compare generated workbooks through a text snapshot, not their bytes: one line per cell with value, number format, bold and fill, floats rounded, volatile cells masked. A pytest fixture compares it with a committed golden file, fails with a unified diff that names each changed cell and property, and rewrites the golden file only on --update-golden — after which the diff is reviewed with the code.

Frequently asked questions

Why can't I compare a generated .xlsx file with an expected file byte for byte? openpyxl writes the save time into docProps/core.xml on every save, so two runs of the same code never produce identical bytes. Compare a description of the contents instead — values, formats and styles — which stays the same when the report does.

What is a golden file test? A test that compares the program's output with an approved copy stored in the repository. When the output changes, the test fails with a diff; if the change was intended, the golden copy is regenerated and the diff is reviewed like any other code change.

How do I handle cells that change every run, like a generated-at timestamp? Replace them with a placeholder such as in the snapshot, or pass a fixed value into the report function during tests. Masking keeps the rest of the sheet under test while ignoring the one cell that legitimately varies.

Can XlsxWriter produce identical files on every run? Yes, if the creation date is fixed with workbook.set_properties({"created": ...}). In a test, two builds a second apart were then byte-identical. A text snapshot is still easier to review, because a byte difference does not say what changed.

Related

• Up one level: Testing and Packaging Excel Automation Scripts — making report code dependable.
• Test Excel Output with pytest — targeted assertions for the numbers that matter most.
• Create Excel Test Fixtures in Memory for pytest — small, representative inputs for snapshot tests.
• Compare Two Excel Files for Differences with Python — the same idea applied to data files.