2

I am trying to read an excel file with pandas read_excel function, but I keep getting the following error:

expected <class 'openpyxl.styles.fills.Fill'>

The exact code I tiped is:

corrosion_df=pd.read_excel('Corrosion.xlsx')

I already double checked the filename and it is correct. The file is also saved in the correct directory. I don't know what's going wrong because I used this method many times and until now it has always worked. Thank you very much in advance.

Alvaro Fernandez
  • 33
  • 1
  • 6

4 Answers4

2

I had the same issue, but I found when I made some changes to the spreadsheet and resaved the problem stopped.

I think the answer here is the most helpful:

Error when trying to use module load_workbook from openpyxl

My data was also being autogenerated by another site so I'm assuming there is so slight corruption in their process. I'm adding the option of csv to my project just to give an alternative.

Abi B
  • 21
  • 1
  • 6
0

I had the same problem. I just resaved the excel file.

Uralbi
  • 37
  • 2
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 06 '21 at 02:29
  • OP says `The file is also saved in the correct directory.` Why would they need to resave it? – Eric Aya Nov 06 '21 at 10:15
  • When you open excel.with pandas you dont need openpyxl. – Uralbi Nov 07 '21 at 06:01
  • I was working with pandas, and suddenly hade that error with another excel file from diff. source. ... . Sure you can open with openpyxl but this a different story, cannot handle large file. – Uralbi Nov 07 '21 at 06:06
  • Solved this issue, download all files as .xls (+ pip install xlrd), ... manage with pandas and save output as .xlsx : SO no need to open file and resave it. – Uralbi Nov 20 '21 at 03:34
0

The only way was to manually open it, save it and load it.

My workaround for it is to convert the file using libreoffice:

I ran this command line in my jupyter notebook:

!libreoffice --convert-to xls 'my_file.xlsx'

this creates a new file named my_file.xls, this file can be opened now with pandas.

import pandas as pd
df = pd.read_excel('my_file.xls')
JDArango
  • 1
  • 1
0

Good afternoon. The problem is with the styles in the table, and for some reason openpyxl doesn't want to work with them. Here is my solution which should work for you. Add the pylightxl library as a dependency, use it to get data from xlsx and then work with pandas. My latest package versions are pandas==2.0.2 and pylightxl==1.61. I hope this helps you, have a nice day.

# read excel
db = pylightxl.readxl('excel/test.xlsx')

# data sheet
name_first_sheet = db.ws_names[0]
sheet_data = list(db.ws(ws=name_first_sheet).rows)

# init dataframe
df = pd.DataFrame(data=sheet_data[1:], columns=sheet_data[0])
Fat Hare
  • 1
  • 2