Read Strict Open XML Excel Files in Python

Excel's Save As list contains two formats with the .xlsx extension: Excel Workbook, the one almost everyone uses, and Strict Open XML Spreadsheet. The second is the stricter flavour of the ISO standard behind .xlsx, and some organisations pick it for archiving or by policy. The files look identical in Explorer and open normally in Excel, but most Python libraries cannot read them. pandas with openpyxl reports ValueError: Worksheet index 0 is invalid, 0 worksheets found, openpyxl on its own loads a workbook with no sheets, and the calamine engine says Cannot detect file format. Nothing in those messages mentions Strict, so people suspect a corrupt file. This page shows why the readers fail, how to recognise a Strict workbook in two lines, how to convert it to the ordinary format in memory so pandas reads it, when to use LibreOffice instead, and how to stop such files arriving. It belongs to Handling Excel File Formats and Conversions.

Prerequisites

pip install pandas openpyxl python-calamine

LibreOffice (soffice) is used for the alternative fix in Step 4. The sample, sales_strict.xlsx, has the namespaces and conformance flag Excel writes when saving as Strict Open XML Spreadsheet: a Sales sheet with three regions.

Step 1 — Reproduce the errors

import warnings
import pandas as pd
from openpyxl import load_workbook

for engine in ["openpyxl", "calamine"]:
    try:
        pd.read_excel("sales_strict.xlsx", engine=engine)
    except Exception as e:
        print(f"{engine:<9} {type(e).__name__}: {e}")

with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    wb = load_workbook("sales_strict.xlsx")
print("openpyxl sees sheets:", wb.sheetnames, "| warning:", str(caught[0].message)[:60] if caught else None)

openpyxl opens the package — it is a valid zip — but finds no worksheets, because it looks for elements and relationships in the transitional namespaces and the Strict file uses different ones; pandas then asks for sheet 0 of an empty workbook. calamine fails earlier, unable to recognise the package at all. The same "0 worksheets found" message also appears for broken relationship paths in some generated files — a different cause with different fixes, covered in Fix "Worksheet index 0 is invalid, 0 worksheets found" — so check which one you have before fixing.

Step 2 — Recognise a Strict workbook

import zipfile

def is_strict(path):
    with zipfile.ZipFile(path) as z:
        workbook = z.read("xl/workbook.xml").decode("utf-8", "replace")
    return 'conformance="strict"' in workbook or "purl.oclc.org/ooxml" in workbook

print("sales_strict.xlsx:", is_strict("sales_strict.xlsx"), "| _transitional.xlsx:", is_strict("_transitional.xlsx"))

The workbook part of a Strict file declares conformance="strict" and uses namespaces under http://purl.oclc.org/ooxml/ instead of http://schemas.openxmlformats.org/. Both formats share the same zip layout, the same part names such as xl/workbook.xml and xl/worksheets/sheet1.xml, and the same content types, which is why file-type detection by extension or by zip contents cannot tell them apart and why the readers' errors are so unhelpful. Checking for the flag or the namespaces takes a few milliseconds and needs only the standard library, so a reading job can recognise Strict files up front and either convert them or report them clearly instead of failing with a misleading error.

Step 3 — Convert the namespaces in memory and read with pandas

For ordinary data workbooks the difference is mostly the namespace URIs, including those inside relationship types. Rewrite them back to the transitional ones in an in-memory copy and hand that to pandas:

from io import BytesIO

TO_TRANSITIONAL = {
    "http://purl.oclc.org/ooxml/spreadsheetml/main": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
    "http://purl.oclc.org/ooxml/officeDocument/relationships": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
    "http://purl.oclc.org/ooxml/drawingml/main": "http://schemas.openxmlformats.org/drawingml/2006/main",
}

def strict_to_transitional(path):
    out = BytesIO()
    with zipfile.ZipFile(path) as zin, zipfile.ZipFile(out, "w", zipfile.ZIP_DEFLATED) as zout:
        for item in zin.infolist():
            data = zin.read(item.filename)
            if item.filename.endswith((".xml", ".rels")):
                text = data.decode("utf-8")
                for strict, transitional in TO_TRANSITIONAL.items():
                    text = text.replace(strict, transitional)
                data = text.replace(' conformance="strict"', "").encode("utf-8")
            zout.writestr(item, data)
    out.seek(0)
    return out

def read_any_xlsx(path, **kwargs):
    source = strict_to_transitional(path) if is_strict(path) else path
    return pd.read_excel(source, **kwargs)

print(read_any_xlsx("sales_strict.xlsx", sheet_name="Sales").to_string(index=False))

The rewritten copy reads normally — the Sales sheet with all three regions — and the original file is untouched. Because the copy lives in a BytesIO buffer, nothing is written to disk, which matters when the input folder is read-only or shared, and the conversion costs about as much as reading the file twice. Because the relationship types are URIs under the same namespace prefix (…/relationships/worksheet, …/relationships/officeDocument), replacing the prefix fixes them too. read_any_xlsx makes the check-and-convert step invisible to the rest of a pipeline. The limits are worth stating: Strict also allows dates to be stored as ISO strings and has a few other structural differences, and workbooks with charts, pivot tables or other rich content use more namespaces than these three. For plain data sheets the rewrite is enough; for anything richer, or if the result looks wrong, use LibreOffice.

Step 4 — Convert with LibreOffice when the rewrite is not enough

LibreOffice reads Strict files fully and can write them back out as ordinary workbooks:

import subprocess

subprocess.run(["soffice", "--headless", "--convert-to", "xlsx:Calc MS Excel 2007 XML", "--outdir", "converted", "sales_strict.xlsx"],
               check=True, capture_output=True)
converted = "converted/sales_strict.xlsx"
print("strict after conversion:", is_strict(converted))
print(pd.read_excel(converted).to_string(index=False))

The Calc MS Excel 2007 XML export filter writes the ordinary transitional format, which every Python library reads; the converted file is no longer Strict and gives the same data. LibreOffice handles the content the namespace rewrite does not — ISO dates, charts, styles — at the cost of needing LibreOffice installed and taking a second or two per file. It is the right choice for batch conversion of an archive of Strict workbooks. Conversions in general are covered in Convert Excel to PDF on Linux with LibreOffice, which uses the same command with a different target.

Finding Strict files in a folder

When files arrive from many senders, scan the incoming folder with the same check and report Strict workbooks alongside the data job's other warnings. A loop over Path("inbox").glob("*.xlsx") that calls is_strict() on each file, skipping Excel's ~$ lock files, lists the senders to contact; the check reads one small part of each archive, so it is quick even for large workbooks. Logging "converted from Strict Open XML" whenever read_any_xlsx takes the conversion path also leaves a trace, so if a converted file's figures are ever questioned, the conversion is the first thing to rule out — compare with a LibreOffice conversion of the same file, which should give identical values for plain data sheets, as it did here.

Stop them arriving

Strict files usually come from one person or one system set to save in that format. In Excel, File › Save As shows the format in the type list: choosing Excel Workbook (*.xlsx) instead fixes it for that file, and File › Options › Save › Save files in this format sets the default. A system that exports Strict workbooks may have a setting for the ordinary format. Telling the sender — with the check from Step 2 in your job's error message — is usually the quickest permanent fix.

Common pitfalls

• Assuming the file is corrupt. It opens in Excel; check for Strict first.
• Confusing the two "0 worksheets" causes. Strict namespaces and broken relationship paths need different fixes.
• Rewriting rich workbooks. The namespace swap suits plain data; use LibreOffice for charts and pivots.
• Overwriting the original. Convert in memory or into a separate folder.
• Fixing every file forever. Ask the sender to save as Excel Workbook.

Conclusion

A Strict Open XML workbook uses purl.oclc.org/ooxml namespaces and a conformance="strict" flag, which makes openpyxl find no worksheets and calamine fail to detect the format. Detect it by reading xl/workbook.xml, then either rewrite the namespaces to the transitional ones in an in-memory copy and read that with pandas, or convert the file with LibreOffice's Calc MS Excel 2007 XML filter when it contains more than plain data — and ask the sender to save as Excel Workbook in future.

Frequently asked questions

What is a Strict Open XML Excel file? An .xlsx saved with Excel's Strict Open XML Spreadsheet option; it uses the strict variant of the ISO standard, with different XML namespaces from the usual Excel Workbook format.

Why does pandas say "Worksheet index 0 is invalid, 0 worksheets found" for my file? One common cause is a Strict Open XML workbook: openpyxl does not recognise its namespaces and finds no sheets. Check xl/workbook.xml for purl.oclc.org namespaces.

How do I read a Strict xlsx file with pandas? Rewrite the purl.oclc.org namespaces to the transitional schemas.openxmlformats.org ones in an in-memory copy and read that, or convert the file with LibreOffice first.

How do I stop Excel saving files as Strict Open XML? Use Save As and choose Excel Workbook (*.xlsx), and set the default format under File › Options › Save.

Related

• Up one level: Handling Excel File Formats and Conversions — converting between formats.
• Fix "Worksheet index 0 is invalid, 0 worksheets found" — the other cause of the same error.
• Fix "Excel file format cannot be determined" in pandas — when pandas cannot tell what a file is.
• Fix BadZipFile Error When Reading Excel in Python — when the file is not a zip at all.