Test Excel Cleaning Code with Hypothesis

Example-based tests check the inputs you thought of: a name with two spaces, a price with a comma, the file from last month. Real spreadsheets contain the inputs you did not think of — a tab pasted into a cell, a zero-width space, a number with seventeen significant digits, a cell that is empty but not missing. Property-based testing flips the approach: you state what must always be true of the output, and Hypothesis generates hundreds of inputs trying to break it, then shrinks any failure to the smallest example that still fails. For code that cleans and writes Excel data, it finds real problems within seconds. This page, part of Testing and Packaging Excel Automation Scripts, shows three that it found while writing it.

Prerequisites

pip install hypothesis pytest pandas openpyxl xlsxwriter

The tests below are ordinary pytest files, run with pytest. Every test sets derandomize=True, which makes Hypothesis generate the same sequence of examples on every run, so the failures shown here are reproducible — and so a CI run is not flaky. The failing examples quoted on this page are exactly what Hypothesis reported.

Step 1 — Properties of a cleaning function

A typical cleaning function normalises names from a spreadsheet column: collapse runs of whitespace, trim the ends, title-case. Instead of listing examples, state properties it must have for any text — no double spaces, no leading or trailing whitespace, and running it twice changes nothing:

import re
from hypothesis import given, settings, strategies as st

def clean_name(s: str) -> str:
    return re.sub(r"\s+", " ", s).strip().title()

@settings(derandomize=True, max_examples=500)
@given(st.text())
def test_no_double_spaces_or_padding(s):
    out = clean_name(s)
    assert "  " not in out
    assert out == out.strip()

@settings(derandomize=True, max_examples=500)
@given(st.text())
def test_idempotent(s):
    assert clean_name(clean_name(s)) == clean_name(s)

python -m pytest -q -p no:cacheprovider test_clean.py 2>&1 | tail -1

st.text() generates arbitrary Unicode strings — non-breaking spaces, tabs, line separators, combining accents — far beyond what anyone would type as test data. Both properties hold, because \s and strip() both understand Unicode whitespace. Idempotence is a particularly useful property for cleaning steps: a pipeline that cleans already-cleaned data must not change it again, which matters when a job is rerun on its own output.

Step 2 — A round-trip property finds three real problems

The most valuable property for Excel code is the round trip: whatever you write to a workbook should come back unchanged when you read it. State it for a column of text and let Hypothesis look for exceptions:

import io
import pandas as pd
from hypothesis import HealthCheck, given, settings, strategies as st

SLOW = [HealthCheck.too_slow]

def roundtrip(df):
    buf = io.BytesIO()
    df.to_excel(buf, index=False)
    buf.seek(0)
    return pd.read_excel(buf, dtype=str, keep_default_na=False)

@settings(derandomize=True, max_examples=200, suppress_health_check=SLOW)
@given(st.lists(st.text(max_size=20), min_size=1, max_size=5))
def test_text_roundtrip(values):
    assert roundtrip(pd.DataFrame({"name": values}))["name"].tolist() == values

python -m pytest -q -p no:cacheprovider test_roundtrip.py 2>&1 | grep -E "values=|AssertionError|failed|passed" | head -4

Hypothesis fails the test with the smallest example it can find: values=['']. A DataFrame with one empty string writes a header and a row whose only cell is empty — and an empty cell is not stored in the file at all, so reading back finds no data row. In a real report, a row of blank text cells at the end of a sheet disappears the same way. Excel has no difference between "empty string" and "no value"; decide which one your data means and convert explicitly before writing.

With empty strings excluded (min_size=1), the next run finds values=['\x1f'], a control character, which comes back as the seven characters _x001F_. The default engine, xlsxwriter, escapes control characters in the XML that way, and openpyxl returns the escape sequence as literal text when reading. Data from APIs and pasted text contains such characters more often than one would think; strip them before writing — Remove Non-Printable Characters from Excel Data shows how.

Step 3 — Numbers: the precision limit

Run the same property for floats, excluding NaN and infinity, which Excel cannot store:

import io
import math
import pandas as pd
from hypothesis import HealthCheck, given, settings, strategies as st

def roundtrip(values):
    buf = io.BytesIO()
    pd.DataFrame({"x": values}).to_excel(buf, index=False)
    buf.seek(0)
    return pd.read_excel(buf)["x"].tolist()

floats = st.floats(allow_nan=False, allow_infinity=False)
amounts = st.floats(min_value=-1e15, max_value=1e15)

@settings(derandomize=True, max_examples=150, suppress_health_check=[HealthCheck.too_slow])
@given(st.lists(floats, min_size=1, max_size=3))
def test_exact(values):
    assert roundtrip(values) == values

@settings(derandomize=True, max_examples=150, suppress_health_check=[HealthCheck.too_slow])
@given(st.lists(amounts, min_size=1, max_size=3))
def test_close(values):
    assert all(math.isclose(a, b, rel_tol=1e-14, abs_tol=0) for a, b in zip(roundtrip(values), values))

python -m pytest -q -p no:cacheprovider test_numbers.py 2>&1 | grep -E "values=|failed|passed" | head -3
python -c "import io, pandas as pd; b = io.BytesIO(); pd.DataFrame({'x': [0.1 + 0.2]}).to_excel(b, index=False); b.seek(0); print(repr(0.1 + 0.2), '->', pd.read_excel(b)['x'][0])"

The exact test fails twice over. The first failure is precision: xlsxwriter writes numbers with the 15–16 significant digits Excel itself works with, so 2.1050057130643227e+20 is stored as 2.105005713064323E+20 and comes back slightly different — and 0.30000000000000004 becomes 0.3, which is usually what people wanted anyway. The second is more alarming: a value at the very edge of the float range, -1.7976931348623155e+308, is written rounded away from zero to -1.797693134862316E+308, which is beyond the largest magnitude a Python float can hold, so reading it back makes pandas' reader crash with OverflowError. No real report contains such a number — but a sentinel value like sys.float_info.max used to mean "no limit" does. The tolerance test, restricted to amounts up to a quadrillion, passes. The property is now honest about the format's limit, and a value that must survive exactly — a 17-digit ID, a cryptographic hash — has to be written as text, as Fix Long ID Numbers Losing Digits in Excel explains.

Step 4 — Generate whole DataFrames

hypothesis.extra.pandas builds DataFrames column by column, which suits cleaning steps that take a frame. Here the property is for a function that removes rows where every cell is blank: no blank rows remain, and no non-blank row is lost or reordered:

import pandas as pd
from hypothesis import given, settings, strategies as st
from hypothesis.extra.pandas import column, data_frames

cell = st.one_of(st.none(), st.just(""), st.just("  "), st.text(min_size=1, max_size=5))

def drop_blank_rows(df):
    blank = df.apply(lambda col: col.isna() | col.astype(str).str.strip().eq("")).all(axis=1)
    return df[~blank].reset_index(drop=True)

@settings(derandomize=True, max_examples=300)
@given(data_frames([column("a", elements=cell, dtype=object), column("b", elements=cell, dtype=object)]))
def test_drop_blank_rows(df):
    out = drop_blank_rows(df)
    is_blank = lambda v: v is None or (isinstance(v, str) and not v.strip())
    assert not any(all(is_blank(v) for v in row) for row in out.itertuples(index=False))
    kept = [tuple(r) for r in df.itertuples(index=False) if not all(is_blank(v) for v in r)]
    assert [tuple(r) for r in out.itertuples(index=False)] == kept

python -m pytest -q -p no:cacheprovider test_frames.py 2>&1 | tail -1

The strategy for cell deliberately mixes None, empty strings, whitespace-only strings and real text — the four states a "blank" cell can be in after reading a workbook. The test states both halves of correctness: nothing blank survives, and everything non-blank survives in order. A function that dropped too much would pass the first half and fail the second.

Step 5 — Running property tests in CI

Hypothesis settings can live in a profile loaded by conftest.py, so local runs stay fast and CI searches harder:

import os
from hypothesis import HealthCheck, settings

settings.register_profile("dev", max_examples=50)
settings.register_profile("ci", max_examples=500, deadline=None,
                          suppress_health_check=[HealthCheck.too_slow])
settings.load_profile(os.environ.get("HYPOTHESIS_PROFILE", "dev"))

HYPOTHESIS_PROFILE=ci python -m pytest -q -p no:cacheprovider test_clean.py test_frames.py 2>&1 | tail -1

deadline=None stops Hypothesis failing tests that are slow because they write workbooks — slowness is not a bug here. When a CI run does find a failure, Hypothesis prints the minimal example and a @reproduce_failure decorator to paste into the test; add the example permanently with @example(...) so it stays covered. Hypothesis also keeps a local database of failing examples and retries them first on the next run, which is why the -p no:cacheprovider flag on these commands only affects pytest's own cache.

Common pitfalls

• Properties that restate the implementation. "Output equals re.sub(...) of input" tests nothing; state rules the output must satisfy.
• Excluding the hard cases from the strategy. Filtering out empty strings and control characters hides exactly the inputs real files contain; handle them in the code instead.
• Exact float comparison across a file boundary. Excel stores about 15 significant digits; compare with a tolerance or write as text.
• Random, unreproducible CI runs. Use derandomize=True or a fixed seed profile for pipelines where flaky tests are costly.
• Slow tests failing on deadlines. Workbook I/O is slow; set deadline=None for those tests.

Conclusion

State properties instead of examples — idempotence, no leftover whitespace, blank rows removed without losing others — and let Hypothesis's text, float and DataFrame strategies search for counterexamples. The write-then-read round trip is the property that pays for itself fastest: it showed that empty strings vanish, control characters come back as _x001F_ floats keep only about 15 significant digits, and numbers at the edge of the float range cannot be read back at all — each of which is a decision the cleaning code now makes explicitly. Keep the tests reproducible with derandomize=True and tune their effort with profiles.

Frequently asked questions

What is property-based testing? Instead of checking chosen inputs against expected outputs, you state properties that must hold for every input — such as "cleaning twice equals cleaning once" — and a library like Hypothesis generates many inputs to find a counterexample, which it then shrinks to the smallest failing case.

How do I generate pandas DataFrames with Hypothesis? Use hypothesis.extra.pandas: data_frames(column("a", elements=..., dtype=...), ...) builds frames column by column from element strategies you choose, including None and empty strings.

Why does an empty string disappear when I write it to Excel? Excel does not distinguish an empty string from an empty cell, and empty cells are not stored in the file. A row whose cells are all empty strings therefore has nothing to read back.

Why do my floats change after writing them to Excel? Excel works with about 15 significant digits, and xlsxwriter writes numbers at that precision, so the last digits of a 17-digit float are rounded. Compare with a relative tolerance, or store values that must be exact as text.

Related

• Up one level: Testing and Packaging Excel Automation Scripts — making report code dependable.
• Test Excel Output with pytest — example-based tests for workbooks.
• Fix IllegalCharacterError When Writing Excel with openpyxl — control characters with the openpyxl engine.
• Fix Floating Point Noise in Excel Output — the precision question from the other side.