In another post on using custom colors in CFSpreadsheet, I think I might have given the wrong impression that using custom colors REQUIRES that you abandon the CF-native spreadsheet functions. This is not true, of course–my intention was to show how adding custom colors IN ADDITION to the default palette will push you into the direction of POI and away from the CF methods.

However, if you really don’t care about using the default palette, you can have the best of both worlds: custom colors in conjunction with CF spreadsheet methods.

To not belabor the point, here’s the full code:

// create new workbook
excel = spreadsheetnew("My Worksheet",false);

// get reference to object containing color-related methods
palette = excel.getworkbook().getcustompalette();

// light blue color
lb = {
     r = javacast("int",240).bytevalue(),
     g = javacast("int",248).bytevalue(),
     b = javacast("int",255).bytevalue()};

// using index 48 to overwrite HSSFColor.LIGHT_BLUE
palette.setcoloratindex(48,lb.r,lb.g,lb.b);

// now that we have a custom color that has overwritten one of the POI constants (LIGHT_BLUE),
// we can use "LIGHT_BLUE" with CF methods and it will understand what we're talking about 

// specify format object for "odd" rows
format = {};
     format.fgcolor="light_blue";
     format.topborder="thin";
     format.topbordercolor="grey_40_percent";
     format.bottomborder="thin";
     format.bottombordercolor="grey_40_percent";
     format.leftborder="thin";
     format.leftbordercolor="grey_40_percent";
     format.rightborder="thin";
     format.rightbordercolor="grey_40_percent";

// apply style formatting to row
spreadsheetformatrows(excel,format,1);

As you can see, we treat the CF formatting the same way as we normally would.  The only difference is that prior to applying the style, we’ve modified the value of “light_blue” in our instance of the workbook’s color palette by using setcoloratindex() (here’s a list of the default palette color indexes).   Therefore, when we use “light_blue” in our CF formatting object, it will do the same as it’s always done, but will apply the custom color that we used to overwrite what was previously a seriously ugly color.  We get our cake (custom colors) and can eat it too (using native CF methods).

Nice. 🙂