xlwt enhancement to support custom RGB colors in palette

[Alan Rotman]

Hi,

I modified xlwt-0.7.2 to support adding custom RGB colors in the b8 palette.
I hope that this code can be used by other people now, and will be in the next release of xlwt.
From the user perspective, I added 2 new routines.
xlwt.add_palette_colour(colour_str, array_index)
set_colourRGB (self, colour_str, array_index, redVal, greenVal, BlueVal)
If no new colors are added, then xlwt behaves the same as today and no color palette is written to the .xls file.
If any new colors are added, then the custom b8 color palette is written to the .xls file.

Sample code is:
xlwt.add_palette_colour("light_blue_21", 0x21)
wbi = xlwt.Workbook()
wbi.set_colourRGB("light_blue_21", 0x21, 197, 217, 241) # 0xF1D9C5 in excel palette location 0x21
wsi = wbi.add_sheet('Roadmap')
style_n_r_L_green_wrap = ezxf('pattern: pattern solid, fore_colour light_blue_21;' 'align: horiz right, wrap yes;' "borders: left double;")
wsi.write(11, 0, "Dual Risk Rating:", style_n_r_L_green_wrap)

The modifications to xlwt-0.7.2 include one new file
Colors.py
and changes to 3 other files
BIFFRecords.py
Style.py
Workbook.py

The diffs for the 3 modified files are in diff.log, and all 4 files are in the folder NEW_FILES.

[John Yeung]

On Sat, Feb 25, 2012 at 1:35 PM, Alan Rotman <alan....@gmail.com> wrote:
> I modified xlwt-0.7.2 to support adding custom RGB
> colors in the b8 palette. I hope that this code can be
> used by other people now, and will be in the next
> release of xlwt.

I haven't tried out your changes yet, but assuming they work, I like
the additional functionality! Thanks for the contribution!

John Y.

[John Machin]

On Sunday, February 26, 2012 5:35:00 AM UTC+11, Alan Rotman wrote:

Hi,

I modified xlwt-0.7.2 to support adding custom RGB colors in the b8 palette.

Hi Alan,

Thanks for sharing your code.
 

I hope that this code can be used by other people now, and will be in the next release of xlwt.

It needs a fair bit of work (see below) starting with the API. As I've mentioned in this forum at least twice, publishing the proposed API for discussion before coding starts is recommended.
 

From the user perspective, I added 2 new routines.
     xlwt.add_palette_colour(colour_str, array_index)

This function changes package-global mapping from colour_name to colour_index [with no error checking]. This mapping is used only by easyxf, so being a package-global mapping is maybe OK.

However Colors.py [spelling?] defines another function:

change_palette_b8_colour (colour_str, array_index, redVal, greenVal, BlueVal)

which has several problems, in no particular order
(1) arg colour_str not used
(2) arg array_index should be "colour_index"
(3) camelCase + other deviations from PEP8
(4) updates a package-global mapping ( named "excel_default_palette_b8" !!!) from colour_index to RGB with no checking

This mapping should be a Workbook attribute.  The  excel_default_palette_b8 can be package-global but should never be updated.
 

     set_colourRGB (self, colour_str, array_index, redVal, greenVal, BlueVal)

This is a Workbook method. All it does is to call the above 2 functions in quick succession and sets a flag to say that the palette has been changed. It should (a) NOT change the first (name to index) mapping (b) maintain a Workbook-attribute COPY of the default index to RGB mapping (c) lose the flag. Consequental changes to BIFFRecords.py. Method name need an underscore before RGB

I'm not in favour of  "from foo import *". The Colors.py file can vanish. The function and mapping can be imported by name from Styles where necessary

 

If no new colors are added, then xlwt behaves the same as today and no color palette is written to the .xls file.
If any new colors are added, then the custom b8 color palette is written to the .xls file.

Sample code is:
    xlwt.add_palette_colour("light_blue_21", 0x21)
    wbi = xlwt.Workbook()
    wbi.set_colourRGB("light_blue_21", 0x21, 197, 217, 241)     # 0xF1D9C5 in excel palette location 0x21

The repetition of both the colour_name and the colour_index is an obvious(?) code smell.

 

    wsi = wbi.add_sheet('Roadmap')
    style_n_r_L_green_wrap = ezxf('pattern: pattern solid, fore_colour light_blue_21;' 'align: horiz right, wrap yes;' "borders: left double;")

"green" and "light_blue"? Perhaps you want to name your colour "our_green_21" and delete "light_blue" in case it's accidentally used somewhere in your code.
 
Cheers,
John

[Michael Torrie]

On 02/25/2012 11:35 AM, Alan Rotman wrote:
> The diffs for the 3 modified files are in diff.log, and all 4 files are in the folder NEW_FILES.

Right now the diffs are backwards (I can't apply them with the patch
command). Can you make new diffs, but make sure your are diffing old
against new, and use the -u flag? Possibly put them all together, but
do the diffs from the root of the source tree (or one level up) so they
can easily be applied with patch -p0 or patch -p1.

Also I if you use the "-N" flag then you can diff a nonexistent old file
with the new file and the patch will automatically create the file.

I normally do this kind of thing that creates a patch that will apply
with patch -p1:
diff -i -N path.orig/to/file1 /path/to/file1 > my.patch
diff -i -N path.orig/to/file2 /path/to/file2 >> my.patch
diff -i -N path.orig/to/nonexistent /path/to/newfile >> my.patch

Too bad python-excel doesn't have a subversion or git repository as you
can generate complete diffs with a simple "svn diff" or "git diff"

[Chris Withers]

On 25/02/2012 22:35, Michael Torrie wrote:
> Too bad python-excel doesn't have a subversion

https://secure.simplistix.co.uk/svn/xlwt/
https://secure.simplistix.co.uk/svn/xlrd/
https://secure.simplistix.co.uk/svn/xlutils/

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Alan Rotman]

I cleaned up the code, based on John Machin's comments, and added correct patch files.

Note that the global routine add_palette_colour() allows defining ezxf() shortcuts before the Workbook is instantiated.

Otherwise, there is no need to call add_palette_colour(). 

A better piece of sample code is: 

  file1.py

    xlwt.add_palette_colour("light_blue_21", 0x21) 

    style_n_r_L_green_wrap = ezxf('pattern: pattern solid, fore_colour light_blue_21;' 'align: horiz right, wrap yes;' "borders: left double;")

  file2.py

    wbi = xlwt.Workbook() 
    wbi.set_colourRGB("light_blue_21", 0x21, 197, 217, 241)     # 0xF1D9C5 in excel palette location 0x21 

    wsi = wbi.add_sheet('Roadmap') 

[Alan Rotman]

Has anyone used the new code, are there any comments on it?

John, have you added this code to the SVN main trunk?

alan

[John Machin]

On Friday, March 2, 2012 10:22:08 AM UTC+11, Alan Rotman wrote:

I cleaned up the code, based on John Machin's comments, and added correct patch files.

Still unclean. parentheses in

if (very_simple_expression):

and spaces in

def func (args):
alist [index]

Lines far too long. print statements in comments. Error checking silentlly ignores error instead of raising exception. Uses class attribute.

a_list = list(copy.copy(a_tuple)) # boggle!

Here is some code (untested) as an example:

    # __custom_palette_b8 is initialised to None in Workbook.__init__
   
    def set_colour_RGB(self, colour_index, red, green, blue):
        if not(8 <= colour_index < 64):
            raise Exception(
                "set_colour_RGB: colour_index (%d) not in range(8, 64)"
                % colour_index)
        if min(red, green, blue) < 0 or max(red, green, blue) >= 256:
            raise Exception(
                "set_colour_RGB: colour values (%d,%d,%d) must be in range(256)"
                % (red, green, blue))
        if self.__custom_palette_b8 is None:
            self.__custom_palette_b8 = list(Style.excel_default_palette_b8)
        # User-mutable palette starts at colour index 8,
        # so subtract 8 from colour index when placing in palette
        palette_index = colour_index - 8
        self.__custom_palette_b8[palette_index] = (
            red << 24 | green << 16 | blue << 8)

Patch files not generated with -u option as requested by Michael Torrie.

Eight individual files instead of zip containing 1 patch file and 4 .py files.

Note that the global routine add_palette_colour() allows defining ezxf() shortcuts before the Workbook is instantiated.

Already noted It also allows doing that after the Workbook has been instantiated. In other words, it does one simple job irrespective of whether the Workbook has been instantiated or not. This is as it should be. The issue is with the second API which does two different things. It should do only one: maintain the mapping from colour_index to RGB.
 

Otherwise, there is no need to call add_palette_colour(). 

A better piece of sample code is: 

  file1.py

    xlwt.add_palette_colour("light_blue_21", 0x21) 

    style_n_r_L_green_wrap = ezxf('pattern: pattern solid, fore_colour light_blue_21;' 'align: horiz right, wrap yes;' "borders: left double;")

  file2.py
    wbi = xlwt.Workbook() 
    wbi.set_colourRGB("light_blue_21", 0x21, 197, 217, 241)     # 0xF1D9C5 in excel palette location 0x21 
    wsi = wbi.add_sheet('Roadmap') 
    wsi.write(11, 0, "Dual Risk Rating:", style_n_r_L_green_wrap) 

File 2 is not standalone; it doesn't define  style_n_r_L_green_wrap. The concept of "green_thing = blue_thing" has not been explained.

 What versions of Python have you tested your code with? What tests have you done?

[John Machin]

On Sunday, March 11, 2012 8:56:56 PM UTC+11, Alan Rotman wrote:

Has anyone used the new code,

Not me.

 

are there any comments on it?

See answer to your previous question.
 

John, have you added this code to the SVN main trunk?

No, nor to the new (github) repository.
 

[Bea]

I've installed this enhancement and for some reason some basic functions stopped working like mentioned in other topic changing the column width..

But in overall I think its a great option to have...