CODEWITHSUNDEEP
pythonexcel
GyaNoLi
GyaNoLi

Reputation: 37

Get the color of a cell from xlsx with python

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

Upvotes: 2

Views: 7682

Answers (2)

Sumit Pokhrel
Sumit Pokhrel

Reputation: 2208

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

Upvotes: 2

DirtyBit
DirtyBit

Reputation: 16772

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.

out

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

Upvotes: 3

Related Questions