37

I am writing a python script to read data from an excel sheet using xlrd. Few of the cells of the the work sheet are highlighted with different color and I want to identify the color code of the cell. Is there any way to do that ? An example would be really appreciated.

Kinjal
  • 718
  • 2
  • 11
  • 21

4 Answers4

46

Here is one way to handle this:

import xlrd
book = xlrd.open_workbook("sample.xls", formatting_info=True)
sheets = book.sheet_names()
print "sheets are:", sheets
for index, sh in enumerate(sheets):
    sheet = book.sheet_by_index(index)
    print "Sheet:", sheet.name
    rows, cols = sheet.nrows, sheet.ncols
    print "Number of rows: %s   Number of cols: %s" % (rows, cols)
    for row in range(rows):
        for col in range(cols):
            print "row, col is:", row+1, col+1,
            thecell = sheet.cell(row, col)      
            # could get 'dump', 'value', 'xf_index'
            print thecell.value,
            xfx = sheet.cell_xf_index(row, col)
            xf = book.xf_list[xfx]
            bgx = xf.background.pattern_colour_index
            print bgx

More info on the Python-Excel Google Group.

Sursh
  • 25
  • 4
JMax
  • 26,109
  • 12
  • 69
  • 88
  • The script is able to identify color code. But now I am getting a different problem. The excel sheet which I am parsing has two empty cells without any extra formatting or color, that is, both of the cells has white background and no text inside, at the position - (52,4) and (9,4). In first case, color is 64 and the for the other one it is 9. How is the color code different for two similar type of cells? – Kinjal Nov 03 '11 at 14:06
  • Are you sure you don't have one `blank` and another `no fill`? – JMax Nov 03 '11 at 14:37
  • Colour index 9 means white unless the palette has been changed. 61 is a "system" colour index; read the section about colour indexes near the start of the xlrd docs. "empty", "white" and "no text inside" are vague. Tell us the cell **type**, value, and xf_index. Use `repr(value)`. You may have default formatting applied to column 4; what does `sheet.colinfo_map[4].xf_index` give you? – John Machin Nov 07 '11 at 10:43
  • 2
    I finally managed to differentiate if a cell is highlighted or not. The check is done by finding cell’s color map. Here is the code: book = xlrd.open_workbook("sample.xls", formatting_info=1) xfx = sheet.cell_xf_index(row, col) xf = book.xf_list[xfx] bgx = xf.background.pattern_colour_index color_map = book.colour_map[bgx] if color_map and (color_map[0] != 255 or color_map[1] != 255 or color_map[2] != 255): #worksheet Cell is highlighted else: #worksheet Cell is not highlighted So, if none of values in tuple is 255, that means the cell is highlighted. – Kinjal Nov 30 '11 at 15:47
  • 4
    This raises a `NotImplementedError` at me. Likely because I open `xlsx` rather than `xls`: `NotImplementedError: formatting_info=True not yet implemented` – deeenes Oct 13 '16 at 22:54
  • 2
    Unfortunately, this works only for 'XLS' files, not for 'XLSX' files. Does anybody have any solution to 'XLSX' files ? You will get a 'NotImplementedError'. – Sumit Pokhrel Apr 07 '20 at 14:28
9

The Solution suggested by JMax 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
Sumit Pokhrel
  • 2,087
  • 24
  • 18
  • @Heinz I tested it for multiple cases, by changing a lot of colors. It worked just fine. Can you share your example ? – Sumit Pokhrel Nov 14 '20 at 18:04
  • In my case it raises `TypeError: 'int' object is not subscriptable` because color_in_hex is just a number like 6 or 7 (even though the colors are completely different like yellow and grey, how can they be two numbers next to each other?) and it's an integer, which is not a subscritable type, thus [i:i+2] cannot be executed. I tried to align it with `color_in_hex: str = f"#{str(color_in_hex).zfill(6)}"` to get something like `#000007` but the color is incorrect anyway – George Zorikov Sep 05 '22 at 16:15
4

This function returns cell background's rgb value in tuple. 

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
Jinbom Heo
  • 7,248
  • 14
  • 52
  • 59
  • This is for the xlrd, with ` open_workbook(..., formatting_info=True)` so it can't be used with xlsx files. – Echo9k Jun 14 '22 at 20:12
0
# say you have an Excel file called workbook
 and inside it there is a worksheet called worksheet

# inside that worksheet is cell say C1 that is highlighted
 and you want to get the color value of the highlighted cell

# Trying the openpyxl package
import openpyxl

# Importing all modules from the openpyxl package
from openpyxl import *

# reading ev2 excel workbook through load_workbook function
workbook = load_workbook("C:PATHTO/workbook.xlsx")

# Accessing existing worksheets
worksheet = workbook ["worksheet"]

## METHOD1 ##

# Getting the highlight property of the cell
highlight=str(worksheet ['C1'].fill)

# Printing out the value of the color
index=int(highlight.find("rgb='"))
print(highlight[index+5:index+13])

## METHOD 2 ##

print(worksheet ['C11'].fill.start_color.index)
Urodoc
  • 21
  • 2