Lint and Type-Check an Excel Automation Project

Excel automation scripts fail in predictable ways: a sheet name changes and read_excel returns something else, a cell holds text where a number was expected, a default argument quietly accumulates values between runs. Most of these can be found without running anything. A linter catches the risky patterns, and a type checker — given type information for pandas and openpyxl — catches the calls that would crash. This guide, part of Testing and Packaging Excel Automation Scripts, runs both on a small report module, shows what they report and how to fix each finding, and wires them into the editor and CI.

Prerequisites

pip install ruff mypy pandas-stubs types-openpyxl

pandas-stubs and types-openpyxl contain only type information. Without them mypy treats pandas and openpyxl as untyped and checks almost nothing about how you call them. Configure both tools in pyproject.toml:

[tool.ruff]
line-length = 100
src = ["src"]

[tool.ruff.lint]
select = ["E", "F", "W", "I", "B", "UP", "DTZ", "PD", "PTH", "SIM", "RUF"]

[tool.mypy]
python_version = "3.12"
files = ["src"]
warn_unused_ignores = true
disallow_untyped_defs = true
check_untyped_defs = true

The extra ruff rule sets matter for report code: B (bugbear) finds classic bugs, PD pandas pitfalls, DTZ timezone-naive timestamps and PTH string paths. disallow_untyped_defs makes mypy insist on annotated functions, which is what gives it something to check.

Step 1 — Run them on real report code

Here is a module that works on the author's machine most of the time:

import datetime
import os
import pandas as pd
from openpyxl import Workbook, load_workbook


def load_orders(path: str) -> pd.DataFrame:
    orders = pd.read_excel(path, sheet_name=None)
    return orders.dropna(how="all")


def region_totals(orders: pd.DataFrame, regions=[]) -> pd.DataFrame:
    regions.append("Unknown")
    orders.fillna({"region": "Unknown"}, inplace=True)
    return orders.groupby("region")["amount"].sum().reset_index()


def write_summary(totals: pd.DataFrame, path: str) -> None:
    wb = Workbook()
    ws = wb.active
    ws.title = "Summary"
    ws["A1"] = f"Summary"
    ws["B1"] = datetime.datetime.now()
    for row in totals.itertuples(index=False):
        ws.append(list(row))
    wb.save(path)


def grand_total(path: str) -> float:
    ws = load_workbook(path, data_only=True)["Summary"]
    total = 0.0
    for (value,) in ws.iter_rows(min_row=2, min_col=2, max_col=2, values_only=True):
        total += value
    return total


def main() -> None:
    try:
        orders = load_orders("orders.xlsx")
        write_summary(region_totals(orders), "summary.xlsx")
    except:
        print("failed")

ruff check --output-format concise src
mypy

Ruff reported seven findings and mypy sixteen errors. The interesting ones:

The first mypy error is a real bug: sheet_name=None reads every sheet into a dictionary, so .dropna would fail on the first run. mypy knows this because pandas-stubs gives read_excel separate signatures for a single sheet and for None.

Step 2 — Fix the findings

Each fix is small, and several make the code more honest about what it expects:

import datetime as dt
import logging
from pathlib import Path

import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.worksheet.worksheet import Worksheet

log = logging.getLogger(__name__)


def load_orders(path: Path) -> pd.DataFrame:
    orders = pd.read_excel(path, sheet_name="Orders")
    return orders.dropna(how="all")


def region_totals(orders: pd.DataFrame, regions: list[str] | None = None) -> pd.DataFrame:
    orders = orders.fillna({"region": "Unknown"})
    if regions is not None:
        orders = orders[orders["region"].isin([*regions, "Unknown"])]
    return orders.groupby("region")["amount"].sum().reset_index()


def write_summary(totals: pd.DataFrame, path: Path) -> None:
    wb = Workbook()
    ws = wb.active
    if not isinstance(ws, Worksheet):
        raise TypeError("new workbook has no active worksheet")
    ws.title = "Summary"
    ws["A1"] = "Summary"
    ws["B1"] = dt.datetime.now(tz=dt.UTC).replace(tzinfo=None)
    for row in totals.itertuples(index=False):
        ws.append(list(row))
    wb.save(path)


def grand_total(path: Path) -> float:
    ws = load_workbook(path, data_only=True)["Summary"]
    total = 0.0
    for (value,) in ws.iter_rows(min_row=2, min_col=2, max_col=2, values_only=True):
        if not isinstance(value, (int, float)):
            raise ValueError(f"expected a number in column B, found {value!r}")
        total += value
    return total


def main() -> int:
    try:
        orders = load_orders(Path("orders.xlsx"))
        write_summary(region_totals(orders), Path("summary.xlsx"))
    except Exception:
        log.exception("summary failed")
        return 1
    return 0

wb.active is typed as "worksheet, chartsheet or None", so the isinstance check narrows it — after that line mypy accepts ws.title, ws["A1"] and ws.append. In grand_total, a cell value can be a number, text, date, time, formula object, rich text or None; mypy listed all of them when the code added value to a float. The isinstance check both satisfies mypy and turns a text cell into a clear error message. main now logs the exception and returns an exit code instead of printing "failed".

The timestamp fix needs one more step than ruff's DTZ005 message suggests. datetime.now(tz=dt.UTC) makes the time unambiguous, but openpyxl refuses to save it: TypeError: Excel does not support timezones in datetimes. The tzinfo in the datetime/time object must be set to None. Excel cells have no time zone, so decide which zone the report shows — UTC here — and drop the tzinfo after converting, as write_summary does with .replace(tzinfo=None). The error itself is covered in Fix "Excel does not support datetimes with timezones" in pandas. Both tools then passed, and a quick run confirmed the behaviour:

import pandas as pd

orders = pd.DataFrame({"region": ["North", None, "South", "North"], "amount": [10.0, 5.0, 7.5, 2.5]})
totals = region_totals(orders)
write_summary(totals, Path("summary.xlsx"))
print(totals.to_dict("records"), grand_total(Path("summary.xlsx")))
# [{'region': 'North', 'amount': 12.5}, {'region': 'South', 'amount': 7.5}, {'region': 'Unknown', 'amount': 5.0}] 25.0

Step 3 — When the stubs are wrong

Type stubs describe the libraries imperfectly. The first version of the fix used orders.groupby("region", as_index=False)["amount"].sum(), which returns a DataFrame at runtime, but mypy reported Incompatible return value type (got "Series[Any]", expected "DataFrame") — the stubs do not model as_index=False. Three ways out, in order of preference:

1. Use an equivalent spelling the stubs understand — here .sum().reset_index(), which is what the final code does.
2. Use typing.cast(pd.DataFrame, …) when there is no such spelling, which documents the assumption.
3. As a last resort, # type: ignore[return-value] with the specific error code. warn_unused_ignores then reports the comment once a stub update makes it unnecessary.

Avoid silencing whole modules with ignore_errors; the value of the checker is in the calls you did not think about.

Step 4 — Run the checks everywhere

Checks help only if they run without anyone remembering to. Ruff's editor integration flags problems as you type; ruff check --fix applies the safe fixes, such as sorting imports and removing unused ones. A pre-commit hook runs both tools before every commit:

# .pre-commit-config.yaml
repos:
  - repo: https://github.com/astral-sh/ruff-pre-commit
    rev: v0.16.8
    hooks:
      - id: ruff
        args: [--fix]
      - id: ruff-format
  - repo: https://github.com/pre-commit/mirrors-mypy
    rev: v2.3.1
    hooks:
      - id: mypy
        additional_dependencies: [pandas-stubs, types-openpyxl]

mypy in pre-commit runs in its own environment, so the stub packages must be listed in additional_dependencies, or the hook checks against untyped pandas and passes code the command line would reject. In CI, run ruff check, ruff format --check, mypy and pytest as separate steps so a failure says which one broke; Run a Python Excel Report in GitHub Actions shows a workflow to add them to.

Common pitfalls

• Stubs out of step with the library. Pin pandas-stubs to a release matching your pandas version, or mypy reports functions that do not exist or misses ones that do.
• Annotating everything as Any. It silences the checker and removes its value. Annotate DataFrame parameters as pd.DataFrame and let the stubs do the rest.
• Types do not check columns. mypy knows an object is a DataFrame, not which columns it has. Validate columns at the boundary, for example with Validate Excel Data with pandera Schemas.
• Turning everything on at once in an old project. Hundreds of findings get ignored. Start with the defaults plus B and PD, fix them, then add rule sets one at a time.

Conclusion

Add ruff with the bugbear, pandas, timezone and pathlib rules, and mypy with pandas-stubs and types-openpyxl. On a small report module they found a guaranteed crash from read_excel(sheet_name=None), unsafe uses of wb.active and cell values, a leaking mutable default and a bare except. Narrow openpyxl's unions with isinstance, work around stub gaps with equivalent code or cast, and run both tools in the editor, in pre-commit and in CI.

Frequently asked questions

How do I type-check pandas and openpyxl code? Install mypy with pandas-stubs and types-openpyxl, then run mypy on your source folder. The stubs describe what each function returns, so mypy can tell, for example, that read_excel(..., sheet_name=None) returns a dict of DataFrames rather than a DataFrame.

Why does mypy say wb.active might be None? In openpyxl's type stubs, Workbook.active can be a worksheet, a chartsheet or None. Narrow it with if not isinstance(ws, Worksheet) raise, and mypy then accepts ws.title, ws"A1" and ws.append.

Why can't I add a cell value to a number without a type error? A cell value can be a number, text, date, time, formula object, rich text or None. mypy lists every one of those when you add it to a float. Check isinstance(value, (int, float)) first, which also turns a text cell into a clear error instead of a TypeError.

Which ruff rules are useful for pandas report scripts? Beyond the defaults, B (bugbear) catches mutable default arguments, PD flags inplace=True and other pandas pitfalls, DTZ flags timezone-naive datetime.now(), PTH suggests pathlib, and I sorts imports. They ran in about 8 milliseconds on the example.

Related

• Up one level: Testing and Packaging Excel Automation Scripts — making report code dependable.
• Test Excel Output with pytest — the tests that run next to these checks.
• Build a Command-Line Tool for Excel Reports with argparse — typed arguments at the entry point.
• Keep Excel Report Settings in a Config File — validated settings instead of constants.