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

Generate Realistic Test Data for Excel Reports with Faker

Build believable, reproducible test workbooks for Excel report code with Faker and pandas: seeded generators, locale-specific names and addresses, a custom provider for your own codes, related sheets with valid foreign keys, deliberate messiness such as blanks, stray spaces and text dates, unique values, and a pytest fixture that writes the file.
Logging, testing & packagingpandas

Report code is usually developed against last month's real export — which cannot go into the repository, cannot be shared with a contractor, and does not contain the awkward cases that break things in production. A small generator that produces realistic fake workbooks fixes all three: names and addresses that look like the real thing, amounts and dates in plausible ranges, sheets that reference each other correctly, and, crucially, the messiness real exports have — blank cells, stray spaces, numbers stored as text. With the Faker library and pandas, such a generator is a page of code, and seeding makes every run produce the same file so tests are repeatable. This page builds one for a customers-and-orders workbook and wires it into pytest. It belongs to Testing and Packaging Excel Automation Scripts.

Prerequisites

Bash
pip install faker pandas openpyxl pytest

Faker generates fake names, addresses, companies, dates, numbers and much more, in dozens of locales. The examples were run with Faker 40; exact generated values can change between Faker versions, so pin the version in the project if tests compare generated values literally.

Step 1 — A seeded, multi-locale generator

Create one Faker instance for the generator and seed it, so the same seed always yields the same data. Passing several locales mixes names and addresses the way an international customer list does:

Python
from faker import Faker

fake = Faker(["en_GB", "fr_FR", "de_DE", "sv_SE"])
Faker.seed(20261002)

for _ in range(4):
    print(f"{fake.company():32} {fake.name():24} {fake.city()}")

The output mixes British, French, German and Swedish companies, people and towns — complete with accents and characters such as ö and ß that catch encoding bugs. Faker.seed() seeds the shared random generator used by every instance; for independent generators in the same process, use fake.seed_instance(n) on each instead. Run the block twice and it prints the same rows, which is what makes generated files usable as test fixtures.

Step 2 — A custom provider for your own codes

Real data has identifiers with formats Faker does not know — product SKUs, account codes, region codes. A provider is a small class whose methods become new fake.<method>() calls:

Python
from faker.providers import BaseProvider

class ShopProvider(BaseProvider):
    regions = ("North", "South", "East", "West")
    categories = {"Tools": (8, 240), "Garden": (4, 120), "Safety": (2, 60)}

    def sku(self):
        return self.bothify("??-####", letters="ABCDEFGHJKLMNPRSTUVWXYZ")

    def region(self):
        return self.random_element(self.regions)

    def category_and_price(self):
        cat = self.random_element(list(self.categories))
        lo, hi = self.categories[cat]
        return cat, round(self.generator.pyfloat(min_value=lo, max_value=hi, right_digits=2), 2)

for factory in fake.factories:          # a multi-locale Faker has one generator per locale
    factory.add_provider(ShopProvider)
print(fake.sku(), fake.region(), fake.category_and_price())

With several locales, fake is a proxy over one generator per locale, and calling fake.add_provider() on it raises NotImplementedError — register the provider on each of fake.factories instead; with a single locale, fake.add_provider(ShopProvider) works directly. bothify replaces ? with a letter and # with a digit, here from an alphabet without the easily confused I, O and Q. random_element picks from a sequence using the seeded generator, so custom values are reproducible too. Price ranges per category keep amounts plausible — a test report showing a pair of gloves at 9,800 euros hides bugs instead of revealing them.

From seed to test workbook A seeded Faker instance with several locales and a custom provider generates customers and then orders that reference them. A messiness step injects blanks, spaces and text values, and the result is written as a two-sheet workbook used by tests. Seeded Faker locales + ShopProvider Customers unique ids, names, cities Orders valid customer ids, dates Mess it up blanks, spaces, text numbers test_data.xlsx same file every run

Report code joins sheets, so the fake data must join too. Generate customers first with unique IDs, then orders that only reference those IDs:

Python
import pandas as pd
from datetime import date

def customers(n):
    ids = [f"C{fake.unique.random_int(1000, 9999)}" for _ in range(n)]
    return pd.DataFrame({
        "Customer ID": ids,
        "Company": [fake.company() for _ in ids],
        "Contact": [fake.name() for _ in ids],
        "Email": [fake.company_email() for _ in ids],
        "City": [fake.city() for _ in ids],
        "Region": [fake.region() for _ in ids],
    })

def orders(cust, n, start=date(2026, 7, 1), end=date(2026, 9, 30)):
    rows = []
    for i in range(n):
        cat, price = fake.category_and_price()
        rows.append({
            "Order ID": f"SO-{10001 + i}",
            "Customer ID": fake.random_element(list(cust["Customer ID"])),
            "Order date": fake.date_between(start_date=start, end_date=end),
            "SKU": fake.sku(),
            "Category": cat,
            "Qty": fake.random_int(1, 20),
            "Unit price": price,
        })
    return pd.DataFrame(rows)

cust = customers(40)
ords = orders(cust, 400)
print(cust.shape, ords.shape, "| orphan orders:", (~ords["Customer ID"].isin(cust["Customer ID"])).sum())
print(ords["Order date"].min(), "→", ords["Order date"].max(), "| unique customer ids:", cust["Customer ID"].is_unique)

fake.unique.random_int(...) never returns the same value twice for this instance, so customer IDs are unique without checking by hand; call fake.unique.clear() between independent datasets. Orders pick customer IDs from the generated list, so every order joins to a customer — no orphans — and dates fall inside the quarter. For data that should look like a real business, skew the choices: random_element also accepts an OrderedDict of weights, which makes a few customers account for most orders, as they usually do.

Step 4 — Add the messiness real exports have

Clean fake data only tests the happy path. Real exports have blanks, stray spaces, inconsistent capitalisation, numbers stored as text and dates typed as strings. Inject a controlled amount of each, at seeded positions:

Python
def make_messy(df, rate=0.03):
    df = df.astype(object).copy()
    n = len(df)
    pick = lambda: fake.random_elements(range(n), length=max(1, int(n * rate)), unique=True)
    for i in pick():
        df.loc[i, "Unit price"] = None                                  # blank cells
    for i in pick():
        df.loc[i, "Category"] = f" {df.loc[i, 'Category'].lower()} "    # spaces and case
    for i in pick():
        df.loc[i, "Qty"] = str(df.loc[i, "Qty"])                        # number stored as text
    for i in pick():
        df.loc[i, "Order date"] = df.loc[i, "Order date"].strftime("%d/%m/%Y")   # date as text
    df.loc[fake.random_int(0, n - 1), "SKU"] = "=HYPERLINK(\"http://example.com\")"  # formula-like text
    return df

messy = make_messy(ords)
print("blank prices:", messy["Unit price"].isna().sum(),
      "| text qty:", sum(isinstance(v, str) for v in messy["Qty"]),
      "| text dates:", sum(isinstance(v, str) for v in messy["Order date"]),
      "| odd categories:", sorted(set(messy["Category"]) - {"Tools", "Garden", "Safety"}))

About 3% of rows get each problem, chosen by the seeded generator, so the same rows are messy every run and a failing test points at the same row each time. Each injected problem corresponds to a real bug report: a blank price that turns a total into NaN, " tools " that creates a fourth category in a pivot, "7" that sorts after "19", a dd/mm/yyyy string that pandas reads as text, and a formula-like value that becomes live if written back to Excel unescaped. The cleaning side of these problems is covered in Strip Whitespace and Normalise Text Columns with pandas.

A few rows of the messy Orders sheet Generated orders with injected problems. One row has a blank unit price, one has the category written as lower case with spaces, one has the quantity stored as text and one has the order date as a text string in day-month-year order. A B C D E F 1 Order ID Customer ID Order date Category Qty Unit price 2 SO-10012 C4821 2026-07-19 Tools 6 142.80 3 SO-10013 C1937 2026-08-02 Garden 3 4 SO-10014 C4821 2026-08-05 garden 11 37.15 5 SO-10015 C6604 2026-09-14 Safety '4 9.90 6 SO-10016 C2210 21/09/2026 Tools 2 88.00

Step 5 — Write the workbook and use it in pytest

Write both sheets with a layout like the real export — including, if the real one has them, a title row above the header:

Python
def write_test_workbook(path, seed=20261002, n_customers=40, n_orders=400):
    Faker.seed(seed)
    fake.unique.clear()
    cust = customers(n_customers)
    ords = make_messy(orders(cust, n_orders))
    with pd.ExcelWriter(path, engine="openpyxl") as xw:
        cust.to_excel(xw, sheet_name="Customers", index=False)
        ords.to_excel(xw, sheet_name="Orders", index=False, startrow=2)
        xw.sheets["Orders"]["A1"] = "Order export — generated test data"
    return path

a = pd.read_excel(write_test_workbook("test_a.xlsx"), sheet_name="Orders", header=2)
b = pd.read_excel(write_test_workbook("test_b.xlsx"), sheet_name="Orders", header=2)
print("same data from the same seed:", a.equals(b), "|", a.shape)

Two files written with the same seed contain identical data, so a test that fails today fails the same way tomorrow. The title row and startrow=2 mimic an export with a heading above the table, which is exactly the kind of layout detail that breaks a reader built against a tidy sample. A pytest fixture then gives each test a fresh copy in a temporary directory:

Python
# conftest.py
import pytest
from testdata import write_test_workbook

@pytest.fixture
def orders_workbook(tmp_path):
    return write_test_workbook(tmp_path / "orders.xlsx")

# test_report.py
def test_report_ignores_blank_prices(orders_workbook):
    report = build_report(orders_workbook)
    assert report["Revenue"].notna().all()

tmp_path is pytest's per-test temporary directory, so tests never share or leave behind files. Use a different seed, or several via pytest.mark.parametrize, to explore more cases; keep one fixed seed for the regression tests whose expected values you assert. Building fixtures without touching disk at all is covered in Create Excel Test Fixtures in Memory.

Real exports versus generated test data A copy of a real export contains personal data, cannot be shared or committed, and lacks rare problems. Generated data is safe to commit, reproducible from a seed, and contains every problem you choose to inject. copy of a real export personal data inside can't go in the repository only the problems it happens to have seeded Faker data safe to commit and share same file from the same seed every problem you inject

Common pitfalls

  • Unseeded generators in tests. Different data each run makes failures unreproducible.
  • Only clean data. Inject blanks, spaces and text values, or the tests prove little.
  • Implausible values. Keep amounts, dates and quantities in realistic ranges.
  • Orphan foreign keys. Generate parents first and pick child keys from them.
  • Asserting exact generated values across Faker upgrades. Pin Faker or assert on properties.

Conclusion

A seeded Faker generator turns test data from a liability into an asset: multi-locale names and addresses, a custom provider for your own codes, related sheets whose keys always join, and a controlled dose of the blanks, stray spaces, text numbers and text dates real exports contain. Write it in the same layout as the real export, generate it in a pytest fixture, and the report code is tested against realistic, shareable, reproducible workbooks.

Frequently asked questions

How do I generate fake data for Excel with Python? Use Faker to generate values such as names, companies, cities and dates in a loop, collect them in a pandas DataFrame, and write it with to_excel.

How do I make Faker produce the same data every time? Call Faker.seed with a fixed number before generating, or seed_instance on a specific Faker instance. The same seed and Faker version produce the same values.

How do I create my own fake values such as product codes? Write a class that inherits from BaseProvider with methods such as sku, using bothify or random_element, and register it with add_provider — on each generator in fake.factories when Faker was created with several locales.

How do I make test data messy like real Excel exports? After generating clean data, set a small seeded fraction of cells to blanks, add spaces or change case in text, store some numbers and dates as text, and include formula-like strings.

More on logging, testing & packaging

More on logging, testing & packaging →