BUG: pandas cannot open xlsx with openpyxl engine
See original GitHub issue-
[ 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
Issue Analytics
- State:
- Created 3 years ago
- Comments:8 (2 by maintainers)
Top Results From Across the Web
Pandas cannot open an Excel (.xlsx) file
Change it to 'openpyxl' ... The latest version of Pandas supports xlsx files. I had this error on 1.1.4 and after upgrading to...
Read more >How To Fix Error Pandas Cannot Open An Excel xlsx File
Let us try opening a XLSX file. ... Well there are couple of ways to fix this problem. With Pandas < 1.2 version,...
Read more >Pandas cannot open an Excel xlsx file
I tried uninstall and reinstall Pandas with the pip command. The error persists. I have xlrd 2.0.1 and Pandas 1.1.5 installed. pythonexcelpandas.
Read more >BUG: read_excel with openpyxl results in empty data frame ...
File is read perfectly and dataframe is ok, with all 11 columns. Problem description. When using openpyxl as engine for read_excel: >>> import ......
Read more >Pandas read_excel removed support for xlsx files
Pandas uses the xlrd as their default engine for reading excel files. However, xlrd has removed support for anything other than xls files...
Read more >
Top Related Medium Post
No results found
Top Related StackOverflow Question
No results found
Troubleshoot Live Code
Lightrun enables developers to add logs, metrics and snapshots to live code - no restarts or redeploys required.
Start Free
Top Related Reddit Thread
No results found
Top Related Hackernoon Post
No results found
Top Related Tweet
No results found
Top Related Dev.to Post
No results found
Top Related Hashnode Post
No results found
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.
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.