Trying to select excel cells with red and green background using Python

Question

I have a file that has green highlighted cells and red highlighted cells indicating severity level.

Right now I'm using StyleFrame to extract them but for some reason I can extract the red cells but not the green cells. Any idea of how to go about this?

Notice that I made sure that the colors specified in the code are the same as the file.

from StyleFrame import StyleFrame, utils


GeneralCategories = StyleFrame.read_excel('Categories.xlsx', read_style=True, use_openpyxl_styles=False)

def redBackground(cell):
    return cell if cell.style.bg_color in {utils.colors.red, 'FFFF0000'} else np.nan


def greenBackground(cell):
    return cell if cell.style.bg_color in {utils.colors.green, '00FF00'} else np.nan


SevereCategory = StyleFrame(GeneralCategories.applymap(redBackground).dropna(axis=(0, 1), how='all'))


NonSevereCategory = StyleFrame(GeneralCategories.applymap(greenBackground).dropna(axis=(0, 1), how='all'))

Results:

print(SevereCategory)

                                    Keyword Categories
1               Adult Content: Nudity & Partial Nudity
2    Adult Content: Nudity & Partial Nudity;Adult C...
3    Adult Content: Nudity & Partial Nudity;Adult C...
4    Adult Content: Nudity & Partial Nudity;Adult C...
5    Adult Content: Nudity & Partial Nudity;Adult C...
..                                                 ...



print(NonSevereCategory)

Empty DataFrame
Columns: []
Index: []

Likely not relevant but why is red denoted in 8 characters like FFFF0000 and green was only in the typical RGB 6 00FF00. — OsmosisJonesLoL, Jan 02 '20 at 20:37
@OsmosisJonesLoL (love the username). I have no idea but I added FF to the beginning of the green hex and it worked! — Gorlomi, Jan 02 '20 at 20:58

score 1 · Accepted Answer · answered Jan 02 '20 at 20:59

1

Thanks to @OsmosisJonesLoL I added "FF" before the usual 6-digit green hex code and it worked!

Final code:

GeneralCategories = StyleFrame.read_excel('Keyword Category Breakout 12.5.19.xlsx', read_style=True, use_openpyxl_styles=False)

def redBackground(cell):
    return cell if cell.style.bg_color in {utils.colors.red, 'FFFF0000'} else np.nan

def greenBackground(cell):
    return cell if cell.style.bg_color in {utils.colors.green, 'FF00FF00', 'green'} else np.nan


SevereCategory = StyleFrame(GeneralCategories.applymap(redBackground).dropna(axis=(0, 1), how='all'))

NonSevereCategory = StyleFrame(GeneralCategories.applymap(greenBackground).dropna(axis=(0, 1), how='all'))

answered Jan 02 '20 at 20:59

Gorlomi

515
2
11

1

It's possible that the FF indicates an alpha channel or something? Not familiar enough with StyleFrame or excel to say. So maybe you were previously searching for invisible greens cells. – OsmosisJonesLoL Jan 02 '20 at 21:15
It could be, i tried other characters but it seems it only works with FF, it also worked with `FF`FFFF00 for yellow – Gorlomi Jan 02 '20 at 21:18

Trying to select excel cells with red and green background using Python

1 Answers1