Python Excel AutomationPractical guides for developers
Fix
Getting Started With Python Excel AutomationDeep dive

Fix "Worksheet index 0 is invalid, 0 worksheets found"

pandas raises "ValueError: Worksheet index 0 is invalid, 0 worksheets found" when openpyxl opens an .xlsx and finds no worksheets, even though Excel shows the data. The usual cause is a file from another tool whose internal paths differ in upper and lower case. Diagnose it by comparing the workbook's relationships with the zip contents, then read it with calamine or repair the paths.
Errors & fixesReading Excel files

A file opens fine in Excel, but pandas refuses it:

Text
ValueError: Worksheet index 0 is invalid, 0 worksheets found

pandas asked openpyxl for the first worksheet and openpyxl reported that the workbook has none. The data is there; openpyxl could not find it. This almost always happens with files produced by some other system — an ERP export, a reporting tool, a web application's "download as Excel" — whose internal file paths are written in a way Excel tolerates and openpyxl does not. This page shows how to confirm that, and three ways around it. It is part of Troubleshooting Common Python Excel Errors.

Prerequisites

Bash
pip install pandas openpyxl python-calamine

erp_export.xlsx stands in for the problem file: it opens in Excel and LibreOffice with a three-row sales table, and fails in pandas with the default engine.

Step 1 — Reproduce and confirm it is "no worksheets", not a bad file

First make sure the file is a real .xlsx and see what openpyxl finds in it:

Python
import zipfile
import pandas as pd
from openpyxl import load_workbook

print("zip archive:", zipfile.is_zipfile("erp_export.xlsx"))
try:
    pd.read_excel("erp_export.xlsx")
except ValueError as e:
    print("pandas:", e)
wb = load_workbook("erp_export.xlsx")
print("openpyxl sheet names:", wb.sheetnames)
print("calamine sees:", list(pd.read_excel("erp_export.xlsx", sheet_name=None, engine="calamine")))

The same root cause produces two other messages, depending on how the file is read. With sheet_name=None, asking for every sheet, pandas raises ValueError: Sheet name is an empty list. With openpyxl directly, wb.active is None, and the first attribute access fails with AttributeError: 'NoneType' object has no attribute 'max_row', which looks like a bug in your own code rather than in the file:

Python
print(repr(wb.active))
try:
    pd.read_excel("erp_export.xlsx", sheet_name=None)
except ValueError as e:
    print("sheet_name=None:", e)

The file is a valid zip, openpyxl loads it without an error, and still lists no sheets, while a different reader sees Sheet1. That combination points at how openpyxl locates sheets. If the file is not a zip at all, the problem is different: see Fix BadZipFile When Reading an Excel File in Python.

Step 2 — Compare the relationships with the zip contents

An .xlsx file is a zip of XML parts. xl/workbook.xml lists the sheets; xl/_rels/workbook.xml.rels maps each sheet to the path of its part inside the zip. openpyxl looks up each path exactly, and silently skips any sheet whose path does not match a member of the archive. Excel matches paths without regard to case. Print both sides:

Python
import posixpath
import re

with zipfile.ZipFile("erp_export.xlsx") as z:
    names = z.namelist()
    rels = z.read("xl/_rels/workbook.xml.rels").decode()
targets = re.findall(r'Target="([^"]+)"', rels)
for t in targets:
    path = posixpath.normpath(posixpath.join("xl", t)) if not t.startswith("/") else t.lstrip("/")
    exact = path in names
    loose = [n for n in names if n.lower() == path.lower()]
    print(f"{t:26} exact match: {exact!s:5}  case-insensitive match: {loose}")

The relationship points at Worksheets/Sheet1.xml; the archive contains xl/worksheets/sheet1.xml. Excel resolves that; openpyxl does not, so the sheet is skipped and the workbook appears to have none.

How openpyxl finds a sheet, and where it loses it workbook.xml names the sheet, the relationships file gives its path, and openpyxl looks for that exact path in the zip. The relationship says Worksheets/Sheet1.xml, the zip holds worksheets/sheet1.xml, the exact lookup fails, and the sheet is skipped without a warning. workbook.xml sheet Sheet1 rId1 Relationships points to Worksheets/… Exact lookup in the zip Not found sheet skipped silently pandas 0 worksheets found

Why no warning? In openpyxl's reader, each sheet's path is checked against the list of files in the archive, and a sheet whose path is not in that list is skipped with a plain continue. That behaviour makes sense for genuinely optional parts, but for a worksheet it turns a malformed file into an apparently empty one. Knowing this is the difference between an afternoon spent looking for data that "vanished" and a two-minute diagnosis.

Other mismatches produce the same symptom: backslashes in member names written by some Windows zip libraries, or a relationship pointing at a part that is missing altogether. The comparison above shows all of them.

Fix 1 — Read it with calamine

The calamine engine resolves the parts differently and reads the file as it is. For reading data, this is the quickest fix:

Python
df = pd.read_excel("erp_export.xlsx", engine="calamine")
print(df)

Nothing else in the code has to change, so this is also the least risky fix for a scheduled job that has just started failing on a new supplier file. calamine is also considerably faster than openpyxl on large files. It reads values only, not formatting, which is all read_excel needs anyway; see Pick an Excel Engine for .xlsx, .xlsm, .xls, .xlsb and .ods.

Fix 2 — Repair the paths in the zip

If the file must go through openpyxl, for example to keep formatting or edit it, rewrite the relationships so each target matches the archive member's actual name. The repair copies every part unchanged except the relationships files:

Python
def repair_part_names(src, dst):
    with zipfile.ZipFile(src) as zi:
        names = zi.namelist()
        by_lower = {n.lower(): n for n in names}
        with zipfile.ZipFile(dst, "w", zipfile.ZIP_DEFLATED) as zo:
            for item in zi.infolist():
                data = zi.read(item.filename)
                if item.filename.endswith(".rels"):
                    base = posixpath.dirname(posixpath.dirname(item.filename))
                    def fix(m):
                        target = m.group(1)
                        full = posixpath.normpath(posixpath.join(base, target)) if not target.startswith("/") else target.lstrip("/")
                        actual = by_lower.get(full.lower())
                        if actual and actual != full:
                            return f'Target="{posixpath.relpath(actual, base or ".")}"'
                        return m.group(0)
                    data = re.sub(r'Target="([^"]+)"', fix, data.decode()).encode()
                zo.writestr(item, data)

repair_part_names("erp_export.xlsx", "erp_export_repaired.xlsx")
print(load_workbook("erp_export_repaired.xlsx").sheetnames)
print(pd.read_excel("erp_export_repaired.xlsx"))

Relationship targets are relative to the folder above the _rels folder that contains them, which is what base computes; external links such as hyperlinks do not match any member and are left alone.

The relationship before and after repair A table comparing the relationship target with the archive member. Before repair the target Worksheets/Sheet1.xml does not match the member xl/worksheets/sheet1.xml exactly. After repair the target is worksheets/sheet1.xml and matches. Target in .rels Exact match Before Worksheets/Sheet1.xml no After worksheets/sheet1.xml yes

Fix 3 — Let LibreOffice rewrite the file

When there are many such files and they need to be ordinary .xlsx files for other tools, a headless LibreOffice conversion writes a clean file with standard part names:

Bash
soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to xlsx --outdir converted erp_export.xlsx > /dev/null 2>&1
ls converted
Python
print(load_workbook("converted/erp_export.xlsx").sheetnames)

Conversion rewrites the whole file, so check that formatting and formulas you care about survive; for a data export they nearly always do.

Checking a folder of exports

When a supplier's system produces these files, every export has the same defect, and a pipeline that processes a folder should detect it up front rather than fail on the first file. Wrap the comparison from Step 2 in a function that returns the mismatched targets, and run it over the folder before reading anything:

Python
from pathlib import Path

def mismatched_parts(path):
    with zipfile.ZipFile(path) as z:
        names = set(z.namelist())
        rels = z.read("xl/_rels/workbook.xml.rels").decode()
    bad = []
    for t in re.findall(r'Target="([^"]+)"', rels):
        full = t.lstrip("/") if t.startswith("/") else posixpath.normpath(posixpath.join("xl", t))
        if full not in names:
            bad.append(t)
    return bad

for f in sorted(Path(".").glob("*.xlsx")):
    problems = mismatched_parts(f)
    print(f"{f.name:28} {'ok' if not problems else 'mismatched: ' + ', '.join(problems)}")

A pipeline can then route each file: straight to openpyxl when it is clean, through repair_part_names or calamine when it is not, and log which files needed help. That log is also the evidence to send to the supplier.

Choosing the fix A decision diagram. If you only need the data, read with calamine. If the file must be edited or its formatting kept with openpyxl, repair the relationship targets. If many files must become clean xlsx files for other tools, convert them with LibreOffice. What do you need from the file? just the data calamine engine='calamine' edit with openpyxl Repair paths rewrite .rels clean files for other tools LibreOffice convert-to xlsx

Common pitfalls

  • Assuming the file is empty. "0 worksheets found" describes what openpyxl found, not what the file contains; compare with another reader before investigating the data.
  • Patching by hand in a zip tool. Some tools re-compress members with backslashes or change their order; repair with Python's zipfile, which keeps names exactly.
  • Only fixing the first sheet. Check every relationship target, including those for shared strings and styles, which fail with a KeyError rather than silently.
  • Reporting it upstream. If a supplier's system produces these files, send them the comparison output; the file does not follow the package conventions openpyxl relies on.

Conclusion

"Worksheet index 0 is invalid, 0 worksheets found" means openpyxl skipped every sheet, usually because the workbook's relationships point at part names whose case or form does not exactly match the zip's members. Confirm it by comparing relationship targets with zipfile.namelist(). Read such files with engine="calamine", repair the relationship targets with zipfile when openpyxl must open the file, or convert them with LibreOffice.

Frequently asked questions

Why does pandas say 0 worksheets found when the file opens in Excel? Excel resolves the parts inside an .xlsx case-insensitively; openpyxl requires exact matches and skips sheets it cannot find. Files from some exporters use different capitalisation in the relationships than in the zip.

How do I read an Excel file that openpyxl cannot find sheets in? Use pd.read_excel(path, engine="calamine") after installing python-calamine, which reads such files. To use openpyxl, repair the relationship targets or convert the file with LibreOffice first.

Is this the same error as "Worksheet named ... not found"? No. That one means the sheet name you asked for is not in the workbook, often because of spelling or trailing spaces. "Worksheet index 0 is invalid" means openpyxl found no worksheets at all.

Can hidden sheets cause this error? No. Hidden worksheets are still worksheets and are counted. A workbook containing only chart sheets would, since pandas counts worksheets only.

More on errors & fixes

More on errors & fixes →