xlrd cell background color

[John Machin]

On Aug 14, 6:03 am, patrick.wa... at 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