Getting RGB value of background colour from a cell

[topix]

Hello everybody,

i'm trying to read the rgb value from a cell but getting wrong
values.

My testfile: http://torsti.net/files/xlrd-test.xls
Testcode:

import xlrd
book = xlrd.open_workbook("xlrd-test.xls", formatting_info=1)
sheets = book.sheet_names()
for index, sh in enumerate(sheets):
sheet = book.sheet_by_index(index)
rows, cols = sheet.nrows, sheet.ncols
for row in range(rows):
for col in range(cols):
print "row, col:", row+1, col+1,
thecell = sheet.cell(row, col) # could get 'dump',
'value', 'xf_index'
xfx = sheet.cell_xf_index(row, col)
xf = book.xf_list[xfx]
bgx = xf.background.pattern_colour_index
print " idx:",bgx,
rgb = book.colour_map[bgx]
print " RGB:",rgb


Output:
row, col: 1 1 idx: 54 RGB: (102, 102, 153)
row, col: 2 1 idx: 10 RGB: (255, 0, 0)
row, col: 3 1 idx: 11 RGB: (0, 255, 0)
row, col: 4 1 idx: 12 RGB: (0, 0, 255)
row, col: 5 1 idx: 64 RGB: None
row, col: 6 1 idx: 13 RGB: (255, 255, 0)
row, col: 7 1 idx: 15 RGB: (0, 255, 255)
row, col: 8 1 idx: 14 RGB: (255, 0, 255)

Row 2-8 are ok, but row 1 is wrong. In Excel set it to (78, 128,
178).

Am i doing something wrong or is there a bug in xlrd?

[John Machin]

There is no PALETTE record in the file. Thus xlrd sets
Book.palette_record to [] and fills Book.colour_map with the default
palette.

After opening the file with Excel 2003 SP3: select cell A1, click on
Format Cells/Patterns ... it highlights the 7th colour in the 2nd row
of the palette.

Click Cancel then Tools/Options/Color, select 7th colour in 2nd row,
click Modify/Custom ... it shows RGB 102, 102, 153.

> Am i doing something wrong or is there a bug in xlrd?

It appears from internal evidence that you have saved this file from
Excel 2007 or 2010. The default palette is different. It seems that MS
have smashed any idea of backward compatibility. See (e.g.) this
article: http://en.allexperts.com/q/Excel-1059/Excel-2007-color-palette-1.htm
which explores various strategies/kludges/workarounds. Sorry, but I
can't see what I can do about it in xlrd.

If it's any consolation, (102, 102, 153) is the nearest in the Excel
2003 default set to (78, 128, 178) using the simplistic metric in
xlrd.formatting.nearest_colour_index()

Regards,
John

[John Machin]

See also the followups to that article ... url is
http://en.allexperts.com/q/Excel-1059/f/Excel-2007-color-palette-1.htm