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

Read Untrusted Excel Files Safely in Python

Accepting .xlsx uploads from customers or the web? Check what the file really is, reject decompression bombs before parsing, make sure openpyxl uses defusedxml, never trust cached formula results, spot macros and external links, and parse in a separate process with memory and time limits.
Logging, testing & packagingReading Excel files

An upload form that accepts spreadsheets, an inbox that processes attachments, a partner who sends files by SFTP: each means Python code opening files someone else made. An .xlsx file is a zip archive of XML documents, and both layers can be abused — a few kilobytes that expand to gigabytes, XML that expands itself, formula results that lie, macros waiting for someone to open the file in Excel. Reading data with pandas or openpyxl does not run macros, but it does parse whatever it is given. This page builds a set of checks to run before and while parsing an untrusted workbook. It is part of Error Handling and Logging in Excel Automation.

Prerequisites

Bash
pip install pandas openpyxl defusedxml

uploads/ holds five files as a real inbox might: a normal order, an "Excel export" from a web portal that is really HTML, a decompression bomb, a workbook whose XML defines self-expanding entities, and an invoice whose formula result has been tampered with. The checks below are ordered from cheapest to most expensive; a file that fails one is rejected before the next runs.

Checks before and during parsing A pipeline of checks. First the file signature and size, then the zip contents without extracting, then parsing with defusedxml in a separate process with memory and time limits, then validating the data, including recomputing formula results rather than trusting cached ones. Signature and size what is it really? Zip inspection no extraction yet Safe parser defusedxml Sandboxed process memory and time limits Validate data recompute formulas

Step 1 — Check what the file really is

Extensions are chosen by whoever named the file. The first bytes say what it is: .xlsx, .xlsm and .xlsb files are zip archives starting with PK\x03\x04; legacy .xls files are OLE compound documents starting with D0 CF 11 E0. Web portals often serve HTML tables with an .xls or .xlsx name. Check the signature and the size before anything else:

Python
from pathlib import Path

MAX_UPLOAD = 20 * 1024 * 1024
SIGNATURES = {b"PK\x03\x04": "zip (xlsx/xlsm/xlsb)", b"\xd0\xcf\x11\xe0": "OLE (xls)"}

def sniff(path):
    size = path.stat().st_size
    if size > MAX_UPLOAD:
        return f"rejected: {size:,} bytes is over the upload limit"
    head = path.read_bytes()[:8]
    for magic, kind in SIGNATURES.items():
        if head.startswith(magic):
            return kind
    return f"rejected: not a spreadsheet (starts with {head[:6]!r})"

for f in sorted(Path("uploads").iterdir()):
    print(f"{f.name:26} {sniff(f)}")

The portal "export" starts with <html>. pandas can read HTML tables with read_html, so such files can be handled on purpose, but they should never reach an Excel parser by accident. Fix BadZipFile When Reading an Excel File in Python covers the other formats that masquerade as .xlsx.

Step 2 — Inspect the zip before extracting anything

A decompression bomb is small on disk and huge in memory: bomb.xlsx passed the size check but one of its parts expands to tens of megabytes of whitespace, and crafted files go far beyond that. The zip directory records every part's uncompressed size, so it can be checked without decompressing anything:

Python
import zipfile

MAX_TOTAL = 50 * 1024 * 1024       # uncompressed bytes across all parts
MAX_RATIO = 100                    # uncompressed / compressed

def inspect_zip(path):
    with zipfile.ZipFile(path) as z:
        infos = z.infolist()
        total = sum(i.file_size for i in infos)
        packed = sum(i.compress_size for i in infos) or 1
        worst = max(infos, key=lambda i: i.file_size / max(i.compress_size, 1))
        names = [i.filename for i in infos]
    problems = []
    if total > MAX_TOTAL:
        problems.append(f"expands to {total:,} bytes")
    if total / packed > MAX_RATIO:
        problems.append(f"compression ratio {total / packed:,.0f}:1 ({worst.filename})")
    if any(n.startswith("xl/vbaProject") for n in names):
        problems.append("contains macros")
    if any(n.startswith("xl/externalLinks/") for n in names):
        problems.append("links to external workbooks")
    return problems

for f in sorted(Path("uploads").glob("*.xlsx")):
    if sniff(f).startswith("zip"):
        print(f"{f.name:26} {inspect_zip(f) or 'ok'}")

Tune the limits to your real files: a normal workbook compresses about 5 to 20 times; ratios in the hundreds or thousands are a strong signal. Macros are not a threat to pandas, which never runs them, but a file with macros that is later opened in Excel by a colleague is; flag it, and see Extract VBA Macro Code from an Excel File to review it without opening Excel.

Step 3 — Make sure the XML parser is hardened

XML can define entities that expand into other entities; nested a few levels deep, a few hundred bytes become gigabytes (the "billion laughs" attack). openpyxl uses the defusedxml package when it is installed, which refuses entity definitions. It is an optional dependency, so check it is present in the environment that processes uploads:

Python
import openpyxl

print("openpyxl uses defusedxml:", openpyxl.DEFUSEDXML)
try:
    pd.read_excel("uploads/entities.xlsx", engine="openpyxl")
except ValueError as e:
    print("refused:", str(e).splitlines()[0])
    print("cause:", type(e.__cause__).__name__ if e.__cause__ else None)

With defusedxml installed, openpyxl refuses the file. Without it, the same file is read and the entities are expanded: the harmless three-level example here turns into a 600-character cell, and a hostile one would exhaust memory. Add defusedxml to the project's requirements, and assert openpyxl.DEFUSEDXML at start-up so a missing package fails loudly instead of silently lowering the protection.

The same file with and without defusedxml Two panels. With defusedxml installed, openpyxl refuses a workbook that defines XML entities and pandas raises an error. Without it, the entities are expanded while parsing, which a hostile file uses to exhaust memory. defusedxml installed entity definitions refused ValueError, file rejected openpyxl.DEFUSEDXML is True Not installed entities expanded memory grows with nesting no warning

Step 4 — Don't trust cached formula results

An .xlsx file stores each formula together with its last calculated result, and pandas, like openpyxl with data_only=True, reads the stored result. Nothing guarantees that result matches the formula. invoice.xlsx says Total = B2*C2, with a quantity of 3 and a price of 2.50, and a stored result of 7,500:

Python
from openpyxl import load_workbook

print(pd.read_excel("uploads/invoice.xlsx").to_string(index=False))
ws = load_workbook("uploads/invoice.xlsx").active
print("formula in D2:", ws["D2"].value)

For any figure that matters, read the inputs and recompute in Python rather than trusting the file's arithmetic:

Python
inv = pd.read_excel("uploads/invoice.xlsx")
inv["Total (recomputed)"] = inv["Qty"] * inv["Price"]
mismatch = inv[inv["Total"] != inv["Total (recomputed)"]]
print(mismatch[["Item", "Total", "Total (recomputed)"]].to_string(index=False))

Step 5 — Parse in a separate process with limits

Checks catch known problems; limits contain unknown ones. Parse each upload in a child process with a memory ceiling and a timeout, so a file that defeats the checks costs one failed job, not the whole service. On Linux and macOS, resource.setrlimit caps the child's address space:

Python
import json
import os
import resource
import subprocess
import sys

def parse_limited(path, max_mb=1024, timeout=30):
    code = ("import sys, pandas as pd; df = pd.read_excel(sys.argv[1], engine='openpyxl', nrows=100_000); "
            "print(df.to_json(orient='records'))")
    limit = max_mb * 1024 * 1024
    def cap():
        resource.setrlimit(resource.RLIMIT_AS, (limit, limit))
    try:
        out = subprocess.run([sys.executable, "-c", code, str(path)], capture_output=True, text=True,
                             timeout=timeout, preexec_fn=cap,
                             env=dict(os.environ, OPENBLAS_NUM_THREADS="1"))
    except subprocess.TimeoutExpired:
        return None, "timed out"
    if out.returncode != 0:
        return None, out.stderr.strip().splitlines()[-1][:100]
    return json.loads(out.stdout), "ok"

rows, status = parse_limited(Path("uploads/order_ok.xlsx"))
print(status, rows)

The address-space limit counts memory the process reserves, not only what it uses, and numeric libraries reserve buffers per thread when they load: with the default thread count, NumPy's OpenBLAS failed to start under a 512 MB cap here. Limiting it to one thread, as above, keeps the cap meaningful without breaking pandas. nrows bounds the work even for a legitimate but enormous sheet. On Windows, where resource is not available, run the parser in a container or a job object with a memory limit instead.

The inbox after the checks A table of the five uploaded files and the check that stopped each one. The portal export failed the signature check, the bomb failed the compression ratio check, the entities file was refused by defusedxml, the invoice parsed but its total failed recomputation, and the normal order passed every check. File Stopped by Result export_from_portal.xlsx signature rejected bomb.xlsx ratio 934:1 rejected entities.xlsx defusedxml rejected invoice.xlsx recomputation flagged order_ok.xlsx accepted

Each file was stopped by the cheapest check that could catch it, and none of the rejected files reached a full parse. Keep a record of which check rejected what; a sudden run of rejections from one sender is worth a conversation, whether it is an attack or a misconfigured export.

Common pitfalls

  • Trusting the extension or the MIME type. Both come from the sender; check the bytes.
  • Assuming defusedxml is installed. openpyxl only uses it if present; assert it.
  • Reading cached formula values as facts. Recompute totals from inputs.
  • Opening uploads in Excel "to have a look". That is the one step that can run macros; inspect with Python first.
  • Unbounded reads. Always cap rows, columns, size and time for files you did not create.

Conclusion

Treat every uploaded workbook as untrusted input. Check the file signature and size, inspect the zip directory for oversized parts, extreme compression ratios, macros and external links before extracting anything, and make sure openpyxl runs with defusedxml. Recompute important figures instead of trusting cached formula results, and parse in a separate process with memory, row and time limits so a hostile file fails alone.

Frequently asked questions

Is it safe to read an untrusted Excel file with pandas? pandas does not run macros or evaluate formulas, but it parses the zip and XML inside the file. Check size and compression first, install defusedxml for openpyxl, and parse with memory and time limits.

Does openpyxl protect against XML bombs? Only when the defusedxml package is installed; openpyxl then refuses entity definitions. Check openpyxl.DEFUSEDXML is True in the environment that processes uploads.

Can the values pandas reads from formulas be wrong? Yes. pandas reads the result stored in the file, which the file's author controls. Recalculate important figures from the input cells.

How do I detect macros in an uploaded xlsm file? Look for an xl/vbaProject.bin part in the zip, or use oletools to extract and analyse the code without opening Excel.

More on logging, testing & packaging

More on logging, testing & packaging →