Block Large or Sensitive Excel Files with a Pre-commit Hook

Repositories for Excel automation attract spreadsheets. Someone copies the real export into tests/ to reproduce a bug, saves the output of a run next to the script, or commits the 80 MB extract that the report reads. Once committed, the file is in the history for good — deleting it in a later commit does not remove it from the clones that already exist — and if it holds customer emails, card numbers or payroll data, the repository has become a place where personal data lives, with everything that implies. A pre-commit hook stops that at the source: before each commit, it checks the spreadsheets being committed and refuses the commit if any of them is too large or contains data that looks real. This page writes such a hook in Python, wires it up with the pre-commit framework and tests it in a scratch repository. It belongs to Testing and Packaging Excel Automation Scripts.

Prerequisites

pip install pre-commit openpyxl

The hook is a plain Python script in the repository, run by the pre-commit framework, which installs it as a git hook and passes it the names of the staged files that match a pattern. It works for .xlsx and .xlsm workbooks and for CSV files.

Step 1 — The checker script

"""Refuse to commit spreadsheets that are too large or look like they contain real personal data."""
import csv, re, sys
from pathlib import Path

MAX_BYTES = 2 * 1024 * 1024
ALLOWLIST_FILE = Path(".spreadsheet-allowlist")
PATTERNS = {
    "email address": re.compile(r"[\w.+-]+@[\w-]+\.[\w.-]+"),
    "UK National Insurance number": re.compile(r"\b[A-CEGHJ-PR-TW-Z]{2}\s?\d{2}\s?\d{2}\s?\d{2}\s?[A-D]\b", re.I),
    "card number": re.compile(r"\b(?:\d[ -]?){13,19}\b"),
}
SAFE_EMAIL_DOMAINS = ("example.com", "example.org", "example.net")

def luhn_ok(text):
    digits = [int(c) for c in text if c.isdigit()][::-1]
    total = sum(d if i % 2 == 0 else (d * 2 - 9 if d * 2 > 9 else d * 2) for i, d in enumerate(digits))
    return 13 <= len(digits) <= 19 and total % 10 == 0

def findings_in_text(text):
    for label, rx in PATTERNS.items():
        for m in rx.finditer(text):
            hit = m.group(0)
            if label == "email address" and hit.lower().endswith(SAFE_EMAIL_DOMAINS):
                continue
            if label == "card number" and not luhn_ok(hit):
                continue
            yield label

def cell_texts(path):
    if path.suffix.lower() == ".csv":
        with path.open(newline="", encoding="utf-8", errors="replace") as f:
            for r, row in enumerate(csv.reader(f), start=1):
                for value in row:
                    yield f"row {r}", value
        return
    from openpyxl import load_workbook
    wb = load_workbook(path, read_only=True, data_only=True)
    try:
        for ws in wb.worksheets:
            for row in ws.iter_rows():
                for cell in row:
                    if cell.value is not None:
                        yield f"{ws.title}!{cell.coordinate}", str(cell.value)
    finally:
        wb.close()

def check(path, allowed):
    problems = []
    if path.name.startswith("~$"):
        return [f"{path}: Office lock file — never commit these"]
    if path.stat().st_size > MAX_BYTES:
        problems.append(f"{path}: {path.stat().st_size / 1024**2:.1f} MB is over the {MAX_BYTES // 1024**2} MB limit")
    if str(path) in allowed:
        return problems
    seen = {}
    for where, text in cell_texts(path):
        for label in findings_in_text(text):
            seen.setdefault(label, where)
    problems += [f"{path}: contains what looks like a real {label} (first at {where})" for label, where in seen.items()]
    return problems

def main(argv):
    allowed = set(ALLOWLIST_FILE.read_text().split()) if ALLOWLIST_FILE.exists() else set()
    problems = [p for name in argv for p in check(Path(name), allowed)]
    for p in problems:
        print(p)
    if problems:
        print("\nCommit refused. Use synthetic data, or add a reviewed fixture to .spreadsheet-allowlist.")
    return 1 if problems else 0

if __name__ == "__main__":
    sys.exit(main(sys.argv[1:]))

The script receives the staged file names as arguments, checks each one and exits with status 1 if anything is wrong, which makes git abort the commit. Three checks run. Lock files — the ~$report.xlsx files Excel leaves while a workbook is open — are always refused. Files over the size limit are refused, because large spreadsheets bloat every clone forever and usually turn out to be real extracts. And the content of every cell is scanned for things that look like personal data: email addresses, UK National Insurance numbers and payment card numbers.

Pattern matching on its own produces false alarms, so each pattern is narrowed. Email addresses at example.com and its siblings are allowed, because those domains are reserved for documentation and test data. A run of thirteen to nineteen digits only counts as a card number if it passes the Luhn checksum that real card numbers satisfy, which rules out most invoice numbers and phone numbers. The message names the first cell where each kind of data appears, so the person committing can find it quickly. The hook reads workbooks in read-only mode, which keeps it fast on larger files.

Step 2 — Register it with pre-commit

repos:
  - repo: local
    hooks:
      - id: check-spreadsheets
        name: block large or sensitive spreadsheets
        entry: python tools/check_spreadsheets.py
        language: system
        files: '\.(xlsx|xlsm|csv)$'

A local repository entry runs a script that lives in the project itself, so the hook needs no download and is reviewed like any other code. language: system runs it with the Python on the developer's PATH, which in an Excel automation project is the project's virtual environment with openpyxl already installed. files limits the hook to spreadsheets, so commits that only touch Python code do not pay for it. Each developer runs pre-commit install once after cloning, which writes the git hook; the same command run in CI with pre-commit run --all-files catches anyone who has not installed it.

Step 3 — Test it in a scratch repository

set -e
rm -rf demo && mkdir demo && cd demo && git init -q && git config user.email dev@example.com && git config user.name Dev
mkdir -p tools tests/fixtures && cp ../tools/check_spreadsheets.py tools/ && cp ../.pre-commit-config.yaml .
python - <<'EOF'
from openpyxl import Workbook
wb = Workbook(); ws = wb.active
ws.append(["Customer", "Email", "Card"]); ws.append(["Synthetic Ltd", "buyer@example.com", "4111 1111 1111 1112"])
wb.save("tests/fixtures/clean.xlsx")
wb = Workbook(); ws = wb.active
ws.append(["Customer", "Email", "NI number", "Card"]); ws.append(["J Smith", "j.smith@gmail.com", "JK 12 34 56 C", "4111 1111 1111 1111"])
wb.save("tests/fixtures/real_extract.xlsx")
EOF
git add .pre-commit-config.yaml tools && git commit -q -m "add hook" && pre-commit install > /dev/null
git add tests/fixtures/clean.xlsx && git commit -q -m "clean fixture" && echo "clean fixture: committed"
git add tests/fixtures/real_extract.xlsx
git commit -q -m "real data" || echo "real extract: commit refused"

The scratch repository goes through the real sequence: the hook is installed, a fixture with synthetic data is committed without complaint, and a workbook copied from a "real" extract is refused with the reasons listed. The clean fixture passes even though it has an email address and a sixteen-digit number — the address is at example.com, and 4111 1111 1111 1112 fails the Luhn check, so it cannot be a real card number. The extract fails on all three counts: a Gmail address, a National Insurance number and 4111 1111 1111 1111, which passes the Luhn check (it is the well-known Visa test number, which is exactly why a hook cannot tell it from a real one).

The allowlist and its limits

Some fixtures legitimately need data that looks real — a test of the card-masking code needs a valid card number in its input. Those files go in .spreadsheet-allowlist, one path per line, which is committed and reviewed like code, so adding a file to it is a visible decision rather than a quiet bypass. The size limit still applies to allowlisted files. Anyone can skip all hooks with git commit --no-verify, so treat the hook as a safety net for honest mistakes rather than a security control; running the same check in CI on every pull request closes that gap, and a secret-scanning service on the hosting platform adds a second layer.

For large binary files that do belong in the repository, such as a template workbook, the answer is Git LFS rather than a higher limit — see Store Excel Files in Git with Git LFS.

Common pitfalls

• Matching every long number as a card. Apply the Luhn checksum first.
• Flagging documentation addresses. Allow example.com, example.org and example.net.
• Relying on the hook alone. --no-verify skips it; run the same check in CI.
• Deleting a committed file and thinking it is gone. It stays in history; rewrite or rotate.
• Loading workbooks fully. Use read_only=True so the hook stays fast.

Conclusion

A pre-commit hook is the cheapest place to stop real data and large spreadsheets from entering a repository. Write a small Python checker that refuses lock files and oversized files and scans cells for emails, National Insurance numbers and Luhn-valid card numbers, register it as a local hook for .xlsx, .xlsm and .csv files, keep an explicit reviewed allowlist for fixtures that need realistic values, and run the same check in CI.

Frequently asked questions

How do I stop Excel files being committed to git? Add a pre-commit hook that receives the staged spreadsheet names and exits with a non-zero status when a file is too large or contains real-looking data.

How can a script detect personal data in an Excel file? Read every cell with openpyxl in read-only mode and match the text against patterns for emails, ID numbers and card numbers, narrowing them with checks such as the Luhn checksum.

Can a developer bypass a pre-commit hook? Yes, with git commit --no-verify; run the same check in CI so the bypass is caught before merging.

What should I do if real data was already committed? Remove it from history with a tool such as git filter-repo, force-push, ask everyone to re-clone, and treat the data as exposed.

Related

• Up one level: Testing and Packaging Excel Automation Scripts — reliable, testable projects.
• Generate Realistic Test Data for Excel Reports with Faker — synthetic data for fixtures.
• Create Excel Test Fixtures in Memory — fixtures without files.
• Store Credentials for Excel Report Scripts Safely — the other thing not to commit.