Colors must be aRGB hex values?

Question

So I was previously running some code for a pollution dataset, and the code was running just fine. Now, I get this error: Colors must be aRGB hex values

The only line of code I have is the following:

pollution_jawn = pd.read_excel('ObservationData_irkfioc copy.xlsx')

I have no idea what the issue is, and I even tried deleting this file from my jupyterhub directory and uploading it, but even that did not work.

Answer 1

Had a similar issue after I updated to pandas 1.2.3. What worked for me is to specify

pd.read_excel(path_to_xlsx, engine='xlrd')

I suspect that the xlsx file I loaded used an older xls standard somewhere, since the option "xlrd supports old-style Excel files (.xls)" (from the docs)

Answer 2

As mentioned by the other answer, a workaround this is to specify engine='xlrd' in pd.read_excel. However, for this to work, xlrd must be in version 1.2.0 (or lower).

To download the specific version (using anaconda), type in your terminal conda install -c anaconda xlrd=1.2.0.

After that is done, this should work (mind that you will get a FutureWarning, as this version of xlrd is deprecated:

pollution_jawn = pd.read_excel('ObservationData_irkfioc copy.xlsx', engine='xlrd')

Answer 3

The .xlsx file was probably saved in an older version of Excel. What worked for me was to simply open the .xlsx file in a new version of Excel and then save it again. After that the error didn't turn up again.

Answer 4

Had similar issue, but found simple solution, just to reassign method set in RGB class in openpyxl/styles/colors.py, catching error with 'Colors must be aRGB hex values' and setting another color like white:

from openpyxl.styles.colors import WHITE, RGB
__old_rgb_set__ = RGB.__set__

def __rgb_set_fixed__(self, instance, value):
    try:
        __old_rgb_set__(self, instance, value)
    except ValueError as e:
        if e.args[0] == 'Colors must be aRGB hex values':
            __old_rgb_set__(self, instance, WHITE)  # Change color here

 RGB.__set__ = __rgb_set_fixed__

Hope it will help somebody

Answer 5

Not a python solution, but I opened the file with excel and removed all colors (font and background). The resulting file could be opened without trouble.

Answer 6

Korney Burau answer works but resets all your default RGB to white which is not what you want, instead you can try

original_rgb_set = RGB.__set__

def custom_rgb_set(self, instance, value):
    if value == "0":
        pass
    else:
        modified_value = value
        original_rgb_set(self, instance, modified_value)

RGB.__set__ = custom_rgb_set
workbook = load_workbook(file)

you can debug in the value variable in the function custom_rgb_set, my faulty values where "0" so I skipped those as they were not necessary. But, see if you can find what the faulty values are and make a new RGB.set with the faulty ones removed or converted to the right format like 'FFFFFF'.