Python Excel AutomationPractical guides for developers
How-to
Automating Reporting WorkflowsDeep dive

Create Excel Test Fixtures in Memory for pytest

Stop committing sample spreadsheets to the test folder. Build each test workbook in memory with openpyxl — including the awkward shapes real exports have, such as title rows above the header, dates and numbers stored as text, error cells, formulas without results and formatted empty rows — and run one test across all of them with pytest.mark.parametrize.
Logging, testing & packagingopenpyxl

Tests for Excel code tend to accumulate a folder of sample workbooks: test_input.xlsx, test_input2.xlsx, broken_one_from_finance.xlsx. Nobody remembers what each contains, a diff cannot show what changed, and adding a case means opening Excel. Generating fixtures in code fixes all three — and makes it cheap to test the ugly layouts that real exports produce. This guide, part of Testing and Packaging Excel Automation Scripts, builds a small fixture library, a loader that must cope with every variant, and a parametrized test suite that ran ten tests in a third of a second.

One helper, many awkward workbooks A make_xlsx helper builds a workbook in memory from rows and optional extras. Small builder functions use it to produce the layouts real exports have — a clean table, title rows above the header, dates and numbers stored as text, error cells, formulas without stored results, styled empty rows and a hidden sheet in front — each returned as bytes. make_xlsx(rows, …) returns bytes clean the happy path title_rows header on row 3 dates / numbers as text "2026-09-01", "120.50" error_cells #N/A in Amount formula_amounts no cached results blank rows, hidden sheet styled empties, Lookup first

Prerequisites

Bash
pip install pandas openpyxl pytest

Step 1 — Write the helper and the variants

One helper builds a workbook from rows and returns its bytes. Each variant is a small function named after what makes it awkward, so a failing test says "title_rows" rather than "test_input2.xlsx":

Python
# xlsx_fixtures.py — workbooks for tests, built in memory
import datetime as dt
import io

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

HEADER = ["Order ID", "Region", "Order date", "Amount"]
ROWS = [
    [1001, "North", dt.date(2026, 9, 1), 120.5],
    [1002, "South", dt.date(2026, 9, 2), 40.0],
    [1003, "East", dt.date(2026, 9, 2), 80.0],
]

def make_xlsx(rows, sheet="Orders", title_rows=(), extra=None):
    """Build a workbook in memory and return its bytes."""
    wb = Workbook()
    ws = wb.active
    ws.title = sheet
    for line in title_rows:
        ws.append([line])
    for row in rows:
        ws.append(row)
    if extra:
        extra(wb, ws)
    buffer = io.BytesIO()
    wb.save(buffer)
    return buffer.getvalue()

def clean():
    return make_xlsx([HEADER, *ROWS])

def title_rows():
    return make_xlsx([HEADER, *ROWS], title_rows=["Orders export", "Generated 2026-09-23"])

def dates_as_text():
    return make_xlsx([HEADER, *[[r[0], r[1], r[2].isoformat(), r[3]] for r in ROWS]])

def numbers_as_text():
    return make_xlsx([HEADER, *[[r[0], r[1], r[2], f"{r[3]:.2f}"] for r in ROWS]])

def error_cells():
    return make_xlsx([HEADER, *ROWS[:2], [1003, "East", dt.date(2026, 9, 2), "#N/A"]])

def formula_amounts():
    rows = [[r[0], r[1], r[2], f"={r[3]}*1"] for r in ROWS]
    return make_xlsx([HEADER, *rows])

def formatted_blank_rows():
    def style_empty_rows(wb, ws):
        for row in range(5, 40):
            ws.cell(row, 1).fill = PatternFill("solid", fgColor="FFFFF2CC")
    return make_xlsx([HEADER, *ROWS], extra=style_empty_rows)

def hidden_sheet_first():
    def add_hidden(wb, ws):
        lookup = wb.create_sheet("Lookup", 0)
        lookup.append(["code", "name"])
        lookup.sheet_state = "hidden"
    return make_xlsx([HEADER, *ROWS], extra=add_hidden)

Building a workbook this small took about 1.5 ms, so a suite can create hundreds without noticing. extra lets a variant change anything the helper does not cover — styles, extra sheets, sheet state — without the helper growing a parameter for every quirk.

Step 2 — Make the code under test accept file objects

The loader below finds the header row wherever it is, drops empty rows and converts types. It reads with pd.read_excel(source, …), which accepts a path or a file object, so tests can pass io.BytesIO(data) and production code can pass a path:

Python
# loader.py — the code under test
import pandas as pd

def load_orders(source, sheet="Orders"):
    raw = pd.read_excel(source, sheet_name=sheet, header=None, dtype=object)
    is_header = raw.apply(lambda row: "Order ID" in row.values, axis=1)
    if not is_header.any():
        raise ValueError("no header row with 'Order ID' found")
    start = is_header.idxmax()
    frame = raw.iloc[start + 1:].set_axis(raw.iloc[start].tolist(), axis=1)
    frame = frame.dropna(how="all").reset_index(drop=True)
    frame["Order ID"] = frame["Order ID"].astype(int)
    frame["Order date"] = pd.to_datetime(frame["Order date"], errors="coerce")
    frame["Amount"] = pd.to_numeric(frame["Amount"], errors="coerce")
    return frame

Code that insists on a path — it calls os.path.getsize or opens the file itself — is harder to test. Accepting "anything read_excel accepts" is usually a one-line change.

Step 3 — Look at what each variant does to pandas

Before writing assertions, load every variant once and look. Several behave in ways worth pinning down in a test:

Python
import io
import pandas as pd

variants = [clean, title_rows, dates_as_text, numbers_as_text, error_cells,
            formula_amounts, formatted_blank_rows, hidden_sheet_first]
for build in variants:
    frame = load_orders(io.BytesIO(build()))
    print(f"{build.__name__:22} rows={len(frame)} amount_sum={frame['Amount'].sum():.1f} "
          f"missing_amounts={frame['Amount'].isna().sum()}")
What each variant simulates, and what the loader has to do Title rows simulate report headers above the table; the loader searches for the header row. Dates and numbers as text simulate CSV-like exports; the loader converts them. Error cells such as N/A read as missing values. Formulas written by openpyxl have no stored results and read as missing. Styled empty rows and a hidden first sheet simulate formatting leftovers and lookup tabs; the loader drops empty rows and reads the sheet by name. Simulates Loader must title_rows report title above the table find the header row dates / numbers as text text exports convert types error_cells #N/A from a failed lookup treat as missing formula_amounts formulas without results cannot compute: NaN formatted_blank_rows styled empty rows drop empty rows hidden_sheet_first lookup tab before data read the sheet by name

Two results surprise people. Error cells such as #N/A come back as missing values, not as the text "#N/A". And the formula variant loads with every amount missing: openpyxl writes =120.5*1 into the cell but no result, and pandas reads the stored result. A test built on generated formulas therefore tests nothing — write values, or recalculate the file first as described in Recalculate Excel Formulas Without Excel in Python.

Step 4 — Run one test across every layout

pytest.mark.parametrize runs the same assertions against each builder; ids uses the function names, so the report reads like a list of layouts:

Python
# test_loader.py
import io

import pandas as pd
import pytest

import xlsx_fixtures as fx
from loader import load_orders

@pytest.mark.parametrize("build", [fx.clean, fx.title_rows, fx.dates_as_text,
                                   fx.numbers_as_text, fx.formatted_blank_rows,
                                   fx.hidden_sheet_first], ids=lambda f: f.__name__)
def test_awkward_layouts_load_the_same(build):
    frame = load_orders(io.BytesIO(build()))
    assert frame["Order ID"].tolist() == [1001, 1002, 1003]
    assert frame["Amount"].sum() == pytest.approx(240.5)
    assert frame["Order date"].dt.day.tolist() == [1, 2, 2]

def test_error_cells_become_missing():
    frame = load_orders(io.BytesIO(fx.error_cells()))
    assert frame["Amount"].isna().tolist() == [False, False, True]

def test_formulas_written_by_openpyxl_have_no_values():
    frame = load_orders(io.BytesIO(fx.formula_amounts()))
    assert frame["Amount"].isna().all()           # no cached results in the file

def test_missing_header_is_reported():
    with pytest.raises(ValueError, match="no header row"):
        load_orders(io.BytesIO(fx.make_xlsx([["just", "text"]])))

def test_loader_accepts_a_path(tmp_path):
    path = tmp_path / "orders.xlsx"
    path.write_bytes(fx.clean())
    assert len(load_orders(path)) == 3
Bash
pytest -v test_loader.py
# test_loader.py::test_awkward_layouts_load_the_same[clean] PASSED
# test_loader.py::test_awkward_layouts_load_the_same[title_rows] PASSED
# test_loader.py::test_awkward_layouts_load_the_same[dates_as_text] PASSED
# …
# 10 passed in 0.31s

The formula test is deliberately a test of a limitation: it documents that generated formulas have no values, so nobody later "fixes" the loader to handle a case the fixture cannot produce. The last test writes the bytes to tmp_path for the one path-based entry point — generate in memory, touch the disk only where the code requires it. Assertions on the output workbook are covered in Test Excel Output with pytest.

Sample files in the repository versus fixtures built in code Committed sample workbooks are real and quick to add once, but their contents are invisible in code review, they cannot be diffed and variants multiply as copies. Fixtures built in code show their data in the test, diff cleanly, turn a new variant into one function and took 4.5 ms against 4.8 ms through a file, so speed is not the trade-off. Real exports still catch quirks nobody thought to generate. Committed .xlsx samples real quirks from the source system − contents invisible in review − binary diffs − variants multiply as copies Built in memory data visible in the test one function per variant clean diffs 4.5 ms vs 4.8 ms via a file

Step 5 — Share the builders across test files

Once several test modules need workbooks, expose the helper as a pytest fixture in conftest.py. A fixture that returns a function — the "factory as fixture" pattern — lets each test build exactly the rows it needs, while pytest takes care of finding it:

Python
# conftest.py
import io
import pytest
import xlsx_fixtures

@pytest.fixture
def orders_upload():
    def build(rows=None, **options):
        data = xlsx_fixtures.make_xlsx(rows or [xlsx_fixtures.HEADER, *xlsx_fixtures.ROWS], **options)
        return io.BytesIO(data)
    return build

# in any test module:
# def test_title_rows(orders_upload):
#     frame = load_orders(orders_upload(title_rows=["Orders export"]))

The test states only what differs from the default — here, one title row — which keeps each test short and makes its intent obvious in review.

Step 6 — Keep a few real files, and name them well

Generated fixtures only contain the quirks you thought of. Keep a handful of genuine exports from each source system — trimmed to a few rows, with personal data removed — and name them after what makes them special: erp_export_merged_header.xlsx, bank_statement_with_totals_row.xlsx. When a new bug comes from a real file, reproduce it with a builder function first; keep the real file only if the builder cannot express the quirk.

Common pitfalls

  • Dates written as datetimes compare as timestamps. openpyxl stores date values as datetimes; pandas reads them as Timestamp. Compare with .dt.date or pd.Timestamp, not with datetime.date.
  • Fixtures with shared state. Build a fresh workbook in each call rather than caching one Workbook object that a test might modify.
  • Comparing bytes. openpyxl writes the save time into every file, so two builds of the same fixture are never byte-identical. Compare content.
  • Huge generated fixtures. A test that needs 100,000 rows is a performance test; mark it and keep it out of the fast suite.

Conclusion

Generate test workbooks with a small make_xlsx helper that returns bytes, name each awkward variant after its quirk, let the code under test accept file objects, and run the same assertions over every layout with pytest.mark.parametrize. Pin down the surprising behaviours — error cells and generated formulas both read as missing — and keep a few trimmed real exports for the quirks nobody predicted.

Frequently asked questions

How do I create an Excel file for a pytest test without saving it to disk? Build the workbook with openpyxl, save it to an io.BytesIO buffer and return buffer.getvalue(). pandas.read_excel and openpyxl.load_workbook both accept io.BytesIO(data), so the code under test can read it exactly as it would read a file.

Why do formulas in my generated test workbook read as NaN in pandas? openpyxl writes formulas but not their results, and pandas reads the stored result. A test workbook with =A2*2 in a cell therefore reads as NaN. Write the value instead, or recalculate the file with LibreOffice when the test really needs formula results.

Are in-memory Excel fixtures faster than files? Not noticeably. In a benchmark, building and reading a small workbook took 4.5 ms in memory and 4.8 ms through a temporary file. The benefits are elsewhere: no binary files in the repository, each test's data visible in the code, and variants that are one function call away.

Should I still keep some real Excel files in the tests? Yes, a few. Files exported by the actual source system catch quirks you would not think to generate. Keep them small, name them after what makes them special, and build everything else in memory.

More on logging, testing & packaging

More on logging, testing & packaging →