Python Excel AutomationPractical guides for developers
Start learning
Guide
Automating Reporting WorkflowsDeep dive

Testing and Packaging Excel Automation Scripts

Turn a working report script into something a colleague can run: separate the transform from the I/O, test the workbook you actually wrote, add a command line, move settings into a config file, and ship it.

A report script has two lives. In the first it runs on your machine, with your paths, your file open in another window and you watching the traceback. In the second it runs at 06:00 on a server, or on a colleague's laptop, and nobody is watching. Most of the difficulty in Excel automation lives in the gap between those two — not in openpyxl or pandas, but in everything around them: the settings baked into line 12, the sheet name that changed, the library that upgraded itself, the "just run this file" instruction that assumes a Python installation.

This part of Automating Reporting Workflows covers closing that gap. It is about structure rather than spreadsheet features: splitting the report into a pure transform and a thin I/O shell so both can be tested, asserting against the workbook you actually wrote, giving the script a command line, layering configuration so the scheduled run and the ad-hoc run can differ, and packaging the result so the next person can run it without reading your code.

The four steps between a working script and one somebody else can run A script that works on your machine becomes a handover-ready tool through four additions: tests that assert the workbook's contents, a command line so it takes arguments instead of edits, configuration held outside the code, and a packaged environment with pinned dependencies. report.py works for you tests assert the sheets, headers and totals command line arguments instead of code edits config paths and recipients outside the code handover pinned + packaged Each step removes one reason the script only runs on your machine None of these change what the report contains — they change who can produce it, and how quickly a broken run explains itself.

Split the transform from the input and output

Almost every untestable report script has the same shape: one long function that reads a file, filters it, formats a workbook and saves it. There is nothing to assert against, because there is no value in the middle — everything is a side effect.

The fix is boring and pays for itself immediately. Put the calculation in a function that takes a DataFrame and returns a DataFrame, and keep reading and writing at the edges:

Python
# report.py
from pathlib import Path

import pandas as pd


def summarise(df):
    """Pure: DataFrame in, DataFrame out. No file paths, no I/O, no globals."""
    clean = df.dropna(subset=["Region", "Amount"]).copy()
    clean["Amount"] = pd.to_numeric(clean["Amount"], errors="coerce").fillna(0)
    out = (clean.groupby("Region", as_index=False)["Amount"]
                .sum()
                .sort_values("Amount", ascending=False))
    out["Share"] = out["Amount"] / out["Amount"].sum()
    return out


def build_report(source: Path, target: Path) -> Path:
    """Thin shell: read, transform, write. Keep the logic out of here."""
    df = pd.read_excel(source, sheet_name="Orders")
    summary = summarise(df)
    with pd.ExcelWriter(target, engine="openpyxl") as writer:
        summary.to_excel(writer, sheet_name="Summary", index=False)
        df.to_excel(writer, sheet_name="Detail", index=False)
    return target

summarise can now be tested with a five-row DataFrame built in the test file, in milliseconds, with no files involved at all. build_report still needs a real workbook to prove itself, but it contains almost no decisions — so the test that covers it is checking the plumbing rather than the arithmetic.

The same split makes the cleaning and validation steps reusable: a pure function can be called from a notebook, a test and the scheduled job without any of them knowing where the data came from.

Test the workbook, not the library

Once the transform is testable, the second question is what to assert about the file. The useful answer is the file's shape and contents at the boundaries — sheet names, header row, row count, a total, a number format if the number format matters. Assertions that openpyxl can set a cell value prove nothing about your report.

Three layers of tests for a report script, from fastest to slowest Many fast tests cover the pure transform functions with in-memory DataFrames. A smaller number write a real workbook to a temporary directory and assert on its sheets, headers and totals. One or two slow smoke tests run the whole job end to end against a realistic input. transform tests — dozens, milliseconds summarise(df) with a DataFrame built in the test workbook tests — a handful, seconds write to tmp_path, reopen, assert sheets and totals smoke test — one the real job, realistic input fast, precise failures names the wrong number catches the writing bugs missing sheet, shifted header catches wiring paths, arguments, permissions

A workbook test is short because pytest's tmp_path fixture hands you a clean directory per test:

Python
# tests/test_report.py
import pandas as pd
from openpyxl import load_workbook

from report import build_report, summarise


def sample_frame():
    return pd.DataFrame({
        "Region": ["North", "South", "North", None],
        "Amount": [100, 250, 50, 999],
    })


def test_summarise_totals_by_region():
    out = summarise(sample_frame())
    assert list(out["Region"]) == ["South", "North"]
    assert out.loc[out["Region"] == "North", "Amount"].item() == 150
    assert round(out["Share"].sum(), 6) == 1.0


def test_build_report_writes_expected_sheets(tmp_path):
    source = tmp_path / "orders.xlsx"
    sample_frame().to_excel(source, sheet_name="Orders", index=False)

    target = build_report(source, tmp_path / "report.xlsx")

    wb = load_workbook(target)
    assert wb.sheetnames == ["Summary", "Detail"]
    ws = wb["Summary"]
    assert [c.value for c in ws[1]] == ["Region", "Amount", "Share"]
    assert ws.max_row == 3                      # header + two regions

Three assertions cover the failures that actually happen: a renamed sheet, a header row that shifted because someone added a title, and a row count that collapsed because a filter went wrong. Test Excel Output with pytest works through fixtures, floating-point comparisons and testing formatting in detail.

Give the script a command line

The moment a second person needs to run the report, the constants at the top of the file become the problem. A command line is fifteen lines and removes the whole class of "edit line 12, run, remember to change it back" mistakes:

Python
import argparse
from pathlib import Path


def parse_args(argv=None):
    parser = argparse.ArgumentParser(description="Build the monthly regional report.")
    parser.add_argument("source", type=Path, help="input workbook (.xlsx)")
    parser.add_argument("-o", "--output", type=Path, default=Path("report.xlsx"))
    parser.add_argument("--sheet", default="Orders", help="sheet to read")
    parser.add_argument("--dry-run", action="store_true",
                        help="do everything except write the file")
    return parser.parse_args(argv)


def main(argv=None):
    args = parse_args(argv)
    if not args.source.is_file():
        raise SystemExit(f"no such input file: {args.source}")
    ...
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

Two details matter more than the argument list. parse_args(argv=None) taking an argument list makes the parser itself testable — parse_args(["in.xlsx", "--dry-run"]) needs no subprocess. And returning an exit code from main gives cron and Task Scheduler something to act on; a job that always exits zero cannot be monitored. Build a Command-Line Tool for Excel Reports with argparse covers subcommands, date arguments and exit codes.

Layer the configuration

Flags are right for the things that change per run. They are wrong for the twenty stable settings a report carries — recipients, thresholds, column mappings, the network path to the source — which belong in a file that a non-programmer can edit and that version control can track.

Four configuration layers, each overriding the one below it Defaults in code sit at the bottom, overridden by a checked-in config file, then by environment variables holding secrets, then by command-line flags for one-off runs. The value that wins is the one from the highest layer that sets it. command-line flags --month 2026-07, one run only environment variables secrets, per-machine paths config file recipients, thresholds, columns defaults in code so a bare run still works higher layer wins edited less often

The loader is a dictionary merge, in the order that gives you the precedence above:

Python
import json
import os
from pathlib import Path

DEFAULTS = {
    "sheet": "Orders",
    "output": "report.xlsx",
    "recipients": [],
    "min_amount": 0,
}


def load_config(path=None, env=os.environ, overrides=None):
    """defaults < config file < REPORT_* env vars < explicit overrides (flags)."""
    config = dict(DEFAULTS)

    if path and Path(path).is_file():
        config.update(json.loads(Path(path).read_text()))

    for key in list(config):
        env_key = f"REPORT_{key.upper()}"
        if env_key in env:
            config[key] = env[env_key]

    for key, value in (overrides or {}).items():
        if value is not None:                   # argparse default of None means "not given"
            config[key] = value
    return config

The if value is not None guard is what makes flags composable: a flag that was not passed must not overwrite the config file with None. Keeping secrets in environment variables rather than the file is the other rule worth enforcing — an SMTP password in a JSON file checked into a repository is the single most common way a reporting job leaks a credential. Keep Excel Report Settings in a Config File covers validation, per-environment files and safe defaults.

Pin what the job depends on

An Excel report is unusually sensitive to library versions. pandas has changed the default engine, the sheet_name return type and several read_excel behaviours across minor releases; openpyxl has changed how it reports empty cells and merged ranges. A scheduled job installed with an unpinned pip install pandas openpyxl is a job that upgrades itself at 06:00 one morning without being asked.

Bash
# Freeze exactly what a working run used
python -m venv .venv
.venv/bin/pip install pandas openpyxl xlsxwriter
.venv/bin/pip freeze > requirements.txt

# On the server, or the next machine
python -m venv .venv
.venv/bin/pip install -r requirements.txt

Point the scheduler at .venv/bin/python — the interpreter inside the environment — rather than activating anything, because cron and Task Scheduler run with a minimal shell that never sources your profile. An absolute path to a pinned interpreter removes the most common cause of "it runs in my terminal but not on a schedule".

Run the tests where they can stop a bad change

A suite that only runs when someone remembers is a suite that stops running. Report tests are fast — small workbooks written to a temporary directory — so they belong on every change, which in practice means a continuous-integration job of about a dozen lines:

Yaml
# .github/workflows/tests.yml
name: tests
on: [push, pull_request]
jobs:
  pytest:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-python@v5
        with:
          python-version: "3.11"
      - run: pip install -r requirements.txt -r requirements-dev.txt
      - run: pytest -q

Note what is not there: no Excel, no display, no Windows. openpyxl, pandas and xlsxwriter write and read the file format directly, so a report suite runs on an ordinary Linux runner. The exception is anything driven through xlwings, which needs a real Excel installation and cannot run here — another reason to keep the parts that need the live application as thin and as separate as possible.

Splitting the requirements in two is worth the extra file. The production install gets pandas and openpyxl and nothing else; pytest and its plugins live in the development file, so the environment the scheduled job runs in stays small and the packaged executable does not bundle a test framework.

Where a broken report is caught, and what each catch costs A failure caught by the tests on a developer's machine costs seconds. Caught in continuous integration before merge it costs minutes. Caught by the validation step in the scheduled run it costs a delayed report. Caught by the person who opens the workbook it costs their trust in the numbers. The same defect, caught at four different moments pytest, locally seconds fix it before anyone knows CI, before merge minutes the change never reaches the server the 06:00 run a late report someone is paged, nothing is wrong yet the reader a wrong decision and every later report is doubted The cost is not the bug — it is how far it travelled before anything asked whether the output was right. Which is why the same assertions belong in the suite AND in the job's own pre-delivery check.

The last panel is the one worth designing against. The assertions in the test suite — the sheets exist, the header is right, the row count is plausible, the total reconciles — are equally valid as a check inside the job itself, run against the file it just built and before it is delivered. Writing them once as a function that both the tests and the job call is the cheapest reliability work available in a reporting pipeline, and it is what Validate an Excel Report Before Sending It covers in full.

Choose a handover format

How the script reaches its user determines how much packaging work is worth doing. There are three realistic destinations and they want different things:

Three destinations for a report script and what each one needs A server you control needs only a pinned virtual environment and a scheduler entry. A colleague who has Python needs an installable package or a pipx install. A colleague with no Python needs a single packaged executable built with PyInstaller. a server you control pinned virtual environment absolute interpreter path cron or Task Scheduler simplest to update a colleague with Python pyproject.toml package console entry point pipx install from a wheel a real command on PATH a colleague without Python PyInstaller executable built per operating system config file beside the exe largest, hardest to patch

The mistake is reaching for the third option first. A packaged executable is 60 MB, has to be rebuilt on every platform you support, and turns a one-line fix into a rebuild-and-redistribute cycle. It earns its cost only when the user genuinely cannot have Python — which is common on locked-down finance desktops, and rare everywhere else. Package a Python Excel Script as an EXE with PyInstaller covers the hidden-import problems that openpyxl and pandas cause and how to keep the config file outside the bundle.

For the second destination, a minimal pyproject.toml turns the script into a named command:

Toml
[project]
name = "monthly-report"
version = "1.0.0"
requires-python = ">=3.9"
dependencies = ["pandas>=2.0", "openpyxl>=3.1"]

[project.scripts]
monthly-report = "report:main"

[build-system]
requires = ["setuptools>=68"]
build-backend = "setuptools.build_meta"

After pipx install . the colleague runs monthly-report orders.xlsx -o july.xlsx from any directory, in an isolated environment that cannot collide with anything else they have installed.

Write down how it is run

The last artefact is not code at all. A report that only one person knows how to run is fragile in a way no test catches, and the fix is a short README beside the script — not a wiki page that will be out of date within a quarter.

Four things earn their place in it: the exact command, including the interpreter path the scheduler uses; where the config file lives and which settings people normally change; what the exit codes mean; and who to contact when the answer is not in the log. Everything else — how the transform works, why a threshold is what it is — belongs in the code or its comments, where it cannot drift away from the thing it describes.

Text
Monthly regional report
=======================
Run:      /opt/reports/.venv/bin/python -m monthly_report build orders.xlsx -o july.xlsx
Config:   /etc/report/config.prod.toml   (recipients, thresholds, source path)
Secrets:  REPORT_SMTP_PASSWORD, REPORT_DB_URL — set in the systemd unit
Exit 0:   report written    Exit 1: bad input data    Exit 2: environment failure
Owner:    finance-systems@example.com

Six lines, kept next to the code, updated in the same commit as any change that invalidates them. It is the difference between a colleague being able to run the report while you are on holiday and the report simply not running that month.

Make failures explain themselves

The last packaging job is not code that runs — it is what the job says when it stops. A scheduled report that fails with KeyError: 'Region' in a log nobody reads costs a morning; the same failure reported as "Orders sheet is missing the Region column — got Area, Amount, Date" costs a minute. Validate the inputs at the boundary, name the file and the sheet in every message, and let error handling and logging turn the exit code into an alert someone actually receives.

That is the whole point of the work on this page. Tests catch the failures before the job runs, the command line and config remove the failures caused by editing code under time pressure, and packaging decides who is able to fix the rest.

Key takeaways

  • Separate the transform from the I/O. A function that takes a DataFrame and returns a DataFrame can be tested in milliseconds; a function that reads, computes and saves cannot be tested at all.
  • Assert on the workbook's shape. Sheet names, header row, row counts and a total catch the failures that happen. Testing that openpyxl writes cells tests openpyxl.
  • Build fixtures in the test. tmp_path plus five lines of pandas beats a checked-in sample file that nobody dares regenerate.
  • Give the script a command line. Arguments instead of code edits, a testable parse_args(argv), and a real exit code so the scheduler can tell success from failure.
  • Layer configuration. Defaults, then a config file, then environment variables for secrets, then flags — and never let an unset flag overwrite the file.
  • Pin the dependencies and point the scheduler at the pinned interpreter. Unpinned pandas and openpyxl upgrades are a real and recurring cause of overnight report failures.
  • Package for the destination. A pinned environment for your own server, an installable package for a colleague with Python, an executable only when there is no Python at all.

Frequently asked questions

What is actually worth testing in a report script? The transform and the shape of the output. Assert that the numbers a function returns are right, then assert that the saved workbook has the sheets, headers and row count you expect. Testing that openpyxl can write a cell tests openpyxl, not your report.

Do I need real Excel files as test fixtures? Rarely. Build the fixture inside the test with a few lines of openpyxl or pandas and write it to pytest's tmp_path. A generated fixture is readable, has no licensing or privacy problem, and cannot silently rot when someone edits the checked-in file.

Should the script read its settings from a config file or command-line flags? Both, layered. Defaults in code, overridden by a config file, overridden by environment variables, overridden by flags. The scheduled run gets a stable file; the person debugging gets a flag they can pass without editing anything.

Is PyInstaller the right way to hand a script to a non-technical colleague? It is the right answer when they have no Python and must run it on their own desktop. If the script runs on a server you control, a pinned virtual environment is simpler, smaller and far easier to update.

How do I stop a report breaking when a library updates? Pin exact versions in a requirements file and install with the same file everywhere. openpyxl and pandas both change behaviour between minor versions — an unpinned scheduled job upgrades itself at the worst moment.

Where should the tests run? On every change, locally, and ideally in CI before the job is deployed. A report suite is fast — it writes small workbooks to a temporary directory — so there is no reason to run it only when something has already gone wrong.