BUG: pandas cannot open xlsx with openpyxl engine

[newbie9188]

• [ x] I have checked that this issue has not already been reported.

• [ x] I have confirmed this bug exists on the latest version of pandas.

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

[NXTS122ND8S10.xlsx](https://github.com/pandas-dev/pandas/files/6165254/NXTS122ND8S10.xlsx)

I have this trouble that pandas cannot open any xlsx files. All of the xlsx files can be open and read by excel.

Current packages and versions in use:
pandas: v1.2.3
openpyxl: v 3.0.7
python : v 3.8.5

method 1:
excel = pd.read_excel('NXTS122ND8S10.xlsx',sheet_name='Run results',engine='openpyxl')
print(excel)

method 2:
excel = pd.read_excel(open('NXTS122ND8S10.xlsx','rb'), sheet_name ='Run results')
print(excel)

method 3:
wb = openpyxl.load_workbook('NXTS122ND8S10.xlsx')
sheet = wb.worksheets[0]
excel = pd.dataframe(sheet.values)
print(excel)

method 4:  
excel = pd.read_excel('NXTS122ND8S10.xlsx')
print(excel)

same error code:

Traceback (most recent call last):
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\base.py", line 55, in _convert
value = expected_type(value)
TypeError: Fill() takes no arguments

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "C:\Users\XXXXX\eclipse-workspace\Work Related Projects\transpose_3.py", line 107, in
excel = pd.read_excel(open(directory+''+file,'rb'), sheet_name ='Run results')
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pandas\util_decorators.py", line 299, in wrapper
return func(*args, **kwargs)
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pandas\io\excel_base.py", line 336, in read_excel
io = ExcelFile(io, storage_options=storage_options, engine=engine)
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pandas\io\excel_base.py", line 1131, in init
self._reader = self._engines[engine](self._io, storage_options=storage_options)
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pandas\io\excel_openpyxl.py", line 475, in init
super().init(filepath_or_buffer, storage_options=storage_options)
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pandas\io\excel_base.py", line 391, in init
self.book = self.load_workbook(self.handles.handle)
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\pandas\io\excel_openpyxl.py", line 486, in load_workbook
return load_workbook(
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py", line 317, in load_workbook
reader.read()
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\reader\excel.py", line 281, in read
apply_stylesheet(self.archive, self.wb)
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\styles\stylesheet.py", line 198, in apply_stylesheet
stylesheet = Stylesheet.from_tree(node)
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\styles\stylesheet.py", line 103, in from_tree
return super(Stylesheet, cls).from_tree(node)
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\serialisable.py", line 103, in from_tree
return cls(**attrib)
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\styles\stylesheet.py", line 74, in init
self.fills = fills
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\sequence.py", line 26, in set
seq = [_convert(self.expected_type, value) for value in seq]
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\sequence.py", line 26, in
seq = [_convert(self.expected_type, value) for value in seq]
File "C:\Users\XXXXX\AppData\Local\Programs\Python\Python38-32\lib\site-packages\openpyxl\descriptors\base.py", line 57, in _convert
raise TypeError('expected ' + str(expected_type))
TypeError: expected <class 'openpyxl.styles.fills.Fill'>

Problem description

[this should explain why the current behaviour is a problem and why the expected output is a better solution]

Expected Output

Output of pd.show_versions()

[paste the output of pd.show_versions() here leaving a blank line after the details tag]
pandas: v1.2.3
openpyxl: v 3.0.7
python : v 3.8.5

[rhshadrach]

Thanks for the report! I also cannot open this file directly using openpyx:

from openpyxl import load_workbook
print(load_workbook(filename='filename.xlsx'))

If I download the file, open it in Libre Office on ubuntu, and resave it as an Open Office XML spreadsheet, I can then open it with both openpyxl and pandas. It seems to me this may be an invalid excel file, but if there is an issue it would be with openpyxl.

[rhshadrach]

@newbie9188 - what tool was used to produce this excel file? If it was produced programmatically using pandas, sharing some example code that reproduces would be helpful.

[newbie9188]

Hi Richard,

The excel is generated by lab instrument and exported with the instrument software.
Thank you for the response, this will save me a lot of time in debugging by eliminating a possible error.

I'm very appreciate for your help.

[chu4276]

Hi @newbie9188 , can you share how you solve this issue? do we need to resave the file as xml file type?

[agileminor]

@newbie9188 I'm also curious how you fixed this issue as I also see these errors when trying to read in a xlsx from this ViCell unit.

[newbie9188]

I cannot fix it. my solution is use "win32.com" package to open the xlsx file via excel application and parse out the data then re-save.

[newbie9188]

I cannot fix it. my solution is use "win32.com" package to open the xlsx file via excel application and parse out the data then re-save. The reason that I cannot open the xlsx file is because the vicell instrument exported the xlsx as protected file. The analyst wants export the result on a per sample bases. If the vicell export result for multiple samples then it will be in csv format.

[agileminor]

I found a different work around - in pandas 1.3.5 (or earlier) and xlrd version < 2.0, use engine='xlrd' and the ViCell file can be opened successfully.

[dkabal14]

I have the same problem for ACL Analytics generated files.