Build a Command-Line Tool for Excel Reports with argparse

Q: How do I make the parser testable?

Give the function an argv parameter — parse_args(argv=None) — and call it with a list in tests. argparse reads sys.argv only when argv is None, so tests never touch the real command line.

Replace the constants at the top of your report script with real arguments: paths, month, dry-run and verbose flags, subcommands, and exit codes a scheduler can act on.

The constants at the top of a report script — SOURCE = r"C:\Reports\orders.xlsx", MONTH = "2026-07" — are the reason the script belongs to one person. Every rerun for a different month is an edit, every edit is a chance to leave the wrong value in place, and nobody else can run it at all without reading the code first.

Fifteen lines of argparse fixes that, and it does more than save typing: a script with arguments is a script a scheduler can call with different parameters, a test can drive without a subprocess, and a colleague can discover with --help. This guide is part of Testing and Packaging Excel Automation Scripts.

Prerequisites

Bash

pip install pandas openpyxl

argparse itself is in the standard library — nothing to install. The examples assume a report.py with a build_report(source, target, **options) function, as set up in the parent guide.

Step 1: A parser that types its arguments

argparse can convert values as it parses, which removes a layer of validation from your own code. type=Path gives you a real path object; a small function gives you a real date:

Python

# cli.py
import argparse
from datetime import date, datetime
from pathlib import Path


def month_arg(value):
    """Accept 2026-07 and return the first day of that month."""
    try:
        return datetime.strptime(value, "%Y-%m").date()
    except ValueError:
        raise argparse.ArgumentTypeError(
            f"expected a month like 2026-07, got {value!r}")


def build_parser():
    parser = argparse.ArgumentParser(
        prog="monthly-report",
        description="Build the monthly regional sales report from an Excel export.",
        epilog="Example: monthly-report orders.xlsx --month 2026-07 -o july.xlsx",
    )
    parser.add_argument("source", type=Path,
                        help="input workbook exported from the order system")
    parser.add_argument("-o", "--output", type=Path, default=Path("report.xlsx"),
                        help="where to write the report (default: %(default)s)")
    parser.add_argument("--sheet", default="Orders",
                        help="sheet to read from the input (default: %(default)s)")
    parser.add_argument("--month", type=month_arg, default=None,
                        help="restrict to one month, e.g. 2026-07")
    parser.add_argument("--min-amount", type=float, default=0.0,
                        help="drop rows below this value")
    parser.add_argument("--dry-run", action="store_true",
                        help="do everything except write the output file")
    parser.add_argument("-v", "--verbose", action="count", default=0,
                        help="repeat for more detail: -v, -vv")
    return parser


def parse_args(argv=None):
    return build_parser().parse_args(argv)

Three things here earn their keep. %(default)s in the help text means the defaults stay accurate when you change them. ArgumentTypeError from month_arg produces argparse's own error format — a usage line and a clear message — instead of a traceback. And parse_args(argv=None) is what makes the parser testable: argparse falls back to sys.argv only when argv is None, so a test can call parse_args(["orders.xlsx", "--month", "2026-07"]) directly.

Step 2: Turn the arguments into a run

Keep main short. Its job is to validate what argparse cannot, wire logging, call the real function, and return an exit code:

Python

import logging
import sys


def main(argv=None):
    args = parse_args(argv)

    level = [logging.WARNING, logging.INFO, logging.DEBUG][min(args.verbose, 2)]
    logging.basicConfig(level=level, format="%(levelname)s %(message)s")
    log = logging.getLogger("report")

    if not args.source.is_file():
        log.error("input workbook not found: %s", args.source)
        return 2

    try:
        summary = build_summary(args.source, sheet=args.sheet,
                                month=args.month, min_amount=args.min_amount)
    except ValueError as exc:                      # a data problem, not a crash
        log.error("cannot build the report: %s", exc)
        return 1

    if args.dry_run:
        log.warning("dry run — %d row(s) would be written to %s",
                    len(summary), args.output)
        return 0

    write_workbook(summary, args.output)
    log.info("wrote %s (%d rows)", args.output, len(summary))
    return 0


if __name__ == "__main__":
    sys.exit(main())

sys.exit(main()) is the line people leave out, and it is the one the scheduler cares about. Without it every run exits zero, including the ones that logged an error, and no monitoring can tell the difference. Returning distinct codes — 1 for a data problem, 2 for a missing input — lets an alert say which kind of failure happened before anyone opens the log. This is the same split described in Error Handling and Logging in Excel Automation.

Step 3: Add subcommands when one verb is not enough

A report tool usually grows a second job — validate the input without producing anything, or re-send yesterday's file. Subparsers keep those in one executable with one --help:

Python

def build_parser():
    parser = argparse.ArgumentParser(prog="report")
    parser.add_argument("-v", "--verbose", action="count", default=0)
    sub = parser.add_subparsers(dest="command", required=True)

    build = sub.add_parser("build", help="build the report workbook")
    build.add_argument("source", type=Path)
    build.add_argument("-o", "--output", type=Path, default=Path("report.xlsx"))
    build.set_defaults(func=cmd_build)

    check = sub.add_parser("check", help="validate the input and stop")
    check.add_argument("source", type=Path)
    check.set_defaults(func=cmd_check)

    send = sub.add_parser("send", help="email an existing report")
    send.add_argument("workbook", type=Path)
    send.add_argument("--to", action="append", required=True,
                      help="recipient; repeat for several")
    send.set_defaults(func=cmd_send)
    return parser


def main(argv=None):
    args = parse_args(argv)
    return args.func(args)          # set_defaults(func=...) does the dispatch

set_defaults(func=...) avoids a chain of if args.command == ... comparisons, and required=True on the subparsers means a bare report prints usage instead of failing later with an unhelpful AttributeError. action="append" on --to is the idiomatic way to accept a repeated flag: --to a@x.com --to b@x.com arrives as a list, which is exactly what the emailing step wants.

Step 3b: Keep --help worth reading

The help text is the only documentation most people will ever see, and argparse assembles it from what you give it. Three habits make the difference between a usage message that answers the question and one that repeats the flag names:

Name the tool with prog= rather than letting argparse use sys.argv[0], which shows as cli.py when run from source and as the executable name after packaging — two different names for the same tool in the same team's notes. Interpolate defaults with %(default)s so the help cannot drift from the code. And put a complete, runnable example in epilog=: it is the one part of a help message people read to the end, because it is the part they can paste.

Step 4: Test the parser and the run separately

Because parse_args takes a list, the parser tests need no files and no subprocess:

Python

import pytest

from cli import main, parse_args


def test_defaults():
    args = parse_args(["orders.xlsx"])
    assert args.output.name == "report.xlsx"
    assert args.sheet == "Orders"
    assert args.dry_run is False


def test_month_is_parsed_to_a_date():
    assert parse_args(["in.xlsx", "--month", "2026-07"]).month.month == 7


def test_bad_month_exits_with_usage():
    with pytest.raises(SystemExit) as exc:
        parse_args(["in.xlsx", "--month", "July"])
    assert exc.value.code == 2                # argparse's own usage-error code


def test_missing_input_returns_2(tmp_path):
    assert main([str(tmp_path / "nope.xlsx")]) == 2

main([...]) returning an integer rather than calling sys.exit is what makes that last test one line. Keep the sys.exit at the module's __main__ guard and nowhere else. The rest of the suite is covered in Test Excel Output with pytest.

Common pitfalls and gotchas

Symptom	Cause	Fix
Scheduler never reports a failure	`main()` called without `sys.exit`	`sys.exit(main())` under `__main__`
Flag value ignored when a config file is present	Merge overwrote the flag	Skip `None` values when merging layers
`--dry-run` still writes a file	The write happens before the check	Put the guard immediately before the save
Windows path argument eats the quote	A trailing backslash escapes the closing quote	Pass `"C:\Reports\"` as `"C:\Reports"` or use forward slashes
`--to a@x.com,b@x.com` sends to one odd address	Commas are not split by argparse	Use `action="append"`, or `nargs="+"`
Help text shows a stale default	Default hardcoded in the help string	Use `%(default)s`
Tests hang waiting on input	The parser read the real `sys.argv` under pytest	Always pass an explicit `argv` list

Performance and scale notes

argparse costs microseconds; it never shows up in a report's runtime. What it changes at scale is the number of scripts you maintain. One parameterised tool called from four schedule entries — one per region, say — replaces four near-identical copies that drift apart over a year. When the number of parameters passes about ten, move the stable ones into a config file and keep the command line for what varies per run; that split is covered in Keep Excel Report Settings in a Config File.

Conclusion

Give the script arguments and it stops being yours alone: --help documents it, the scheduler parameterises it, the tests drive it directly, and nobody edits a constant under time pressure. Type the arguments with type=Path and a small date converter, keep main(argv=None) returning an exit code, split the verbs into subcommands when a second job appears, and let sys.exit(main()) be the only place the process actually stops.

Frequently asked questions

Why argparse rather than click or typer? argparse is in the standard library, so a scheduled job has one less pinned dependency and a packaged executable stays smaller. click and typer are nicer for large tools; for a report script with six flags the difference is not worth the install.

How do I make the parser testable? Give the function an argv parameter — parse_args(argv=None) — and call it with a list in tests. argparse reads sys.argv only when argv is None, so tests never touch the real command line.

What exit code should a failed report return? Anything non-zero, and ideally distinct codes for distinct causes — 1 for bad input data, 2 for an infrastructure failure. cron and Task Scheduler both surface the code, so distinct values let an alert say what kind of failure it was.

Should the output path be an argument or derived from the input? Give it a flag with a sensible default. Deriving it entirely means two runs with different filters overwrite each other; requiring it every time makes the common case tedious.

Up to the parent guide:

Testing and Packaging Excel Automation Scripts — where the command line fits among tests, config and packaging.

Related guides:

Keep Excel Report Settings in a Config File — for the settings that should not be flags.
Package a Python Excel Script as an EXE with PyInstaller — shipping the finished command to someone without Python.
Run a Python Excel Script on Windows Task Scheduler — where the exit codes are read.
Log Python Excel Script Output to a File — wiring -v into a log the scheduler keeps.