Validate an Uploaded Excel File Before Processing It in a Web App

Check an uploaded workbook in layers, cheapest first — the bytes, the zip container, then the rows — so renamed CSVs, old .xls files, password-protected workbooks, macros, external links and compression bombs are refused in under a millisecond, and data errors come back as a list of exact cells the user can fix.

Excel in web apps Validation & data quality

A web app that accepts spreadsheets receives whatever people have: CSVs renamed to .xlsx, workbooks from 2003, password-protected files, macro-enabled templates, and occasionally something built to hurt. Passing all of it straight to pd.read_excel produces either a stack trace or a confusing message — or, for a compression bomb, a worker stuck for half a minute. This guide, part of Serving Excel Files from Python Web Apps, validates uploads in layers, from the cheapest checks to the most thorough, and returns problems the user can act on. Every check was run against real examples of each bad file.

Prerequisites

Bash

pip install pandas openpyxl

A sample upload with the kinds of mistakes people make — a mistyped region, "n/a" in a number column, a negative amount and a duplicated order number:

Python

import io
import zipfile

import pandas as pd
from openpyxl.utils import get_column_letter

orders = pd.DataFrame({
    "Order ID": [1001, 1002, 1003, 1002],
    "Region": ["North", "South", "Nrth", "East"],
    "Order date": pd.to_datetime(["2026-09-01", "2026-09-02", "2026-09-02", "2026-09-03"]),
    "Amount": [120.5, "n/a", 80.0, -5.0],
})
buffer = io.BytesIO()
orders.to_excel(buffer, index=False, sheet_name="Orders")
upload = buffer.getvalue()

Step 1 — Recognise the file from its bytes and zip directory

An .xlsx is a zip archive, and a zip archive lists every part with its compressed and uncompressed size before anything is extracted. That makes a whole class of checks nearly free:

Python

OLE_MAGIC = b"\xd0\xcf\x11\xe0\xa1\xb1\x1a\xe1"
ZIP_MAGIC = b"PK\x03\x04"

def inspect_container(data, max_unpacked=100_000_000, max_ratio=100, max_entries=2_000):
    """Return a list of problems found without parsing any sheet."""
    if data.startswith(OLE_MAGIC):
        if "EncryptionInfo".encode("utf-16-le") in data:
            return ["the workbook is password-protected; remove the password and upload it again"]
        return ["this is an old .xls workbook; save it as .xlsx and upload it again"]
    if not data.startswith(ZIP_MAGIC):
        return ["this is not an Excel workbook (.xlsx); is it a CSV or a renamed file?"]
    try:
        archive = zipfile.ZipFile(io.BytesIO(data))
    except zipfile.BadZipFile:
        return ["the file is damaged or incomplete; try uploading it again"]
    entries = archive.infolist()
    names = {e.filename for e in entries}
    problems = []
    if "[Content_Types].xml" not in names or "xl/workbook.xml" not in names:
        return ["the file is a zip archive but not an Excel workbook"]
    unpacked = sum(e.file_size for e in entries)
    if len(entries) > max_entries or unpacked > max_unpacked or unpacked > max_ratio * len(data):
        problems.append(f"the workbook expands to {unpacked / 1e6:.0f} MB, too large to process")
    if "xl/vbaProject.bin" in names:
        problems.append("the workbook contains macros; save it as a macro-free .xlsx")
    if any(n.startswith("xl/externalLinks/") for n in names):
        problems.append("the workbook links to other files; paste the linked values in and upload again")
    return problems

Each problem is phrased as something the user can do. Run against real files, the checks took well under a millisecond each and returned:

Password-protected workbooks deserve their own message: they are stored in the same OLE2 container format as old .xls files, so without the EncryptionInfo check a user who protected their file is told to "save it as .xlsx" — which it already is. The renamed-CSV case is the one behind most reports of pandas' "Excel file format cannot be determined", explained in Fix "Excel File Format Cannot Be Determined" in pandas. Macros and external links are not dangerous to a parser that never runs them, but a service that stores and redistributes uploads should not pass them on.

Step 2 — Refuse compression bombs before parsing

The size check in Step 1 exists because of files like this one: a 406 KB upload whose sheet XML repeats one row a million times. Its zip directory says it expands to 118 MB, a ratio of nearly 300 to 1. Given to pandas, it took 26 seconds and peaked at 570 MB of memory before returning a million rows — per request, on a web worker. Legitimate workbooks compress too, typically around 5 to 10 to 1 for data sheets, so a ratio limit of 100 leaves plenty of room while catching deliberate abuse. Set the absolute limit from the largest real file you expect.

Test the check against the sample upload and two quick fakes:

Python

print(inspect_container(upload))
print(inspect_container(b"Order ID,Region\n1001,North\n"))

with zipfile.ZipFile(io.BytesIO(upload)) as src:
    macro_buffer = io.BytesIO()
    with zipfile.ZipFile(macro_buffer, "w") as dst:
        for item in src.infolist():
            dst.writestr(item, src.read(item.filename))
        dst.writestr("xl/vbaProject.bin", b"macro code")
print(inspect_container(macro_buffer.getvalue()))
# []
# ['this is not an Excel workbook (.xlsx); is it a CSV or a renamed file?']
# ['the workbook contains macros; save it as a macro-free .xlsx']

Step 3 — Check the rows against a schema, cell by cell

A file that passes the container checks can still be wrong in the cells. Describe what each column must contain, check every column, and report each problem with the cell address the user will see in Excel:

Python

SCHEMA = {
    "order id": {"type": "integer", "unique": True},
    "region": {"type": "text", "allowed": {"North", "South", "East", "West"}},
    "order date": {"type": "date"},
    "amount": {"type": "number", "min": 0},
}

def validate_sheet(data, sheet="Orders", schema=SCHEMA, max_errors=50):
    try:
        frame = pd.read_excel(io.BytesIO(data), sheet_name=sheet, dtype=object,
                              keep_default_na=False, na_values=[""])      # keep "n/a" as text
    except ValueError as error:                              # e.g. missing sheet
        return [{"cell": None, "problem": str(error)}]
    columns = {str(c).strip().casefold(): i for i, c in enumerate(frame.columns)}
    missing = [name for name in schema if name not in columns]
    if missing:
        return [{"cell": None, "problem": f"missing column(s): {', '.join(missing)}"}]

    errors = []
    def add(name, index, problem):
        cell = f"{get_column_letter(columns[name] + 1)}{index + 2}"   # header is row 1
        errors.append({"cell": cell, "column": name, "problem": problem})

    for name, rule in schema.items():
        values = frame.iloc[:, columns[name]]
        for i in values.index[values.isna()]:
            add(name, i, "is empty")
        values = values.dropna()
        if rule["type"] in ("integer", "number"):
            numbers = pd.to_numeric(values, errors="coerce")
            for i in values.index[numbers.isna()]:
                add(name, i, f"expected a number, found {values[i]!r}")
            if rule["type"] == "integer":
                for i in values.index[numbers.notna() & (numbers % 1 != 0)]:
                    add(name, i, f"expected a whole number, found {values[i]!r}")
            if "min" in rule:
                for i in values.index[numbers < rule["min"]]:
                    add(name, i, f"must be at least {rule['min']}, found {values[i]!r}")
        elif rule["type"] == "date":
            dates = pd.to_datetime(values, errors="coerce")
            for i in values.index[dates.isna()]:
                add(name, i, f"expected a date, found {values[i]!r}")
        if "allowed" in rule:
            for i in values.index[~values.isin(rule["allowed"])]:
                add(name, i, f"{values[i]!r} is not one of {sorted(rule['allowed'])}")
        if rule.get("unique"):
            for i in values.index[values.duplicated(keep="first")]:
                add(name, i, f"duplicate value {values[i]!r}")
    errors.sort(key=lambda e: (int(e["cell"].lstrip("ABCDEFGHIJKLMNOPQRSTUVWXYZ")), e["cell"]))
    return errors[:max_errors]

for error in validate_sheet(upload):
    print(error["cell"], error["problem"])
# D3 expected a number, found 'n/a'
# B4 'Nrth' is not one of ['East', 'North', 'South', 'West']
# A5 duplicate value 1002
# D5 must be at least 0, found -5

Three details make the messages trustworthy. Headers are matched after trimming and case-folding, so "Order ID " and "order id" both work. Every value is read as-is with dtype=object, so type checks see what the user typed. And keep_default_na=False stops pandas turning the text "n/a" into a missing value: with the default, the first message read "expected a number, found nan", which quotes nothing the user can find. Reading every column as text-or-number and checking it yourself is the same approach as Check Excel Data Types with pandas.

Step 4 — One function for the web layer

Combine the layers into a single call that returns a result the front end can render, and stop at the first layer that fails — there is no point checking rows in a file that is not a workbook:

Python

def check_upload(filename, data, max_bytes=20_000_000):
    if len(data) > max_bytes:
        return {"accepted": False, "problems": [{"cell": None, "problem": "the file is larger than 20 MB"}]}
    problems = inspect_container(data)
    if problems:
        return {"accepted": False, "problems": [{"cell": None, "problem": p} for p in problems]}
    errors = validate_sheet(data)
    return {"accepted": not errors, "problems": errors}

result = check_upload("orders.xlsx", upload)
print(result["accepted"], len(result["problems"]))
# False 4

In Flask or FastAPI, return this with status 422 when it is not accepted; in Streamlit, show the problems with st.error and st.stop(), as in Build a Streamlit App to Upload and Download Excel Files. Receiving the upload itself — body limits, framework specifics — is covered in Handle an Uploaded Excel File in Flask and FastAPI.

Common pitfalls

Trusting the extension or the browser's MIME type. Both come from the client. Check the bytes.
Stopping at the first data error. Users fix one cell, upload again and get the next error. Report all of them, capped at a sensible number such as 50.
Row numbers off by two. pandas rows start at 0 and the header occupies row 1, so data row i is sheet row i + 2. Reporting the pandas index sends users to the wrong row.
Error messages that echo whole cells. A cell can hold thousands of characters. Truncate quoted values before putting them in a response.

Conclusion

Validate uploads in layers: recognise the file from its first bytes, inspect the zip directory for the workbook parts, macros, external links and the expanded size, and only then read the sheet with dtype=object and keep_default_na=False and check each column against a schema. Return every problem with a cell address and an instruction the user can follow.

Frequently asked questions

How do I check that an upload really is an .xlsx file? Look at the bytes, not the extension. An .xlsx starts with the zip signature PK\x03\x04 and contains Content_Types.xml and xl/workbook.xml. Old .xls files and password-protected workbooks start with the OLE2 signature D0 CF 11 E0 instead.

How can I detect a password-protected Excel upload? An encrypted .xlsx is stored inside an OLE2 container with an EncryptionInfo stream. Check for the OLE2 signature and the UTF-16 name EncryptionInfo. pandas otherwise fails with a confusing "Can't find workbook in OLE2 compound document".

What is an Excel zip bomb and how do I stop it? A small upload whose compressed sheet XML expands enormously. In a test, a 406 KB file expanded to 118 MB and took pandas 26 seconds and 570 MB of memory to read. Sum the uncompressed sizes in the zip directory before parsing and refuse files above a limit or ratio.

Why does my validator say a cell is empty when it contains "n/a"? pandas treats strings such as "n/a", "NA" and "null" as missing by default. Read with keep_default_na=False and na_values="" so those strings stay text and the error message can quote what the user actually typed.

Up one level: Serving Excel Files from Python Web Apps — Excel in and out of web applications.
Highlight Invalid Cells in Excel with Python — sending the errors back as a marked-up workbook.
Replace Placeholder Text with NaN in pandas — the other side of the "n/a" problem.
Work with Macro-Enabled .xlsm Files in openpyxl — when macros are part of the requirement.

More on excel in web apps