Excel workbooks saved in "strict mode" don't show any sheets in openpyxl
openpyxl
cannot find any sheets in documents saved using Excel's "Strict Open XML Spreadsheet" mode.
The first tell-tale sign is a warning:
.../lib/python3.11/site-packages/openpyxl/reader/workbook.py:84: UserWarning: File contains an invalid specification for 0. This will be removed
warn(msg)
That seems to be triggered in WorkbookParser.find_sheets
, whenever the id
attribute of a sheet is unset:
def find_sheets(self):
"""
Find all sheets in the workbook and return the link to the source file.
Older XLSM files sometimes contain invalid sheet elements.
Warn user when these are removed.
"""
for sheet in self.sheets:
if not sheet.id:
msg = f"File contains an invalid specification for {0}. This will be removed".format(sheet.name)
warn(msg)
continue
yield sheet, self.rels[sheet.id]
Inspecting xl/workbook.xml
inside of that XLSX shows an id
attribute in the namespace http://purl.oclc.org/ooxml/officeDocument/relationships
:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook
xmlns="http://purl.oclc.org/ooxml/spreadsheetml/main"
xmlns:r="http://purl.oclc.org/ooxml/officeDocument/relationships"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x15 xr xr6 xr10 xr2"
xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"
xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"
xmlns:xr6="http://schemas.microsoft.com/office/spreadsheetml/2016/revision6"
xmlns:xr10="http://schemas.microsoft.com/office/spreadsheetml/2016/revision10"
xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" conformance="strict">
<fileVersion appName="xl" lastEdited="7" lowestEdited="7" rupBuild="10211"/>
<workbookPr dateCompatibility="0" defaultThemeVersion="202300"/>
<sheets>
<sheet name="Hello sheet" sheetId="1" r:id="rId1"/>
</sheets>
<!-- other elements removed -->
</workbook>
The xmlns
and xmlns:r
namespaces in this file appear to match an example file given in ISO/IEC 29500-1:2016 Annex A.2 ("SpreadsheetML", page 3781).
After poking at a debugger, it looks like openpyxl
expects a different namespace for relationships:
DOC_NS = "http://schemas.openxmlformats.org/officeDocument/2006/"
REL_NS = DOC_NS + "relationships"
If I re-save the workbook in Excel's "default" mode, the id
attribute is in the http://schemas.openxmlformats.org/officeDocument/2006/relationships
namespace:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x15 xr xr6 xr10 xr2"
xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main"
xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision"
xmlns:xr6="http://schemas.microsoft.com/office/spreadsheetml/2016/revision6"
xmlns:xr10="http://schemas.microsoft.com/office/spreadsheetml/2016/revision10"
xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2">
<fileVersion appName="xl" lastEdited="7" lowestEdited="7" rupBuild="10211"/>
<workbookPr defaultThemeVersion="202300"/>
<sheets>
<sheet name="Hello sheet" sheetId="1" r:id="rId1"/>
</sheets>
<!-- other elements removed -->
</workbook>
In default mode, the same sheet opens correctly, but doesn't match the example given in ISO/IEC 29500-1:2016 Annex A.2.
Steps to reproduce
-
Create a new workbook in Excel.
-
Save the workbook in
Strict Open XML Spreadsheet
format. -
Open the workbook using
openpyxl
; I'm using it through Pandas:import pandas xl = pandas.ExcelFile("Book1.xlsx", engine="openpyxl") print(f"sheet names: {repr(xl.sheet_names)}")
Expected behaviour
Should have a list of all sheets in the Excel file, eg:
sheet names: ['Hello sheet']
Actual behaviour
No sheets, and a warning:
.../lib/python3.11/site-packages/openpyxl/reader/workbook.py:84: UserWarning: File contains an invalid specification for 0. This will be removed
warn(msg)
sheet names: []
Workarounds
-
Open the files in Excel, and "Save as..." regular Excel XLSX format.
-
Patch all XML files inside the XLSX to use the
http://schemas.openxmlformats.org/
namespaces. This script seems to do the trick:
from os import PathLike
import zipfile
_STRICT_NS = b'"http://purl.oclc.org/ooxml/spreadsheetml/main"'
_STRICT_RELATIONSHIPS_NS = b'"http://purl.oclc.org/ooxml/officeDocument/relationships"'
_DEFAULT_NS = b'"http://schemas.openxmlformats.org/spreadsheetml/2006/main"'
_DEFAULT_RELATIONSHIPS_NS = (
b'"http://schemas.openxmlformats.org/officeDocument/2006/relationships"'
)
def patch(input_path: PathLike, output_path: PathLike):
zi = zipfile.ZipFile(input_path, mode="r")
# zipfile can't delete/replace: https://github.com/python/cpython/issues/51067
zo = zipfile.ZipFile(output_path, mode="x")
for inf in zi.filelist:
d = zi.read(inf)
if inf.filename.endswith(".xml"):
d = d.replace(_STRICT_NS, _DEFAULT_NS, 1).replace(
_STRICT_RELATIONSHIPS_NS, _DEFAULT_RELATIONSHIPS_NS, 1
)
zo.writestr(inf, d)
Environment
$ cat Pipfile
[[source]]
url = "https://pypi.org/simple"
verify_ssl = true
name = "pypi"
[packages]
pandas = {extras = ["excel"], version = "*"}
defusedxml = "*"
lxml = "*"
[dev-packages]
debugpy = "*"
[requires]
python_version = "3.11"
$ pipenv graph
debugpy==1.8.1
lxml==5.2.1
odfpy==1.4.1
- defusedxml [required: Any, installed: 0.7.1]
openpyxl==3.1.2
- et-xmlfile [required: Any, installed: 1.1.0]
pandas==2.2.1
- numpy [required: >=1.23.2,<2, installed: 1.26.4]
- python-dateutil [required: >=2.8.2, installed: 2.9.0.post0]
- six [required: >=1.5, installed: 1.16.0]
- pytz [required: >=2020.1, installed: 2024.1]
- tzdata [required: >=2022.7, installed: 2024.1]
python-calamine==0.2.0
pyxlsb==1.0.10
xlrd==2.0.1
XlsxWriter==3.2.0
I've installed lxml
and defusedxml
here; but the issue also appears without those packages, using the elementtree
parser.
I was creating files with Excel for Mac, 16.82 (24021116).
I can also see this in a sheet provided by a third party who is very likely using the Windows version of Excel.
The Microsoft 365 Online version of Excel doesn't have an option to save in "Strict Mode" – and appears to use the same namespaces as Excel's default mode.