xlrd cell background color

Thu Aug 14 10:54:40 EDT 2008

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

On Aug 13, 5:32 pm, John Machin <sjmac... at lexicon.net> wrote:
> 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