1

Given a row and a column (plus sheet name) I need a way to extract the rgb value for a cell, as reported within excel, from a python script.

I've been using openpyxl. The following do not work consistently:

cell.fill.fgColor.rgb
cell.fill.bgColor.rgb

The following give the wrong value:

openpyxl.styles.colors.COLOR_INDEX[cell.fill.fgColor.index]
openpyxl.styles.colors.COLOR_INDEX[cell.fill.fgColor.index]

So I'm looking for another way to do it. Possible solutions need not be limited to the openpyxl library, as I'm willing to use another one.

Aozturk
  • 51
  • 1
  • 7
  • What do you mean by "do not work consistently", what are you getting? If you have a new xlsx file, what are the cell colours? – Jason Harrison Feb 23 '17 at 23:27
  • Sometimes it will have a valid and correct rgb value when dealing with, say, a blue cell, but other times it will not. – Aozturk Feb 23 '17 at 23:30
  • In a new xlsx file, colored cells have rgb 'None', attempting to access the rgb returns the string 'Values must be of type '. – Aozturk Feb 23 '17 at 23:37
  • Do you use Python/Excel for Windows? – Parfait Feb 24 '17 at 00:04
  • Yes. (character limit) – Aozturk Feb 24 '17 at 00:07
  • Because the python implementation might not be doing the "right thing." I would back up to excel and look at this question http://stackoverflow.com/questions/24382561/excel-formula-to-get-cell-color and also this http://dmcritchie.mvps.org/excel/colors.htm and from there compare what you are getting from openpyxl and from within excel. – Jason Harrison Feb 24 '17 at 17:50

3 Answers3

2

Consider win32com library if using Python for Windows as it can access the Excel Object Library and use any of its methods and properties such as Workbooks.Open, Interior.Color, Workbook.Close.

Using this interesting link for RGB value extraction, you can translate VBA to Python. Specifically, VBA's integer division operator, the backslash, \, translates to Python's double forward slash, // and the modulo operator in VBA mod becomes Python's %

Below outputs the RGB of a yellow-colored cell. Both cell reference types are included. Entire routine is wrapped in a try/except/finally to close background processes and release resources with or without a runtime error.

import win32com.client as win32

def getRGB(xlCell):
    C = xlCell.Interior.Color

    R = C % 256
    G = C // 256 % 256
    B = C // 65536 % 256

    return "R={}, G={}, B={}".format(R, G, B)

try:
    xlApp = win32.gencache.EnsureDispatch('Excel.Application')
    wb = xlApp.Workbooks.Open('C:\\Path\\To\\Workbook.xlsx')
    ws = wb.Worksheets('RESULTS')

    print(getRGB(ws.Range("A2")))      # A1 Style Reference
    # R=255, G=255, B=0  (yellow-colored cell)

    print(getRGB(ws.Cells(2,1)))       # R1C1 Style Reference 
    # R=255, G=255, B=0  (yellow-colored cell)

    wb.Close(False)
    xlApp.Visible = False    

except Exception as e:
    print(e)

finally:
    xlApp.Quit
    ws = None
    wb = None
    xlApp = None

Aside, do note VBA is NOT part of MS Excel but an external component (by default connected to the software). It is just another language to connect to Excel's object library as any programming language demonstrated here with Python can make a COM interface to this library.

Parfait
  • 104,375
  • 17
  • 94
  • 125
2

So this isn't an answer but it will be useful to folks who want a quick fix.

Using Microsoft Excel 2007 (but I believe most versions), if you go to Format Cells, choose Fill tab, and then select a color for Background color, the ten basic colors at the bottom always work for me.

However I've had only intermittent success with the gradient colors above those.

I mention this is a quick fix because if for your program, you want a simple way to bookmark cells for some function rather than the cell contents, you can still do this if you stick with the ten basic colors.

I found it easier doing that, rather than importing another library just to handle the extra colors I don't need anyway.

Scott McClenaghan
  • 21
  • 2
1

The solution by @Parfait is excellent. But I improved getRGB(), I think shifting will be better than redundant N divisions:

def getRGB(xlCell):
    C = int(xlCell.Interior.Color)

    R = C & 255
    G = C >> 8 & 255
    B = C >> 16 & 255

    return "R={}, G={}, B={}".format(R, G, B)
Михаил Чеботарев
  • 356
  • 4
  • 9