Testing That a Generated Excel Report Is Actually Correct

A report generator is code that produces a document, and the most common way it fails is by producing a document that looks fine and is wrong. A join doubles a customer, a filter drops a region, a total sits in the wrong column. The workbook opens without complaint. Testing catches these, and the structure of a good test suite for a report is different from that of a typical function test, because there are two things to get right. This post sets out what to test, with pytest and openpyxl examples, and how to keep the tests useful. It is a spoke of automating Excel work without clicking.

Two things to test

The numbers. Does the report contain the right figures? That depends on the query, the filters, the joins and the arithmetic.

The workbook. Does the file have the right sheets, headers, formats and formulas, and does it open? That depends on the writer.

Mix them and failures are hard to diagnose: a test that compares a whole file byte for byte fails for a font change and for a wrong total alike. Separate the layers. Compute the data in a function that returns a DataFrame, test it directly, and have the writer take that DataFrame and produce the file. Then there are three levels of test: the data function, the writer given a known frame, and the whole pipeline on a fixture.

Test the data function

import pandas as pd
from report import summarize

def test_summary_by_region():
    orders = pd.DataFrame({
        "region": ["North", "North", "South"],
        "category": ["Books", "Games", "Books"],
        "quantity": [2, 1, 1],
        "unit_price": [20, 60, 10],
    })
    out = summarize(orders)
    assert out.set_index("region")["revenue"].to_dict() == {"North": 100, "South": 10}
    assert out["revenue"].sum() == 110

The input is tiny and hand-checkable: two Books at 20 and one Games at 60 make 100 for North. Small inputs are easy to verify and make failures obvious. Add cases for each rule: a refund (negative quantity), a missing region, a region with no rows in the period, a price of zero. Each is a line in the fixture and an assert.

For SQL, run the query against a small test database (SQLite or a throwaway PostgreSQL) with known rows. The query is code and deserves a test like any other (testing code that produces JSON covers the same idea for another output).

Check an independent control total

The most valuable test compares the report with an independent calculation. If the report says total revenue is 110, a separate simple query should say 110:

def test_total_matches_source(engine, report_df):
    source_total = pd.read_sql("SELECT SUM(quantity * unit_price) AS t FROM orders WHERE order_date >= '2025-01-01'", engine)["t"][0]
    assert abs(report_df["revenue"].sum() - source_total) < 0.005

The two calculations are written differently on purpose. If both use the same joined subquery they share its bug. A plain sum over the base table, with no joins, is the control. Add counts as well: number of orders in the report against COUNT(DISTINCT order_id) in the source. Run this check in production too, not only in tests, and fail the job on a mismatch (checking totals between two Excel reports).

Reopen the saved file

Test the file, not the intention. Write it to a temporary path and read it back:

from openpyxl import load_workbook

def test_workbook_structure(tmp_path):
    path = tmp_path / "report.xlsx"
    write_report(sample_frame(), path)

    wb = load_workbook(path)                      # formulas as text
    assert wb.sheetnames == ["Summary", "Detail"]
    ws = wb["Summary"]
    assert [c.value for c in ws[1]] == ["Region", "Books", "Games", "Total"]
    assert ws.freeze_panes == "A2"
    assert ws.auto_filter.ref is not None
    assert ws["D2"].value == "=B2+C2"             # a formula, as written
    assert ws["B2"].number_format == "#,##0.00"

load_workbook(path) without data_only gives the formula text, so you can assert that a total is a formula and which one. Header names, order, freeze panes, filters and number formats are all properties of the file and cheap to check. Also check sheet and table names, column widths if layout matters, and that a hidden sheet is not hidden by accident.

Read values as a user would. Open with data_only=True and the cells of formulas are None unless a spreadsheet application has calculated and saved the file, because openpyxl and XlsxWriter do not calculate. If your tests need formula results, either assert on the formula text and test the arithmetic separately, or recalculate with LibreOffice in the test run (soffice --headless --convert-to xlsx) and then read the values (handling Excel formulas and cached values).

Assert there are no errors

A generic test for any generated workbook looks for error values and empty cells where there should be data:

ERRORS = {"#REF!", "#NAME?", "#VALUE!", "#DIV/0!", "#N/A", "#NUM!", "#NULL!"}

def test_no_error_values(tmp_path):
    path = tmp_path / "r.xlsx"
    write_report(sample_frame(), path)
    wb = load_workbook(path, data_only=True)
    for ws in wb:
        for row in ws.iter_rows():
            for cell in row:
                assert cell.value not in ERRORS, f"{ws.title}!{cell.coordinate} is {cell.value}"

This needs cached values, so run it on a recalculated file, or on values-only reports. It catches the failure that reaches readers most visibly (Excel REF NAME and VALUE errors explained). Also test that numbers are numbers: isinstance(ws["B2"].value, (int, float)). A column of text numbers sums to zero downstream.

The awkward-input fixture

Most report bugs come from data the author did not picture. Keep a fixture file of awkward inputs and generate a report from it in every test run. Include:

• an empty period (no rows) and a group with a single row;

• null values in every column;

• a negative amount and a zero;

• a very long string, a string with a control character, a leading = or +;

• a name with accents, quotes and an ampersand;

• a sheet name that is too long or contains a slash;

• duplicated keys, if the source can contain them;

• the largest number of rows you expect, in a slow test.

Each time production surprises you, add the case to the fixture before fixing the bug. Over time the fixture becomes the written record of every way the data has been strange (fixing Excel found unreadable content after a script export lists causes worth including).

A golden file for layout

For layout, a golden-file comparison is useful. Save a known-good workbook and compare structure, not bytes. Dump the workbook's essentials to JSON (sheet names, dimensions, values, formulas, number formats) and compare that dump with the stored one:

def dump(path):
    wb = load_workbook(path)
    return {ws.title: [[(c.value, c.number_format) for c in row] for row in ws.iter_rows()] for ws in wb}

def test_matches_golden(tmp_path):
    write_report(sample_frame(), tmp_path / "r.xlsx")
    assert dump(tmp_path / "r.xlsx") == json.loads(Path("tests/golden.json").read_text())

A diff in the golden file shows exactly which cell changed. When you change the layout on purpose, regenerate the golden file and review the diff in the commit. Byte comparison of xlsx files does not work, because the zip contains timestamps and ordering that change between runs.

Does it open in Excel

Libraries are more forgiving than Excel, so a test that reopens the file with openpyxl proves less than it appears. Two further checks help. Validate the package with Microsoft's Open XML SDK validator in the build if you can run it. And keep a manual step for new report types: open a generated sample in the oldest Excel version the users have, and look at it once. Many problems that automated tests miss are visible within seconds to a human (understanding Excel errors when data comes from code).

Run the checks in production

Tests protect against code changes. Production checks protect against data changes. After each real run, compare the row count and the total with the previous period and with the source, and fail or alert when they differ by more than a threshold. A report whose revenue dropped by 90 percent is probably wrong, and a rule that flags a change of more than 30 percent costs a few lines. Log the figures so a trend is visible.

Keep the tests fast and honest

Tests that take minutes get skipped. Keep the unit tests on tiny frames and run the large-volume test separately. Avoid mocking the writer: use the real library and a temporary directory. Avoid asserting on things that vary, such as the current date or file timestamps; pass the date in. Name tests after the rule they protect: test_refund_reduces_total, not test_1.

When automated report tests are not enough

The audience judges the look. Colors, alignment and print layout need human review, at least when a template is created.

The business rule is unclear. A test can check that the code does what it says, not that what it says is what the business meant. Review definitions with the owner.

The inputs are unpredictable. If the source changes shape without warning, add schema checks at the input, not only tests of the output.

The report is rarely run. A yearly report with a long gap should have its test run again before use, with the new year's data.

See the pytest documentation and the openpyxl guide to reading workbooks. To convert a small result to JSON, use the CSV to JSON converter.