Posts tagged POI

CFSpreadsheet Custom Colors: Part Deux

Nov 3rd

Posted by existdissolve in ColdFusion

3 comments

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 >

, ,

CFSpreadsheet Custom Colors (and more)

Nov 1st

Posted by existdissolve in ColdFusion

1 comment

In my last post on CFSpreadsheet, I showed how you can easily create a custom row striping mechanism for your Excel data.  The one downside of the approach I outlined, however, is that it relied on the limited number of functions that CF exposes from POI, the Java magic that makes CFSpreadsheet work.

While the OOTB method is all well and good, there are a number of limitations.  One of the most severe is the limited number of colors that are available to use in spreadsheet styles.  There are a couple of dozen “named” colors that can be used, but that’s it: tough luck if you want 3 light shades of grey 😉

However, like most things in ColdFusion, you don’t necessarily have to limit yourself to what is available through CF’s tags and methods.  Rather, you can tap into the Java goodness underlying it, and modify, modify, modify to your heart’s content…including creating custom colors for CFSpreadsheet styles!

Creating a Color

First, let’s set up our workbook:

// create new workbook
excel = spreadsheetnew("My Worksheet",false);
// get underlying java methods goodness
workbook= excel.getworkbook();

As before, we create a new spreadsheet with spreadsheetnew().  However, immediately following this, we invoke the getworkbook() method.

Now I don’t want More >

,