473,689 Members | 2,840 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

xlrd cell background color

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 that
>>>print cell
text:u'test1.tx t' (XF:22)
text:u'test2.tx t' (XF:15)
text:u'test3.tx t' (XF:15)
text:u'test4.tx t' (XF:15)
text:u'test5.tx t' (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.tx t' (XF:22)
text:u'test2.tx t' (XF:22)
text:u'test3.tx t' (XF:22)
text:u'test4.tx t' (XF:22)
text:u'test5.tx t' (XF:22)
empty:'' (XF:15)
text:u'test6.tx t' (XF:22)
text:u'test7.tx t' (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.setde fault('GREEN',[])
filenames.setde fault('RED',[])

book = xlrd.open_workb ook("/home/pwaldo2/work/workbench/
Summary.xls",fo rmatting_info=T rue)
SumDoc = book.sheet_by_i ndex(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(filenam e)
n+=1
elif color == '23':
filenames['GREEN'].append(filenam e)
n+=1
Aug 13 '08 #1
2 9713
On Aug 14, 6:03 am, patrick.wa...@g mail.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.tx t' (XF:22)
text:u'test2.tx t' (XF:15)
text:u'test3.tx t' (XF:15)
text:u'test4.tx t' (XF:15)
text:u'test5.tx t' (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.tx t' (XF:22)
text:u'test2.tx t' (XF:22)
text:u'test3.tx t' (XF:22)
text:u'test4.tx t' (XF:22)
text:u'test5.tx t' (XF:22)
empty:'' (XF:15)
text:u'test6.tx t' (XF:22)
text:u'test7.tx t' (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.setde fault('GREEN',[])
filenames.setde fault('RED',[])

book = xlrd.open_workb ook("/home/pwaldo2/work/workbench/
Summary.xls",fo rmatting_info=T rue)
SumDoc = book.sheet_by_i ndex(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(filenam e)
n+=1
elif color == '23':
filenames['GREEN'].append(filenam e)
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.ba ckground_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
Aug 13 '08 #2
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.b ackground_colou r_index,
xf.background.p attern_colour_i ndex, and book.colour_map :
>>>color = xf.background.b ackground_colou r_index
print color
60
60
60
65
65
65
49

60 = red and 49 = green
>>>color = xf.background.p attern_colour_i ndex
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.setde fault('GREEN',[])
filenames.setde fault('RED',[])

book = xlrd.open_workb ook("/home/pwaldo2/work/workbench/
Summary.xls",fo rmatting_info=T rue)
SumDoc = book.sheet_by_i ndex(0)
print book.colour_map

n=1
while n<SumDoc.nrows :
filename = SumDoc.cell_val ue(n,5)
xfx = SumDoc.cell_xf_ index(n,5)
xf = book.xf_list[xfx]
print '1', xf.background.p attern_colour_i ndex
print '2', xf.background.b ackground_colou r_index
n+=1
On Aug 13, 5:32 pm, John Machin <sjmac...@lexic on.netwrote:
On Aug 14, 6:03 am, patrick.wa...@g mail.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.tx t' (XF:22)
text:u'test2.tx t' (XF:15)
text:u'test3.tx t' (XF:15)
text:u'test4.tx t' (XF:15)
text:u'test5.tx t' (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.tx t' (XF:22)
text:u'test2.tx t' (XF:22)
text:u'test3.tx t' (XF:22)
text:u'test4.tx t' (XF:22)
text:u'test5.tx t' (XF:22)
empty:'' (XF:15)
text:u'test6.tx t' (XF:22)
text:u'test7.tx t' (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.setde fault('GREEN',[])
filenames.setde fault('RED',[])
book = xlrd.open_workb ook("/home/pwaldo2/work/workbench/
Summary.xls",fo rmatting_info=T rue)
SumDoc = book.sheet_by_i ndex(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(filenam e)
n+=1
elif color == '23':
filenames['GREEN'].append(filenam e)
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.ba ckground_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
Aug 14 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
10440
by: John A. | last post by:
Hello all! I've got a big bunch of pages using tables for layout. Eventually I'll get them set up with more modernized code, but in the meantime I'd like to slip in a little quick holiday decoration. I'd like to insert a table cell with a fixed width (as much as such things can be fixed) and a repeating garland background so as to show up along the left side of the table, repeating down its length. Our site has a couple thousand...
1
6756
by: dhunter | last post by:
I'm kind of new to CSS and hope someone knows how to fix a problem I've been fighting with for days. I'm trying to create a mouseover navbar with CSS which inserts a colored background JPG that fills a transparent cell using "a:hover" and inserts a different colored JPG after you click on the link and are on the linked page ("a:active" I assume). My current test works fine (at least on the "a:hover" part) but the background won't fill...
3
4752
by: Peter Williams | last post by:
Hi All, I want to write some javascript for a html page which does the following. Imagine that the page contains a table with 2 columns and 3 rows, e.g.: +---+---+ | A | B | +---+---+
15
4111
by: John Machin | last post by:
I am pleased to announce a new general release (0.5.2) of xlrd, a Python package for extracting data from Microsoft Excel spreadsheets. CHANGES: * Book and sheet objects can now be pickled and unpickled. Instead of reading a large spreadsheet multiple times, consider pickling it once and loading the saved pickle; can be much faster. * Now works with Python 2.1. Backporting to Python 2.1 was partially
11
2553
by: Tempo | last post by:
Hello. I am getting the error that is displayed below, and I know exactly why it occurs. I posted some of my program's code below, and if you look at it you will see that the error terminates the program pre-maturely. Becasue of this pre-mature termination, the program is not able to execute it's final line of code, which is a very important line. The last line saves the Excel spreadsheet. So is there a way to make sure the last line...
2
5002
by: kylancal | last post by:
I am trying to read an Excel book with XLRD and I am getting the following error Traceback (most recent call last): File "C:\temp\ReadGoldmanExcelFiles.py", line 62, in <module> startRow = 0, sh_idx = 0, path2 = '//otaam.com/root/SharedFiles/ GlobeOp/Risk/Cal Projects/temp/') File "C:\temp\ReadGoldmanExcelFiles.py", line 36, in excelTocsv book = xlrd.open_workbook(infile) File "C:\Python25\lib\site-packages\xlrd\__init__.py", line 362,...
1
2229
by: JYOUNG79 | last post by:
When running 'python setup.py install' to install items for xlrd to work, does anybody know what items are installed and where items are installed at on a Mac (OS 10.4)? I'm assuming it mainly uses things out of the xlrd folder, but was curious if it copies files to other locations. Thanks. Jay
3
19315
by: Chanman | last post by:
This is probably a simple question to most of you, but here goes. I've downloaded the xlrd (version 0.6.1) module and placed in in the site-packages folder. Now, when I write a script, I type: import sys import xlrd When I run it, there is an import error saying there is no module named xlrd. However when I type sys.path, the site-packages folder is definitely in the path. Do I somehow need to run the xlrd setup.py
1
4170
by: Edwin.Madari | last post by:
here is working code that will read & display contents of all rows & columns in all the sheets, you need xlrd 0.6.1 import xlrd, os, sys book = xlrd.open_workbook(sys.argv) print "The number of worksheets is", book.nsheets for shx in range(book.nsheets): sh = book.sheet_by_index(shx) print 'tab:%s rows:%s cols:%s ' % (sh.name, sh.nrows, sh.ncols) for rx in range(sh.nrows):
0
8529
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9077
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8949
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8785
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8793
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6456
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5810
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4548
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2965
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.