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 to over-emphasize this point, but our new “workbook” object is flipping awesome.  It contains a gateway to all of the methods that would enable you to create the entire workbook from scratch, if you so desired.  However, for our purposes here, we’ll keep our feet on the ground and stick to creating custom colors 🙂

Next, we’ll create a reference to a custom workbook palette:

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

At this point, if you dump out palette, you’ll notice a bunch of methods related to helping you create and manage the color palette for your workbook.  For our interests, we want to create a new color, so we’ll use setcoloratindex().

Before we actually invoke this method, however, let me make a few notes.  First, POI already has a default color palette defined; it is this, in fact, that is used when you specify one of the pre-defined colors with the CFSpreadsheet methods.  These default colors occupy indexes 1-40.  Therefore, if you wish to add a new color, just know that you’ll want to start at index 41, unless you are cool with overwriting existing colors at particular indexes.  EDIT: There are more than 40 default colors.  I’m not sure where I got the info for this, but I was mistaken.  So sue me.

Ok, so the setcoloratindex() method takes 4 arguments:

  • index: the index where the color will be set
  • r: the “red”  value of the color, defined as a byte from 0-255
  • g: the “green” value of the color, defined as a byte from 0-255
  • b: the “blue” value of the color, defined as a byte from 0-255

When I initially tried to do this, I thought using javacast() to create a byte would do the trick.  And it does, but only if you color is between -127 and 127.  I’m not smart enough to know what’s actually going on here, but for whatever reason the “byte” data type produced from javacast() is in the range of -127 to 127, rather than 0 to 255.  Fortunately, there is a very easy way around this.  Instead of java-casting as “byte”, you can simply javacast() as int as so:

r = javacast(“int”,183).bytevalue()

This produces the proper byte value, and allows you the more convenient range of 0-255, rather than -127-127.  So putting this altogether, here’s how we create the new color:

// light blue color
lb = {
     r = javacast("int",240).bytevalue(),
     g = javacast("int",248).bytevalue(),
     b = javacast("int",255).bytevalue()
};
// using index 41 for kicks and giggles
palette.setcoloratindex(41,lb.r,lb.g,lb.b)

Ok, so that was the easy part.  The trickier part now lies ahead: applying this color to a custom cell style.

Custom Cell Styles

Now that we’ve created the color, we can apply it to our cell style.  My initial not-so-well-thought-out-plan for this was to just get the new color created, and then figure it out from there.  I had hoped that I could effortlessly merge back into the CF-exclusive implementation of CFSpreadsheet, but no dice.  Now that we’re into POI manipulation, we have to see it through.

But fortunately, it’s not terribly difficult.  If we go back to our workbook object and dump it, we’ll find a method called createcellstyle(); this handy method will provide a way to create custom styles that we can then use to apply to our cells.

So let’s start by creating the new cell style:

// define base style;
bstyle = workbook.createcellstyle();

Easy. Now if we dump out bstyle, we’ll notice a bunch of methods that look pretty familiar: most of them have similarities to the style keys we looked at in the last post on CFSpreadsheet.  And they’re pretty self-explanatory: setbordertop(), setrightbordercolor(), and so on.  For those where colors can be set, we can now use the handy new color that we created earlier:

bstyle.setbordertop(1);
bstyle.setborderright(1);
bstyle.setborderbottom(1);
bstyle.setborderleft(1);
// set a blue border on our cells
bstyle.settopbordercolor(41);
bstyle.setrightbordercolor(41);
bstyle.setbottombordercolor(41);
bstyle.setleftbordercolor(41);

Okay.  So we’ve created a new color, and we we’ve created a new style object.  The last little bit of work to do is to apply this style to our cell.  Cake:

workbook.getsheetat(0).getrow(i).getcell(0).setcellstyle(bstyle);

Wrapping Up

In this post, we’ve seen that it’s pretty straight-forward to create new colors and apply them to workbooks.  While it’s true that we strayed quite a bit from the default implementation of CFSpreadsheet, it’s not a huge leap and could be very easily wrapped into some programmatic logic that would make it even simpler to implement.