I encountered an error while attempting to load a downloaded .xlsx file from a website in our project. The error message indicates a TypeError, specifically mentioning an expected class of "<class 'openpyxl.styles.fills.Fill'>". While I've come across some solutions suggesting manual resaving of the file, this isn't feasible for a large number of files (more than 20,000 in my case).  
I'm seeking a solution that doesn't require changing the versions of my libraries. However, I'm concerned about potential conflicts with other projects running on the server if I were to change library versions. In this post i will provide alternative solutions to address this error without necessitating changes to library versions.

The error "<class 'openpyxl.styles.fills.Fill'>" in Pandas with Openpyxl typically occurs when there's a compatibility issue between Pandas and the version of Openpyxl you are using. To resolve this error, you can follow these steps:

  1. Upgrade Openpyxl: First, try upgrading the Openpyxl library to the latest version. You can do this using pip:
    2. pip install openpyxl --upgrade
  2. Check Pandas Version: Ensure that you are using a version of Pandas that is compatible with the version of Openpyxl you are using. You can check your Pandas version using:
    3. import pandas as pd
print(pd.__version__)
  3. Update Pandas: If you are using an older version of Pandas, consider upgrading it to the latest version. You can do this using pip:
    4. pip install pandas --upgrade
  4. Compatibility: Verify that the version of Pandas you are using is compatible with the version of Openpyxl. You may need to refer to the documentation or release notes of both libraries to ensure compatibility.
  5. Check File Format: Ensure that you are saving your Excel file in a supported format. Sometimes, using an unsupported file format can cause compatibility issues between Pandas and Openpyxl.
  6. Downgrade Openpyxl: If upgrading doesn't resolve the issue, you can try downgrading Openpyxl to a version that is known to work well with your version of Pandas:
    7. pip install openpyxl==<version>

Solution 2:

While browsing through similar queries , we found a potential solution that doesn't require changing library versions or manually modifying files. Here's how we implemented it:

We encountered an error where the .xlsx file raised a TypeError with an expected class of "openpyxl.styles.fills.Fill" when attempting to open it. To address this, we tried the following approach:

  • Using xlwings to Open and Save the File: If the xlsx file triggers the expected TypeError when opening, we employ xlwings to open the file and save it with a new name.
  • Retry Opening the File: After saving the file with xlwings, we attempt to open it again. In most cases, the file opens successfully the second time, allowing us to read the data without encountering the previous error.

By leveraging xlwings to handle the file opening and saving process, we were able to overcome the TypeError issue and access the data as intended.

Solution 3:

If you encounter the error "expected <class 'openpyxl.styles.fills.Fill'>" while reading an Excel file using Pandas' read_excel function, you can try the following solution:

  1. Use xlwings to Open and Resave the File: Instead of directly reading the Excel file with Pandas, utilize xlwings to open the file and then save it with a new name.
  2. Retry Reading the File with Pandas: After saving the file with xlwings, attempt to read it again using Pandas' read_excel function. This retry should work without encountering the previous error.

Here's an example:

import pandas as pd
import xlwings as xw

# Open the Excel file with xlwings
xl_app = xw.App(visible=False)
wb = xw.Book('excel_file.xlsx')
wb.save('resaved_excel_file.xlsx')
xl_app.quit()

# Retry reading the file with Pandas
df = pd.read_excel('resaved_excel_file.xlsx')

By following this solution, you should be able to overcome the expected <class 'openpyxl.styles.fills.Fill'> error while reading the Excel file using Pandas.

Solution 4:

If you encounter the TypeError "expected <class 'openpyxl.styles.fills.Fill'>" while attempting to convert multiple .xlsx files to .csv format in Python, you can resolve it by following these steps:

  1. Use xlwings to Open and Resave the Files: Instead of directly converting the .xlsx files to .csv, utilize xlwings to open each file and then save them with new names.
  2. Retry Converting the Files: After saving the files with xlwings, attempt to convert them to .csv format again. This retry should work without encountering the previous error.

Here's an example code:

import pandas as pd
import xlwings as xw

# List of .xlsx files
xlsx_files = ['file1.xlsx', 'file2.xlsx', 'file3.xlsx']

for file in xlsx_files:
    # Open the .xlsx file with xlwings
    xl_app = xw.App(visible=False)
    wb = xw.Book(file)
    wb.save(f'resaved_{file}')
    xl_app.quit()

# Retry converting the files to .csv
for file in xlsx_files:
    df = pd.read_excel(f'resaved_{file}')
    df.to_csv(f'{file[:-5]}.csv', index=False)

By following this solution, you should be able to convert multiple .xlsx files to .csv format without encountering the TypeError related to <class 'openpyxl.styles.fills.Fill'>.

Read Similar Articles