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 More >