xlrd cell background color

[patrick.waldo]

Hi all,

I am trying to figure out a way to read colors with xlrd, but I did
not understand the formatting.py module. Basically, I want to sort
rows that are red or green. My initial attempt discovered thattext: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: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

 

[John Machin]

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

 

[patrick.waldo]

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:
60
60
60
65
65
65
49

60 = red and 49 = green
10
10
10
64
64
64
11

10 = red 11 = green
{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

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