Python: openpyxl how to read a cell font color

Question

I have tried to print some_cell.font.color.rgb and got various results.

For some I got what I want (like "FF000000"), but for others it gives me Value must be type 'basetring'. I assume that the latter is because I haven't actually defined the font color for these cells.

I'm using openpyxl 2.2.2

Answer 1

I think this is a bug in openpyxl and I think you should report it here.

Debugging the following code (with trepan3k of course):

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
c = ws['A4']  # cell gets created here
print(ws['A4'].font.color)

I get:

Color(rgb=Value must be type 'str', indexed=Value must be type 'int', auto=Value must be type 'bool', theme=1, tint=0.0, type='theme')

and this is coming from _repr_() of class Typed() in file openpyxl/descriptors/base.py. This message is given when a value hasn't been initialized. Notice that "indexed" and "auto" also haven't been set.

But these presumably should have been set when the code for the access of ws['a4'] was performed.

Note: the slight difference in message: 'str' instead of 'basestring' is probably attributable to the fact that I was using Python 3 or less likely openpyxl 2.2.3

And if there's some other additional code should that I should have added in my example, then at least https://openpyxl.readthedocs.org/en/latest/index.html should indicate that.

See also openpyxl cell style not reporting correctly where one of the developers seems to say the same thing in so many words.

Edit:

A couple of other things may be of interest to note. First, you can set a value and then read it, for example you can do this:

c.font.color.rgb = "FF000000"

Second, if you test c.font.color.rgb in a boolean it will look like a value has been set. That is

if c.font.color: print("yes")

will print "yes".

Answer 2

I remember diving into the openpyxl source a while back to try and whittle down some styling issues. If you are opening a pre-existing excel file, cells will only be styled if they contain content. For example, if you have a .xlsx with A:A being highlighted yellow, but only A1 contains any text, then openpyxl will not have that highlighting info available for A2. And if (using openpyxl) you record some data to A2 then it will initiate that cell with no styling, and you'd end up with your A column being yellow everywhere except A2.

The difficulty I ran into was in monkey-patching the cell creation to determine if the sheet has styling that should be maintained on an inheritance basis. If you have A:A styled with thick border, and B:B styled with thin border, which do you inherit from?

Answer 3

I did a check in this way:

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
c = ws['A1']
if 'rgb' in c.font.color.__dict__:
    print(c.font.color.rgb)
else:
    print('None')