Re: xlrd cell background color

[John Machin]

On Aug 14, 6:03 am, patrick.wa...@gmail.com wrote in
news:comp.lang.python thusly:
> Hi all,
>
> I am trying to figure out a way to read colors with xlrd, but I did
> not understand the formatting.py module.

It is complicated, because it is digging out complicated info which
varies in somewhat arbitrary fashion between the 5 (approx.) versions
of Excel that xlrd handles. Sometimes I don't understand it, and I
wrote it :-)

What I do when I want to *use* the formatting info, however, is to
read the xlrd documentation, and I suggest that you do the same. More
details at the end.

> Basically, I want to sort
> rows that are red or green. My initial attempt discovered that>>>print cell
>
> text:u'test1.txt' (XF:22)
> text:u'test2.txt' (XF:15)
> text:u'test3.txt' (XF:15)
> text:u'test4.txt' (XF:15)
> text:u'test5.txt' (XF:23)
>
> So, I thought that XF:22 represented my red highlighted row and XF:23
> represented my green highlighted row. However, that was not always
> true. If one row is blank and I only highlighted one row, I got:>>>print cell
>
> text:u'test1.txt' (XF:22)
> text:u'test2.txt' (XF:22)
> text:u'test3.txt' (XF:22)
> text:u'test4.txt' (XF:22)
> text:u'test5.txt' (XF:22)
> empty:'' (XF:15)
> text:u'test6.txt' (XF:22)
> text:u'test7.txt' (XF:23)
>
> Now NoFill is XF:22! I am sure I am going about this the wrong way,
> but I just want to store filenames into a dictionary based on whether
> they are red or green. Any ideas would be much appreciated. My code
> is below.
>
> Best,
> Patrick
>
> filenames = {}
> filenames.setdefault('GREEN',[])
> filenames.setdefault('RED',[])
>
> book = xlrd.open_workbook("/home/pwaldo2/work/workbench/
> Summary.xls",formatting_info=True)
> SumDoc = book.sheet_by_index(0)
>
> n=1
> while n<SumDoc.nrows:
> cell = SumDoc.cell(n,5)
> print cell
> filename = str(cell)[7:-9]
> color = str(cell)[-3:-1]
> if color == '22':
> filenames['RED'].append(filename)
> n+=1
> elif color == '23':
> filenames['GREEN'].append(filename)
> n+=1

22 and 23 are not colours, they are indexes into a list of XFs
(extended formats). The indexes after 16 have no fixed meaning, and as
you found, if you add/subtract formatting features to your XLS file,
the actual indexes used will change. Don't use str(cell). Use
cell.xf_index.

Here is your reading path through the docs, starting at "The Cell
class":
Cell.xf_index
Book.xf_list
XF.background
XFBackground.background_colour_index
Book.colour_map
which leaves you with a (red, green, blue) tuple. Deciding whether the
result is "red" or "green" or something else is up to you. For
example, you may wish to classify your cell colours as red or green
according to whether they are closer to (255, 0, 0) or (0, 255, 0)
respectively. Do make sure that you read the docs section headed "The
Palette; Colour Indexes".

As suggested in the xlrd README etc, consider the python-excel
newsgroup / mailing-list (http://groups.google.com/group/python-
excel), to which I've CCed this post ... you'll find a thread "Getting
a particular cell background color index" starting on 2007-09-08 that
covers about 80% of what you need.

HTH,
John

[patric...@gmail.com]

Thank you very much. I did not know there was a python-excel group,
which I will certainly take note of in the future. The previous post
answered my question, but I wanted to clarify the difference between
xf.background.background_colour_index,
xf.background.pattern_colour_index, and book.colour_map:

>>>color = xf.background.background_colour_index
>>>print color
60
60
60
65
65
65
49

60 = red and 49 = green

>>>color = xf.background.pattern_colour_index
>>>print color
10
10
10
64
64
64
11

10 = red 11 = green

>>>print book.colour_map
{0: (0, 0, 0), 1: (255, 255, 255), 2: (255, 0, 0), 3: (0, 255, 0), 4:
(0, 0, 255), 5: (255, 255, 0), 6: (255, 0, 255), 7: (0, 255, 255), 8:
(0, 0, 0), 9: (255, 255, 255), 10: (255, 0, 0), 11: (0, 255, 0), 12:
(0, 0, 255), 13: (255, 255, 0), 14: (255, 0, 255), 15: (0, 255, 255),
16: (128, 0, 0), 17: (0, 128, 0), 18: (0, 0, 128), 19: (128, 128, 0),
20: (128, 0, 128), 21: (0, 128, 128), 22: (192, 192, 192), 23: (128,
128, 128), 24: (153, 153, 255), 25: (153, 51, 102), 26: (255, 255,
204), 27: (204, 255, 255), 28: (102, 0, 102), 29: (255, 128, 128), 30:
(0, 102, 204), 31: (204, 204, 255), 32: (0, 0, 128), 33: (255, 0,
255), 34: (255, 255, 0), 35: (0, 255, 255), 36: (128, 0, 128), 37:
(128, 0, 0), 38: (0, 128, 128), 39: (0, 0, 255), 40: (0, 204, 255),
41: (204, 255, 255), 42: (204, 255, 204), 43: (255, 255, 153), 44:
(153, 204, 255), 45: (255, 153, 204), 46: (204, 153, 255), 47: (255,
204, 153), 48: (51, 102, 255), 49: (51, 204, 204), 50: (153, 204, 0),
51: (255, 204, 0), 52: (255, 153, 0), 53: (255, 102, 0), 54: (102,
102, 153), 55: (150, 150, 150), 56: (0, 51, 102), 57: (51, 153, 102),
58: (0, 51, 0), 59: (51, 51, 0), 60: (153, 51, 0), 61: (153, 51, 102),
62: (51, 51, 153), 63: (51, 51, 51), 64: None, 65: None, 81: None,
32767: None}

After looking at the color, OpenOffice says I am using 'light red' for
the first 3 rows and 'light green' for the last one, so how the
numbers change for the first two examples makes sense. However, how
the numbers change for book.colour_map does not make much sense to me
since the numbers change without an apparent pattern. Could you
clarify?

Best,
Patrick

Revised Code:

import xlrd

filenames = {}
filenames.setdefault('GREEN',[])
filenames.setdefault('RED',[])

book = xlrd.open_workbook("/home/pwaldo2/work/workbench/
Summary.xls",formatting_info=True)
SumDoc = book.sheet_by_index(0)

print book.colour_map

n=1
while n<SumDoc.nrows:
filename = SumDoc.cell_value(n,5)
xfx = SumDoc.cell_xf_index(n,5)
xf = book.xf_list[xfx]
print '1', xf.background.pattern_colour_index
print '2', xf.background.background_colour_index
n+=1

[John Machin]

On Aug 15, 12:55 am, patrick.wa...@gmail.com wrote:
> Thank you very much. I did not know there was a python-excel group,
> which I will certainly take note of in the future. The previous post
> answered my question, but I wanted to clarify the difference between
> xf.background.background_colour_index,
> xf.background.pattern_colour_index, and book.colour_map:

Pretend you are an artist. The background colour is what you've
painted all over your canvas. Then you paint a pattern (e.g. thick
forward-sloping diagonal lines) using the pattern colour. Your
signature in the corner uses the font colour (default: black).

See the message about an example file that I posted a few minutes ago.

>
> >>>color = xf.background.background_colour_index
> >>>print color

It's not a colour, it's a colour *INDEX*

>
> 60
> 60
> 60
> 65
> 65
> 65
> 49
>
> 60 = red and 49 = green

Presumably you mean "Where I see red, it says 60 etc"

>
> >>>color = xf.background.pattern_colour_index
> >>>print color
>
> 10
> 10
> 10
> 64
> 64
> 64
> 11
>
> 10 = red 11 = green

With the default palette in operation, 10 means red, 11 means green,
49 means aqua, and 60 means brown. Presumably your file is using a
solid fill pattern, so that the foreground colour hides the background
colour, so you don't see any brown or aqua.

>
> >>>print book.colour_map
>
> {0: (0, 0, 0), 1: (255, 255, 255), 2: (255, 0, 0), 3: (0, 255, 0), 4:
> (0, 0, 255), 5: (255, 255, 0), 6: (255, 0, 255), 7: (0, 255, 255), 8:
> (0, 0, 0), 9: (255, 255, 255), 10: (255, 0, 0), 11: (0, 255, 0), 12:

colour index 10 -> (red=255, green=0, blue=0)
colour index 11 -> (red=0, green=255, blue=0)

> (0, 0, 255), 13: (255, 255, 0), 14: (255, 0, 255), 15: (0, 255, 255),
> 16: (128, 0, 0), 17: (0, 128, 0), 18: (0, 0, 128), 19: (128, 128, 0),
> 20: (128, 0, 128), 21: (0, 128, 128), 22: (192, 192, 192), 23: (128,
> 128, 128), 24: (153, 153, 255), 25: (153, 51, 102), 26: (255, 255,
> 204), 27: (204, 255, 255), 28: (102, 0, 102), 29: (255, 128, 128), 30:
> (0, 102, 204), 31: (204, 204, 255), 32: (0, 0, 128), 33: (255, 0,
> 255), 34: (255, 255, 0), 35: (0, 255, 255), 36: (128, 0, 128), 37:
> (128, 0, 0), 38: (0, 128, 128), 39: (0, 0, 255), 40: (0, 204, 255),
> 41: (204, 255, 255), 42: (204, 255, 204), 43: (255, 255, 153), 44:
> (153, 204, 255), 45: (255, 153, 204), 46: (204, 153, 255), 47: (255,
> 204, 153), 48: (51, 102, 255), 49: (51, 204, 204), 50: (153, 204, 0),
> 51: (255, 204, 0), 52: (255, 153, 0), 53: (255, 102, 0), 54: (102,
> 102, 153), 55: (150, 150, 150), 56: (0, 51, 102), 57: (51, 153, 102),
> 58: (0, 51, 0), 59: (51, 51, 0), 60: (153, 51, 0), 61: (153, 51, 102),
> 62: (51, 51, 153), 63: (51, 51, 51), 64: None, 65: None, 81: None,
> 32767: None}
>
> After looking at the color, OpenOffice says I am using 'light red' for
> the first 3 rows and 'light green' for the last one, so how the
> numbers change for the first two examples makes sense. However, how
> the numbers change for book.colour_map does not make much sense to me
> since the numbers change without an apparent pattern. Could you
> clarify?

> > Here is your reading path through the docs, starting at "The Cell
> > class":
> > Cell.xf_index
> > Book.xf_list
> > XF.background
> > XFBackground.background_colour_index
> > Book.colour_map
> > which leaves you with a (red, green, blue) tuple. Deciding whether the
> > result is "red" or "green" or something else is up to you. For
> > example, you may wish to classify your cell colours as red or green
> > according to whether they are closer to (255, 0, 0) or (0, 255, 0)
> > respectively. Do make sure that you read the docs section headed "The
> > Palette; Colour Indexes".

Did you? Which parts are unclear? If you don't understand (red, green,
blue), read (some of) http://en.wikipedia.org/wiki/RGB_color_model

Cheers,
John

[patric...@gmail.com]

Ok, I understand now how it works. I was not looking at the tuples
closely enough!

Thank you for your help.

Best,
Patrick