xlrd - determine color of font in a cell
Nancy Head (CBIZ MMP)
Thanks!
Nancy
John Machin
Hi, Nancy.
> Am using xlrd.
Is good.
> I want to read a cell and determine whether its font is red, or not red.
> (For my purposes, the cells I care about contain text only - not numbers
> or dates - if that matters.)
Shouldn't matter. Analogy: Whether the ink in your pen is black or red
or blue is quite independent of whether you are scribbling words or
numbers or dates. However this is MS Excel :-)
> I'm also not sure what color the "red" is - so also might need a way to
> determine that also.
Might indeed -- could need a definition of "redness" and some math[s].
> I'm fumbling around trying things - Can the color be pulled using xf_list?
Don't fumble, read manual.
> import xlrd
> rd_xls_file = "C:\\work\\WB1.xls"
> xlrd_wb = xlrd.open_workbook(rd_xls_file, formatting_info=True)
>
> # As an example: first sheet, first cell
> rd_ws = xlrd_wb.sheet_by_index(0)
> row = 0
> col = 0
>
> rd_xf = xlrd_wb.xf_list[rd_ws.cell_xf_index(row,col)]
>
> # But I don't know how to use this to get the font color - or even if
> I'm on the right track.
You are definitely on the most probable of the three right tracks.
TRACK ONE:
You have the cell's xf. You know you need the font colour. OK,
emergency, all else has failed, we'll have to read the manual.
Starting from where you are (an XF instance), the next step would be to
look in the XF class, and find this:
http://www.lexicon.net/sjmachin/xlrd.html#xlrd.XF.font_index-attribute
The path is:
XF.font_index
Book.font_list
Font.colour_index
The docs on Font.colour_index point to the Formatting section up the
front of the docs. Read it.
Potted summary of what you have to do next:
[untested]
if colour_index == 2:
definitely red
elif not (0 <= colour_index <= 7):
definitely NOT red
elif colour_index >= 64:
rather unlikely to be red (defaults are all black AFAIK)
else:
rgb = book.colour_map[colour_index]
red = (255, 0, 0)
proclaim_red = colour_nearness(rgb, red) > YOUR_THRESHOLD
| >>> def colour_nearness(rgb1, rgb2):
| ... import math
| ... tot = 0
| ... for i in (0, 1, 2):
| ... tot += (rgb1[i] - rgb2[i]) ** 2
| ... diff = math.sqrt(tot)
| ... maxdiff = math.sqrt(3) * 255
| ... return 1 - diff / maxdiff
| ...
| >>> red = (255, 0, 0)
| >>> colour_nearness(red, red)
| 1.0
| >>> colour_nearness(red, (0,0,0))
| 0.42264973081037416
| >>> colour_nearness(red, (0,255,255))
| -2.2204460492503131e-016
| >>> colour_nearness(red, (128,0,0))
| 0.71245692475653932
| >>> colour_nearness(red, (153,51,0))
| 0.74180111025283879
| >>> colour_nearness(red, (0,255,0))
| 0.18350341907227385
| >>> colour_nearness(red, (0,0,255))
| 0.18350341907227385
| >>>
| >>> temp = [(ci, rgb) for (ci, rgb) in book.colour_map.items() if ci < 64]
| >>> temp2 = [(colour_nearness(red, rgb), ci, rgb) for (ci, rgb) in temp]
| >>> temp3 = temp2[:]
| >>> temp3.sort(reverse=True)
| >>> from pprint import pprint as pp
| >>> pp(temp3)
| [(1.0, 10, (255, 0, 0)),
| (1.0, 2, (255, 0, 0)),
| (0.76905989232414962, 53, (255, 102, 0)),
| (0.74180111025283879, 60, (153, 51, 0)),
| (0.71245692475653932, 37, (128, 0, 0)),
| (0.71245692475653932, 16, (128, 0, 0)),
| (0.65358983848622443, 61, (153, 51, 102)),
| (0.65358983848622443, 52, (255, 153, 0)),
| (0.65358983848622443, 25, (153, 51, 102)),
| (0.59174857038411166, 36, (128, 0, 128)),
| (0.59174857038411166, 20, (128, 0, 128)),
| (0.59174857038411166, 19, (128, 128, 0)),
| (0.59015073584804334, 29, (255, 128, 128)),
| (0.58366680010677341, 28, (102, 0, 102)),
| (0.53811978464829935, 51, (255, 204, 0)),
| (0.52390477143047665, 59, (51, 51, 0)),
| (0.51010205144336429, 63, (51, 51, 51)),
| [snip]
TRACK TWO:
As the xlrd manual points out, it is possible to override the font
colour in the "number format" by using e.g. [RED] or [COLOR2]; however
AFAIK this is unlikely to be used on cells which always contain text,
more likely to be used (conditionally) on numbers e.g. positive is
black, negative is red.
TRACK THREE:
It is also possible that the workbook uses "conditional formatting"
which allows for varying most things that are in the XF. Again I'd
expect this to be more likely used for numbers that text.
NEXT STEP:
You need to determine what is making those cells red (and hope it's not
conditional formatting, which xlrd doesn't dig out at the moment).
HTH,
John
Nancy Head (CBIZ MMP)
> > I'm fumbling around trying things - Can the color be pulled
> using xf_list?
>
> Don't fumble, read manual.
...
> The path is:
> XF.font_index
> Book.font_list
> Font.colour_index
> The docs on Font.colour_index point to the Formatting section
I didn't exactly neglect to read the manual - so thank you, John, for
not leaving it at just RTFM.
I am inexperienced, though - and you significantly helped me to
read/follow through it.
> > import xlrd
> > rd_xls_file = "C:\\work\\WB1.xls"
> > xlrd_wb = xlrd.open_workbook(rd_xls_file, formatting_info=True)
> >
> > # As an example: first sheet, first cell
> > rd_ws = xlrd_wb.sheet_by_index(0)
> > row = 0
> > col = 0
> >
> > rd_xf = xlrd_wb.xf_list[rd_ws.cell_xf_index(row,col)]
> >
cell_font = xlrd_wb.font_list[rd_xf.font_index]
# This 'print' helped me determine brute-force what "reds" to look for -
# not elegant or future-proof but "good-enough" for my needs:
#
# print rd_ws.cell_value(row,col), cell_font.colour_index
# Results: User entries were 10 or 16 - xlwt entries were 2 (but you
knew that).
red_index_list = [2,10,16]
if cell_font.colour_index in red_index_list:
# do stuff if cell contains red text
> NEXT STEP:
> You need to determine what is making those cells red (and
> hope it's not
> conditional formatting, which xlrd doesn't dig out at the moment).
Luckily, I know exactly what (& who) does make those cells red.
And, in the future, it will usually be xlwt.
Also luckly - it's nothing dire if a red cell gets through unnoticed.
So thanks!! You helped a great deal. Not the least of which was the
brief tutorial on how to read the doc. :)
-Nancy
John Machin
>
> # This 'print' helped me determine brute-force what "reds" to look for -
> # not elegant or future-proof but "good-enough" for my needs:
> #
> # print rd_ws.cell_value(row,col), cell_font.colour_index
Presuming your needs don't extend beyond the actual spreadsheets that
you have just inspected. There are 3 more colour indexes whose redness
is >= that of index 16; they could be used by the users ... and we hope
they're not sufficiently enterprising as to fiddle with the palette and
change some boring shade of grey to their own made-up special customised
Nancy-confounding red :-)
> # Results: User entries were 10 or 16 - xlwt entries were 2 (but you
> knew that).
How could I know whether the xlwt entries used 2 or 10? Only 10 (0x0A)
is mentioned in the easyxf list of colours.
> red_index_list = [2,10,16]
> if cell_font.colour_index in red_index_list:
> # do stuff if cell contains red text
>
>
>> NEXT STEP:
>> You need to determine what is making those cells red (and
>> hope it's not
>> conditional formatting, which xlrd doesn't dig out at the moment).
>
>
> Luckily, I know exactly what (& who) does make those cells red.
> And, in the future, it will usually be xlwt.
Let's hope so :-)
Cheers,
John
Nancy Head (CBIZ MMP)
> we hope they're not sufficiently enterprising as to fiddle with the
> palette and change some boring shade of grey to their own made-up
special
> customised Nancy-confounding red :-)
Well, one of "them" is me - and I don't like Excel enough that I'm
likely to muck with the palette.
And the other of "them" is close enough to *thwap* if he does. ;)
As I gain more experience programming, and with Python, and with
xlrd/xlwt, and with reading the doc ;) - I'll become better at
generalizing solutions.
I'm thrilled with what I've picked up already from this list - but yes,
I readily admit I still have a lot to learn.
-Nancy