Get the color of a cell from xlsx with python

Question

I am really new with python, but Iwould like to get the color of a given cell with python from a xlsx. I wore the part that reads the xlsx, but how can I continue it with getting the backgrond color of the A3 cell?

import xlrd

workbook = xlrd.open_workbook('67.xlsx')
worksheet = workbook.sheet_by_name('Oldal1')
# read a cell
cell = worksheet.cell(2,2)
#print cell
print cell.value

https://stackoverflow.com/questions/7991209/identifying-excel-sheet-cell-color-code-using-xlrd-package — Carlos Gonzalez, Mar 12 '19 at 13:43
Yes, I tried it, but it gives back the error: NotImplementedError: formatting_info=True not yet implemented — GyaNoLi, Mar 12 '19 at 13:49

DirtyBit · Accepted Answer · 2019-03-12T15:01:32.010

Always a solution out there, somewhere in the deep ocean of SO:

def getBGColor(book, sheet, row, col):
    xfx = sheet.cell_xf_index(row, col)
    xf = book.xf_list[xfx]
    bgx = xf.background.pattern_colour_index
    pattern_colour = book.colour_map[bgx]

    #Actually, despite the name, the background colour is not the background colour.
    #background_colour_index = xf.background.background_colour_index
    #background_colour = book.colour_map[background_colour_index]

    return pattern_colour

Oh, wait. this is even better!

EDIT:

Here is a complete code:

from xlrd import open_workbook

wb = open_workbook('cel_lis.xls', formatting_info=True)
sh = wb.sheet_by_name('Sheet1')


def getBGColor(book, sheet, row, col):
    xfx = sheet.cell_xf_index(row, col)
    xf = book.xf_list[xfx]
    bgx = xf.background.pattern_colour_index
    pattern_colour = book.colour_map[bgx]

    #Actually, despite the name, the background colour is not the background colour.
    #background_colour_index = xf.background.background_colour_index
    #background_colour = book.colour_map[background_colour_index]

    return pattern_colour


print("The RGB value of the cell is: {}".format(getBGColor(wb, sh, 0, 0)))

OUTPUT:

The RGB value of the cell is: (255, 0, 0)

Note:

I used a worksheet of type .xls with a name cel_lis.xls having its sheet called Sheet1 with the first cell A which has a Red background color.

EDIT 2:

To get the name of the color, you could use webcolors:

from webcolors import rgb_to_name

rgb_Col = getBGColor(wb, sh, 0, 0)
print("The RGB value of the cell is: {} which is equivalent to {}".format(rgb_Col, rgb_to_name(rgb_Col)))

OUTPUT:

The RGB value of the cell is: (255, 0, 0) which is equivalent to red

do I have to define the workbook, worksheet, row and col in advance? Because it doesnt print out anything if it add print pattern_colour — GyaNoLi, Mar 12 '19 at 13:56
@GyaNoLi Indeed, you do. Let me see If I can serve it better for you. — DirtyBit, Mar 12 '19 at 14:00
@GyaNoLi Kindly check the edit and if it helps please accept the answer: https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work cheers — DirtyBit, Mar 12 '19 at 14:15

Sumit Pokhrel · Answer 2 · 2020-04-21T12:08:24.953

The Solution previously suggested works only for xls file, not for xlsx file. This raises a NotImplementedError: formatting_info=True not yet implemented. Xlrd library is still not updated to work for xlsx files. So you have to Save As and change the format every time which may not work for you.
Here is a solution for xlsx files using openpyxl library. A2 is the cell whose color code we need to find out.

import openpyxl
from openpyxl import load_workbook
excel_file = 'color_codes.xlsx' 
wb = load_workbook(excel_file, data_only = True)
sh = wb['Sheet1']
color_in_hex = sh['A2'].fill.start_color.index # this gives you Hexadecimal value of the color
print ('HEX =',color_in_hex) 
print('RGB =', tuple(int(color_in_hex[i:i+2], 16) for i in (0, 2, 4))) # Color in RGB

Get the color of a cell from xlsx with python

2 Answers2