Hi.
I want to fill the background of cell with some RGV value. Is it
possible?
I found only how to fill background with color from predefined set of
colors (xlwt.Style.colour_map_text).
> Hi. > I want to fill the background of cell with some RGV value. Is it > possible? > I found only how to fill background with color from predefined set of > colors (xlwt.Style.colour_map_text).
Excel 97-2003 doesn't support setting cell background to an arbitrary RGB value in a generalised fashion. What it does support is the ability to change the RGB values associated with one or more colour indexes in the palette. This is not implemented in xlwt (yet).
Here is an OTTOMH sketch of what needs to be done to implement this facility, in case someone wants to jump in and help:
1. Set up const default palette (grab it from e.g. xlrd.formatting.py). 2. Initialise working palette from default. 3. Provide API Workbook.set_palette_entry(colour_index, red, green, blue) ... validations: colour_index in range(8, 64); r/g/b in range(256). 4. When saving workbook: if working palette is not the same as the default palette, write a PALETTE record [there is a stub for this].
On Thursday, November 27, 2008 8:59:17 PM UTC-8, Grigoriy Petukhov wrote:
> Hi. > I want to fill the background of cell with some RGV value. Is it > possible? > I found only how to fill background with color from predefined set of > colors (xlwt.Style.colour_map_text).
On Fri, Sep 14, 2012 at 12:03 PM, Sammie <kingjym2...@gmail.com> wrote:
> I have a RGB tuple (r, g, b) for the background color of a cell, how to I
> set the background color using (r,g,b)?
> I appreciate your help.
You can't using xlwt. Prior to Excel 2007, Excel only had color
indexes, and that's all that xlwt supports. Maybe this will help,
though. I use a ColorMatcher that takes an RGB input and tries to
return the closest matching Excel color index:
class ColorMatcher(object):
def __init__(self):
self.reset()
def reset(self):
self.unused_colors = set(self.xlwt_colors)
# Never use black.
self.unused_colors.discard((0, 0, 0))
@staticmethod
def color_distance(rgb1, rgb2):
# Adapted from Colour metric by Thiadmer Riemersma,
# http://www.compuphase.com/cmetric.htm rmean = (rgb1[0] + rgb2[0]) / 2
r = rgb1[0] - rgb2[0]
g = rgb1[1] - rgb2[1]
b = rgb1[2] - rgb2[2]
return (((512 + rmean) * r * r) / 256) + 4 * g * g \
+ (((767 - rmean) * b * b) / 256)
def match_color_index(self, color):
"""Takes an "R,G,B" string or wx.Color and returns a matching xlwt
color.
"""
if isinstance(color, int):
return color
if color:
if isinstance(color, basestring):
rgb = map(int, color.split(','))
else:
rgb = color.Get()
distances = [self.color_distance(rgb, x) for x in self.xlwt_colors]
result = distances.index(min(distances))
self.unused_colors.discard(self.xlwt_colors[result])
return result
def get_unused_color(self):
"""Returns an xlwt color index that has not been previously returned by
this instance. Attempts to maximize the distance between the color and
all previously used colors.
"""
if not self.unused_colors:
# If we somehow run out of colors, reset the color matcher.
self.reset()
used_colors = [c for c in self.xlwt_colors if c not in
self.unused_colors]
result_color = max(self.unused_colors,
key=lambda c: min(self.color_distance(c, c2)
for c2 in used_colors))
result_index = self.xlwt_colors.index(result_color)
self.unused_colors.discard(result_color)
return result_index
Hi Ian:
Thanks very much for your quick reply.
How can I change to use Excel 2007/2010? Do they support setting background
colors using RGB tuple(r, g, b)?
On Fri, Sep 14, 2012 at 11:57 AM, Ian Kelly <ian.g.ke...@gmail.com> wrote:
> On Fri, Sep 14, 2012 at 12:03 PM, Sammie <kingjym2...@gmail.com> wrote:
> > I have a RGB tuple (r, g, b) for the background color of a cell, how to I
> > set the background color using (r,g,b)?
> > I appreciate your help.
> You can't using xlwt. Prior to Excel 2007, Excel only had color
> indexes, and that's all that xlwt supports. Maybe this will help,
> though. I use a ColorMatcher that takes an RGB input and tries to
> return the closest matching Excel color index:
> class ColorMatcher(object):
> def __init__(self):
> self.reset()
> def reset(self):
> self.unused_colors = set(self.xlwt_colors)
> # Never use black.
> self.unused_colors.discard((0, 0, 0))
> @staticmethod
> def color_distance(rgb1, rgb2):
> # Adapted from Colour metric by Thiadmer Riemersma,
> # http://www.compuphase.com/cmetric.htm > rmean = (rgb1[0] + rgb2[0]) / 2
> r = rgb1[0] - rgb2[0]
> g = rgb1[1] - rgb2[1]
> b = rgb1[2] - rgb2[2]
> return (((512 + rmean) * r * r) / 256) + 4 * g * g \
> + (((767 - rmean) * b * b) / 256)
> def match_color_index(self, color):
> """Takes an "R,G,B" string or wx.Color and returns a matching xlwt
> color.
> """
> if isinstance(color, int):
> return color
> if color:
> if isinstance(color, basestring):
> rgb = map(int, color.split(','))
> else:
> rgb = color.Get()
> distances = [self.color_distance(rgb, x) for x in
> self.xlwt_colors]
> result = distances.index(min(distances))
> self.unused_colors.discard(self.xlwt_colors[result])
> return result
> def get_unused_color(self):
> """Returns an xlwt color index that has not been previously
> returned by
> this instance. Attempts to maximize the distance between the
> color and
> all previously used colors.
> """
> if not self.unused_colors:
> # If we somehow run out of colors, reset the color matcher.
> self.reset()
> used_colors = [c for c in self.xlwt_colors if c not in
> self.unused_colors]
> result_color = max(self.unused_colors,
> key=lambda c: min(self.color_distance(c, c2)
> for c2 in used_colors))
> result_index = self.xlwt_colors.index(result_color)
> self.unused_colors.discard(result_color)
> return result_index
> --
> You received this message because you are subscribed to the Google Groups
> "python-excel" group.
> To post to this group, send an email to python-excel@googlegroups.com.
> To unsubscribe from this group, send email to
> python-excel+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/python-excel?hl=en-GB.
On Fri, Sep 14, 2012 at 11:57 AM, Ian Kelly <ian.g.ke...@gmail.com> wrote:
> On Fri, Sep 14, 2012 at 12:03 PM, Sammie <kingjym2...@gmail.com> wrote:
> > I have a RGB tuple (r, g, b) for the background color of a cell, how to I
> > set the background color using (r,g,b)?
> > I appreciate your help.
> You can't using xlwt. Prior to Excel 2007, Excel only had color
> indexes, and that's all that xlwt supports. Maybe this will help,
> though. I use a ColorMatcher that takes an RGB input and tries to
> return the closest matching Excel color index:
> class ColorMatcher(object):
> def __init__(self):
> self.reset()
> def reset(self):
> self.unused_colors = set(self.xlwt_colors)
> # Never use black.
> self.unused_colors.discard((0, 0, 0))
> @staticmethod
> def color_distance(rgb1, rgb2):
> # Adapted from Colour metric by Thiadmer Riemersma,
> # http://www.compuphase.com/cmetric.htm > rmean = (rgb1[0] + rgb2[0]) / 2
> r = rgb1[0] - rgb2[0]
> g = rgb1[1] - rgb2[1]
> b = rgb1[2] - rgb2[2]
> return (((512 + rmean) * r * r) / 256) + 4 * g * g \
> + (((767 - rmean) * b * b) / 256)
> def match_color_index(self, color):
> """Takes an "R,G,B" string or wx.Color and returns a matching xlwt
> color.
> """
> if isinstance(color, int):
> return color
> if color:
> if isinstance(color, basestring):
> rgb = map(int, color.split(','))
> else:
> rgb = color.Get()
> distances = [self.color_distance(rgb, x) for x in
> self.xlwt_colors]
> result = distances.index(min(distances))
> self.unused_colors.discard(self.xlwt_colors[result])
> return result
> def get_unused_color(self):
> """Returns an xlwt color index that has not been previously
> returned by
> this instance. Attempts to maximize the distance between the
> color and
> all previously used colors.
> """
> if not self.unused_colors:
> # If we somehow run out of colors, reset the color matcher.
> self.reset()
> used_colors = [c for c in self.xlwt_colors if c not in
> self.unused_colors]
> result_color = max(self.unused_colors,
> key=lambda c: min(self.color_distance(c, c2)
> for c2 in used_colors))
> result_index = self.xlwt_colors.index(result_color)
> self.unused_colors.discard(result_color)
> return result_index
> --
> You received this message because you are subscribed to the Google Groups
> "python-excel" group.
> To post to this group, send an email to python-excel@googlegroups.com.
> To unsubscribe from this group, send email to
> python-excel+unsubscribe@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/python-excel?hl=en-GB.
I modified xlwt 6 months ago to add support for setting new colors with an RGB tuple. wbs.set_colour_RGB() add_palette_colour() The code should be in the github trunk. If you can't access it, I could send you the sources. I don't know when the next xlwt release will be.
On Friday, September 14, 2012 9:57:39 PM UTC+3, Ian Kelly wrote:
> On Fri, Sep 14, 2012 at 12:03 PM, Sammie <kingj...@gmail.com <javascript:>> > wrote: > > I have a RGB tuple (r, g, b) for the background color of a cell, how to > I > > set the background color using (r,g,b)? > > I appreciate your help.
> You can't using xlwt. Prior to Excel 2007, Excel only had color > indexes, and that's all that xlwt supports. Maybe this will help, > though. I use a ColorMatcher that takes an RGB input and tries to > return the closest matching Excel color index:
> class ColorMatcher(object):
> def __init__(self): > self.reset()
> def reset(self): > self.unused_colors = set(self.xlwt_colors) > # Never use black. > self.unused_colors.discard((0, 0, 0))
> @staticmethod > def color_distance(rgb1, rgb2): > # Adapted from Colour metric by Thiadmer Riemersma, > # http://www.compuphase.com/cmetric.htm > rmean = (rgb1[0] + rgb2[0]) / 2 > r = rgb1[0] - rgb2[0] > g = rgb1[1] - rgb2[1] > b = rgb1[2] - rgb2[2] > return (((512 + rmean) * r * r) / 256) + 4 * g * g \ > + (((767 - rmean) * b * b) / 256)
> def match_color_index(self, color): > """Takes an "R,G,B" string or wx.Color and returns a matching xlwt > color. > """ > if isinstance(color, int): > return color > if color: > if isinstance(color, basestring): > rgb = map(int, color.split(',')) > else: > rgb = color.Get() > distances = [self.color_distance(rgb, x) for x in > self.xlwt_colors] > result = distances.index(min(distances)) > self.unused_colors.discard(self.xlwt_colors[result]) > return result
> def get_unused_color(self): > """Returns an xlwt color index that has not been previously > returned by > this instance. Attempts to maximize the distance between the > color and > all previously used colors. > """ > if not self.unused_colors: > # If we somehow run out of colors, reset the color matcher. > self.reset() > used_colors = [c for c in self.xlwt_colors if c not in > self.unused_colors] > result_color = max(self.unused_colors, > key=lambda c: min(self.color_distance(c, c2) > for c2 in used_colors)) > result_index = self.xlwt_colors.index(result_color) > self.unused_colors.discard(result_color) > return result_index
On Saturday, 15 September 2012 22:36:19 UTC+5:30, Alan Rotman wrote:
> I modified xlwt 6 months ago to add support for setting new colors with an > RGB tuple. > wbs.set_colour_RGB() > add_palette_colour() > The code should be in the github trunk. If you can't access it, I could > send you the sources. > I don't know when the next xlwt release will be.
> Alan
> On Friday, September 14, 2012 9:57:39 PM UTC+3, Ian Kelly wrote:
>> On Fri, Sep 14, 2012 at 12:03 PM, Sammie <kingj...@gmail.com> wrote: >> > I have a RGB tuple (r, g, b) for the background color of a cell, how to >> I >> > set the background color using (r,g,b)? >> > I appreciate your help.
>> You can't using xlwt. Prior to Excel 2007, Excel only had color >> indexes, and that's all that xlwt supports. Maybe this will help, >> though. I use a ColorMatcher that takes an RGB input and tries to >> return the closest matching Excel color index:
>> class ColorMatcher(object):
>> def __init__(self): >> self.reset()
>> def reset(self): >> self.unused_colors = set(self.xlwt_colors) >> # Never use black. >> self.unused_colors.discard((0, 0, 0))
>> @staticmethod >> def color_distance(rgb1, rgb2): >> # Adapted from Colour metric by Thiadmer Riemersma, >> # http://www.compuphase.com/cmetric.htm >> rmean = (rgb1[0] + rgb2[0]) / 2 >> r = rgb1[0] - rgb2[0] >> g = rgb1[1] - rgb2[1] >> b = rgb1[2] - rgb2[2] >> return (((512 + rmean) * r * r) / 256) + 4 * g * g \ >> + (((767 - rmean) * b * b) / 256)
>> def match_color_index(self, color): >> """Takes an "R,G,B" string or wx.Color and returns a matching >> xlwt >> color. >> """ >> if isinstance(color, int): >> return color >> if color: >> if isinstance(color, basestring): >> rgb = map(int, color.split(',')) >> else: >> rgb = color.Get() >> distances = [self.color_distance(rgb, x) for x in >> self.xlwt_colors] >> result = distances.index(min(distances)) >> self.unused_colors.discard(self.xlwt_colors[result]) >> return result
>> def get_unused_color(self): >> """Returns an xlwt color index that has not been previously >> returned by >> this instance. Attempts to maximize the distance between the >> color and >> all previously used colors. >> """ >> if not self.unused_colors: >> # If we somehow run out of colors, reset the color matcher. >> self.reset() >> used_colors = [c for c in self.xlwt_colors if c not in >> self.unused_colors] >> result_color = max(self.unused_colors, >> key=lambda c: min(self.color_distance(c, c2) >> for c2 in used_colors)) >> result_index = self.xlwt_colors.index(result_color) >> self.unused_colors.discard(result_color) >> return result_index
