Get count of a color in cells of my xlsx file python

Question

i have a .xlsx file which has 3 columns.

id   name  age
1    jon    10       #jon cell is red
2    bob    54       #bob cell is red
3    rob    77  
4    sal    22       #sal cell is red
5    wil    47
6    nia    32

in my column 'name' jon ,bob, sal cell are red colored, rest cells of column 'name' are green colored.

i want to find the count of red colored cells i.e in this case 3.

this is just an example, i have .xlsx files which have more than 1000 rows and it would be very difficult to manually count red color cells.

I tried it using openpyxl and xlrd but could'nt find much

any leads would be appreciated, thanks in advance

Answer 1

I had a look Here

First we need to load the workbook and then work with a selected sheet from that workbook, for that we do the following:

import openpyxl as px

#Loading the workbook into python
wb = px.load_workbook('FileName.xlsx')

#Selecting Active Sheet
sheet = wb.get_sheet_by_name('Sheet1')

The second answer (in the link) was quite helpful - one can grab the HEX code of the cell colour by referring to the colour index doing the following:

i = sheet['A1'].fill.start_color.index #Grabbing colour at [A1]
index_colours = px.styles.colors.COLOR_INDEX
result = str(index_colours [i])

result= "#"+result[2:]

print result
#>>>#HEXCODE

you can use this and add all the codes to a list after reading the excel file, eg:

colour_list = []
index_colours = px.styles.colors.COLOR_INDEX
for row in range(1,all_rows):

    i = sheet['B' + str(row)].fill.start_color.index
    result = str(index_colours [i])
    result= "#"+result[2:]
    colour_list.append(result)

and count HEX codes referring to Red

red = '#FF0000'
print colour_list.count(red)
#>>> 3