openpyxl cannot read Strict Open XML Spreadsheet format: UserWarning: File contains an invalid specification for Sheet1. This will be removed
I just posted this to Stackoverflow before realizing it would be a better fit here.
A few of my users (all of whom use Mac) have uploaded an Excel into my application, which then rejected it because the file appeared to be empty. After some debugging, I've determined that the file was saved in Strict Open XML Spreedsheet format, and that openpyxl (2.6.0) doesn't issue an error, but rather prints a warning to stderr.
To reproduce, open a file, add a few rows and save as Strict Open XML Spreedsheet (*.xlsx) format.
import openpyxl
with open('excel_open_strict.xlsx', 'rb') as f:
workbook = openpyxl.load_workbook(filename=f)
This will print the following warning, but will not throw any exception:
UserWarning: File contains an invalid specification for Sheet1. This will be removed
Furthermore, the workbook appears to have no sheets:
assert workbook.get_sheet_names() == []
assert workbook.worksheets == []
I've now had three Mac users experience this issue. It seems like Mac will sometimes default to using this Strict Open XML Spreedsheet format. If this is a normal case, then openpyxl should be able to handle it. Otherwise, it would be great if openpyxl would just throw an exception. As a workaround, it seems I can do the following:
import openpyxl with open('excel_open_strict.xlsx', 'rb') as f: workbook = openpyxl.load_workbook(filename=f)
if not workbook.get_sheet_names(): raise Exception("The Excel was saved in an incorrect format")
I would suggest that if openpyxl cannot easily support this Strict Open XML format, instead of printing a warning, it might be better to raise an Exception.
I do not own a Mac, but I have seen this issue a few times with different Mac users, so I wonder if this might be a common case.