Troubleshooting Common Python Excel Errors

Every Python developer who automates Excel meets the same short list of errors, usually at the worst moment: a scheduled job dies at 06:00 with zipfile.BadZipFile, or a workbook your script wrote yesterday now greets a colleague with "we found a problem with some content". The messages look cryptic because the exception is raised several layers below the code you wrote — in a zip reader, an XML parser, or an engine you never named. This guide is a triage map for those failures: what each message actually means, how to confirm the cause in one line, and which fix to apply. It sits inside Getting Started with Python Excel Automation because the errors here are the ones that interrupt the very first script you write, long before performance or formatting matter.

Read the traceback from the bottom up, then identify the file

Python prints the exception last, and that final line is the only part written for you rather than for the library author. Two pieces of information settle most cases: the exception type, and which module raised it. zipfile means the container never opened. openpyxl.utils.exceptions means the container opened but the contents were not what openpyxl expected. pandas.io.excel means pandas could not pick an engine. xml.etree or lxml means a part inside the workbook is malformed.

Before changing any code, confirm what the file actually is. Extensions lie constantly, because export tools rename files without converting them:

"""Identify a spreadsheet by its magic bytes, not its extension."""
from pathlib import Path

SIGNATURES = {
    b"PK\x03\x04": "Zip container — .xlsx, .xlsm, .xlsb or .ods",
    b"\xd0\xcf\x11\xe0": "OLE2 compound file — legacy .xls (or an encrypted workbook)",
    b"<?xml": "XML text — a SpreadsheetML file, not a real .xlsx",
    b"<html": "HTML text — an export renamed to .xls",
    b"\xef\xbb\xbf": "UTF-8 text with a byte-order mark — almost certainly CSV",
}

def identify(path: str) -> str:
    head = Path(path).read_bytes()[:8]
    for magic, label in SIGNATURES.items():
        if head.startswith(magic):
            return label
    if head[:1].isalpha() or head[:1] in (b'"', b","):
        return "Plain text — CSV/TSV with a spreadsheet extension"
    return f"Unrecognised: {head!r}"

print(identify("report.xlsx"))

Run that against the failing file first. A surprising share of "openpyxl is broken" reports end here: the file is a CSV, an HTML table, or a legacy .xls wearing an .xlsx extension. The follow-up guide Fix "Excel file format cannot be determined" in pandas works through that specific message end to end.

When the container will not open at all

zipfile.BadZipFile: File is not a zip file is the single most common Python Excel error, and it has nothing to do with Excel. An .xlsx is a zip archive; when the bytes on disk are not a zip, the read fails before any spreadsheet logic runs. Three causes account for nearly all of it:

The third case is the nastiest because it is intermittent. A file appears on a network share the instant its first byte lands, so a cron job that fires on a timer can open a half-written workbook. Guard the read:

"""Only read a file once its size has stopped changing."""
import time
from pathlib import Path

def wait_until_stable(path: str, checks: int = 3, delay: float = 2.0) -> Path:
    p = Path(path)
    last = -1
    stable = 0
    while stable < checks:
        size = p.stat().st_size
        stable = stable + 1 if size == last else 0
        last = size
        time.sleep(delay)
    return p

wb_path = wait_until_stable("/mnt/share/incoming/sales.xlsx")

The full breakdown, including how to salvage data from a partially damaged archive, is in Fix BadZipFile when reading an Excel file in Python.

When the engine is wrong for the format

pandas does not read Excel itself; it delegates to an engine, and each engine handles exactly one family of formats. Choosing the wrong one produces ValueError: Excel file format cannot be determined, openpyxl does not support the old .xls file format, or a missing-dependency error naming a package you have never installed.

The practical rule: never let pandas guess when you already know. Pass the engine explicitly, and install it in the same requirements file as pandas so a fresh environment cannot drift:

import pandas as pd

df_modern = pd.read_excel("sales.xlsx", engine="openpyxl")   # pip install openpyxl
df_legacy = pd.read_excel("sales.xls", engine="xlrd")        # pip install xlrd
df_binary = pd.read_excel("sales.xlsb", engine="pyxlsb")     # pip install pyxlsb
df_open   = pd.read_excel("sales.ods", engine="odf")         # pip install odfpy
df_fast   = pd.read_excel("sales.xlsx", engine="calamine")   # pip install python-calamine

Note that xlrd dropped .xlsx support in version 2.0, which is why an old tutorial's engine="xlrd" on a modern workbook now fails. The dedicated pages Fix "openpyxl does not support the old .xls format" and Handling Excel File Formats and Conversions cover conversion rather than merely reading.

When the sheet or cell is not where you think

Once the file opens, the next class of failure is a lookup that misses. KeyError: 'Worksheet Sheet1 does not exist.' is thrown by openpyxl when the sheet name differs by a character you cannot see — a trailing space, a non-breaking space pasted from a web page, or different capitalisation. pandas raises its own ValueError: Worksheet named 'Sheet1' not found.

Never hardcode a sheet name without a fallback. Normalise instead:

from openpyxl import load_workbook

wb = load_workbook("sales.xlsx")
print(wb.sheetnames)  # ['  Q3 Summary ', 'Raw Data']

def get_sheet(wb, wanted: str):
    """Match a sheet name ignoring case and surrounding whitespace."""
    target = wanted.strip().casefold()
    for name in wb.sheetnames:
        if name.strip().casefold() == target:
            return wb[name]
    raise KeyError(f"{wanted!r} not in {wb.sheetnames}")

ws = get_sheet(wb, "q3 summary")

Its close relative is TypeError: unsupported operand type(s) for +: 'int' and 'NoneType', which appears when you sum a range that contains empty cells — openpyxl returns None, not 0, for a blank. And if the value you expect is a formula string like '=SUM(B2:B9)' rather than a number, you loaded the workbook without data_only=True; Read formula results with openpyxl data_only explains why that flag returns None on a file Excel has never opened. Fix "Worksheet does not exist" KeyError in openpyxl walks the whole lookup path.

When Excel repairs the file your script wrote

The most alarming failure produces no Python exception at all. The script finishes, the file exists, and Excel greets the recipient with "We found a problem with some content in report.xlsx". Excel is strict about a handful of rules that openpyxl and xlsxwriter will happily let you break:

• A sheet name longer than 31 characters, or containing \ / ? * [ ].
• A defined name, table name or chart reference pointing at a sheet you later deleted or renamed.
• Two Excel tables with the same name in one workbook, or a table whose range no longer holds data.
• A string written into a cell that starts with = but is not a valid formula.
• A datetime carrying a timezone, which the file format cannot represent.

A five-line guard catches the sheet-name class before it ships:

import re

INVALID = re.compile(r"[\\/?*\[\]:]")

def safe_sheet_name(name: str) -> str:
    """Return a name Excel will accept: trimmed, sanitised, at most 31 chars."""
    cleaned = INVALID.sub("-", name).strip() or "Sheet"
    return cleaned[:31]

print(safe_sheet_name("Q3 2026 / EMEA — regional breakdown"))  # 'Q3 2026 - EMEA — regional break'

The full checklist, including how to diff a repaired file against your original to find what Excel stripped, is in Fix "Excel found unreadable content" after writing with Python.

When writing fails: locks, paths and encodings

Writing has a smaller error surface, dominated by one message on Windows: PermissionError: [Errno 13] Permission denied. In practice it means the target workbook is open in Excel, which holds an exclusive lock, and the fix is either to close it or to write somewhere else and swap the file in. Handle "Permission denied" when writing Excel in Python covers the atomic-write pattern that makes a scheduled job immune to a colleague's open window.

Two more writing faults are worth pre-empting:

• FileNotFoundError on save — the directory does not exist. Path(out).parent.mkdir(parents=True, exist_ok=True) before saving.
• IllegalCharacterError from openpyxl — a string contains a control character (often \x00 or a vertical tab) that XML forbids. Strip them with re.sub(r"[\x00-\x08\x0b\x0c\x0e-\x1f]", "", value) as data comes in from a database or scraped source.

A defensive reader you can reuse everywhere

Most of the diagnostics above collapse into one helper that every job in a codebase can call. It resolves the path, refuses a file that is not the format it claims, picks the engine deliberately, and re-raises every failure with the filename attached so a 06:00 log line is actionable without a rerun.

"""open_workbook — one entry point that fails loudly and legibly."""
from pathlib import Path

import pandas as pd

ENGINES = {".xlsx": "openpyxl", ".xlsm": "openpyxl", ".xls": "xlrd",
           ".xlsb": "pyxlsb", ".ods": "odf"}

class SpreadsheetError(RuntimeError):
    """Raised with the offending path so logs never say just 'BadZipFile'."""

def read_sheet(path: str, sheet=0) -> pd.DataFrame:
    p = Path(path).expanduser().resolve()
    if not p.is_file():
        raise SpreadsheetError(f"{p} does not exist (cwd={Path.cwd()})")
    if p.stat().st_size == 0:
        raise SpreadsheetError(f"{p} is zero bytes — upstream write failed")

    head = p.read_bytes()[:4]
    suffix = p.suffix.lower()
    if suffix in (".xlsx", ".xlsm") and head[:2] != b"PK":
        raise SpreadsheetError(f"{p} is named {suffix} but is not a zip container")
    if suffix == ".xls" and head != b"\xd0\xcf\x11\xe0":
        raise SpreadsheetError(f"{p} is named .xls but is not an OLE2 file")

    try:
        return pd.read_excel(p, sheet_name=sheet, engine=ENGINES.get(suffix))
    except Exception as exc:  # noqa: BLE001 — re-raised with context below
        raise SpreadsheetError(f"{p}: {type(exc).__name__}: {exc}") from exc

Two details matter more than they look. Resolving the path and printing the working directory turns the most common deployment failure — a relative path that meant something different under cron — into a one-line diagnosis. And checking the magic bytes before handing the file to pandas converts an obscure BadZipFile deep in a zip reader into a sentence naming the file and the mismatch.

Decide which fix applies in one pass

Work the questions in that order and you will rarely need a debugger. The order matters because each answer changes what the next one means: an engine argument cannot help a file that is really a CSV, and a sheet-name fix cannot help a workbook that never opened. In a scheduled job, encode the same order as guard clauses so the log records which question failed rather than a stack trace from three libraries down.

Pin the libraries so a fixed error cannot come back

A large share of "it worked last month" reports are version drift rather than data problems. Three changes in the ecosystem break old code on upgrade, and all three surface as errors from this page:

• xlrd 2.0 removed .xlsx support entirely, so any tutorial code passing engine="xlrd" to a modern workbook now raises a format error.
• pandas 2.x made the engine lookup stricter and turned several silent fallbacks into explicit ValueErrors — code that used to guess right by luck now fails loudly.
• openpyxl 3.1 tightened validation of styles and defined names, so a workbook written by an older release can raise a warning or drop a stale name on re-save.

Record exact versions in requirements.txt (or the lock file of whichever tool you use) and install the engines alongside pandas rather than relying on whatever the base image happens to carry:

pandas==2.2.3
openpyxl==3.1.5
xlrd==2.0.1
python-calamine==0.3.1

Then make the versions visible when something does break. Logging them at start-up costs one line and removes an entire round of guesswork when a job fails only on the server:

import openpyxl
import pandas as pd

print(f"pandas {pd.__version__}, openpyxl {openpyxl.__version__}")

Key takeaways

• Identify the file by its first bytes before debugging the code; extensions are unreliable and Excel's silent repairs hide the truth.
• BadZipFile is a container problem — a wrong format, a truncated download, or a read that raced a write.
• pandas delegates to an engine per format; pass engine= explicitly and pin the dependency so environments cannot drift.
• Sheet lookups fail on invisible whitespace and case; match names defensively rather than hardcoding them.
• A workbook Excel offers to repair is usually breaking one of a few hard rules — sheet names, duplicate table names, stale defined names, or timezone-aware datetimes.
• Wrap every failure with the filename before re-raising, so a scheduled job's log tells you which input broke without a rerun.

Frequently asked questions

Why does the same file open fine in Excel but fail in Python? Excel repairs quietly. It will open an .xls file named .xlsx, tolerate a truncated download, and silently fix a damaged zip entry. Python libraries do none of that — they read the bytes literally. When Excel opens a file that openpyxl rejects, the file is almost always not the format its extension claims.

What is the fastest way to tell what a mystery file really is? Read the first bytes. A modern .xlsx starts with PK (a zip container), a legacy .xls starts with the OLE2 signature D0 CF 11 E0, and anything starting with a letter, a quote or a byte-order mark is text — CSV, TSV or HTML saved with a spreadsheet extension.

Do I need Excel installed to fix these errors? No. Every fix on this page is pure Python and runs on a headless server. You only need Excel itself when a workbook must recalculate formulas or run macros, which is the xlwings case, not a file-reading case.

Why does my script work locally and fail on the server? The three usual causes are a different library version, a relative path resolving against a different working directory, and a file that is still being written by an upload or sync client when the job starts. Pin versions, use absolute paths, and check the file size is stable before reading.

Should I catch these exceptions or let them crash the job? Catch the ones you can act on — a missing sheet, a locked output path — and re-raise with the filename attached. Let genuinely unexpected exceptions propagate so the scheduler records a failure rather than writing an empty report.

Conclusion

Python Excel errors feel opaque because they surface several layers below your code, but each layer has a small, memorable vocabulary. Establish the file's true format first, then read the exception's module to decide whether you are fighting the container, the engine, the parser or your own lookup. The five guides below take each family in turn, with the one-line diagnostic and the durable fix for every message.

Related

• Up: Getting Started with Python Excel Automation — the section this troubleshooting map belongs to, from first read to first automated report.
• Fix BadZipFile when reading an Excel file in Python — the container-level failure and how to salvage the data.
• Fix "openpyxl does not support the old .xls format" — reading and converting legacy workbooks.
• Fix "Excel file format cannot be determined" in pandas — engine selection when the extension is wrong or missing.
• Fix "Worksheet does not exist" KeyError in openpyxl — sheet lookups that miss on whitespace, case or index.
• Fix "Excel found unreadable content" after writing with Python — the rules Excel enforces on files your script produces.
• Sibling topics: Reading Excel Files with pandas and Handling Excel File Formats and Conversions — the normal paths these errors interrupt.