[FIXED] Pandas Openpyxl error "<class 'openpyxl.styles.fills.Fill'>

Issue

I'm getting this error while loading a downloaded .xlsx file from a website

TypeError: expected "<class 'openpyxl.styles.fills.Fill'>

I saw some solutions here, but they are telling me to resave the file manually, which is not an option for more than 10.000 files.

It is a normal .xlsx file without any formulas or vba, only data.

Is there a solution for this without me having to change the versions of my libraries, I had to downgrade Pandas and XLRD to the version 1.2.0 for a solution to work. Since I'm deploying to a server, changing the versions may create a conflict with other projects that I'm running.

try:
     pd.read_excel("Espelho.xlsx",  engine='openpyxl')
except Exception as e:
     print(e)

This is the file: Espelho.xlsx

**Edit**
This is known issue as described in github.com/pandas-dev/pandas/issues/40499.
The question is about how to workaround the problem and does not need additional debugging details.

Solution

I saw another similar query on github and their only solution was to open with win32.com resave or roll back pandas. Therefore something like this may do the trick without having to change versions or separately modify the files.
The example file provided was tested with this code.
If the xlsx file has an expected <class 'openpyxl.styles.fills.Fill'> exception when opening then use xlwings to open and save with new name and try opening the file again which should work second time and allow you to read the data.

import xlwings as xw
import pandas as pd


path = 'Espelho.xlsx'
path2 = "renamed_" + path

while True:
    try:
        df = pd.read_excel(path, engine='openpyxl')
    except Exception as e:
        print("Failed to open workbook; error: ")
        print(e)
        wingsbook = xw.Book(path)
        wingsapp = xw.apps.active
        wingsbook.save(path2)
        wingsapp.quit()

        path = path2
    else:
        break

for col in df.columns:
    print(col)

Answered By - moken