Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
Ben Nadel at NCDevCon 2011 (Raleigh, NC) with: Rakshith Naresh
Ben Nadel at NCDevCon 2011 (Raleigh, NC) with: Rakshith Naresh@rakshithn )

POI Utility Freeze Frames, Bug Fixes, And Branded Templates

By Ben Nadel on
Tags: ColdFusion

I have made some updates to the POI Utility project for creating Microsoft Excel documents with ColdFusion. First, Dan Wilson pointed out some minor bugs that I had failed to test. Specifically with the DateFormat and with the FontSize properties of a cell. Thanks Dan!

Also, after suggestions by Matthew Abbott and Greg Cronkright, I have finally addeded freeze panes to the custom tags. For those of you are not familiar with this concept, a freeze pane allows you to have certains rows or columns become "static" while the rest of the rows and columns scroll beneath it. This kind of effect is great for a header row, espcially column headers on really long sets of data.

There are two ways to create the freeze pane. Either you can set the freeze row and / or column in the desired Sheet tag:

  • <poi:sheet name="Sheet One" freezerow="3" freezecolumn="1">

Or, you can set the freeze="true" attribute on the Column tag:

  • <poi:column freeze="true" />

Or, you can set the freeze="true" attribute on the Row tag:

  • <poi:row class="header" freeze="true">

You can use any combination of those as well; you can set these values in the Sheet tag and then override them in the Row / Column tags. The index for the row and column freezing starts at one; meaning, if you set the freeze be at row 2, rows 1 and 2 will be static and rows 3 and greater will scroll. Using the following code:

  • <poi:sheet name="Smokin' Hotties" freezerow="2">

... gives us this output:


POI Utility Creates Excel Document With Freeze Pane With ColdFusion  

Notice that after row 2 comes row 5. This is because rows 1 and 2 are now frozen and I have scrolled down in the document to demonstrate that the rest of the rows will scroll underneath these rows.

Branded templates are another thing I just wanted to demonstrate. The POI Utility custom tags have been able to use branded templates when they were first released, but I never really talked about them or demonstrated how they work. They are not perfect, but they can be useful. The biggest caveat to be aware of is that if you use an image that overlaps with a column or row that is resized, your image will also get resized. This is just a side effect of the POI limitations. And so, if you are going to be using an image in your branded template, you neeed to either resize your columns ahead of time (in the template) or do not explicitly resize them in the POI custom tags.

Here is an example of my branded template, included in the POI build:


Kinky Solutions Branded Template For Use In POI Utility To Create Excel Documents Using ColdFusion  

Notice that it contains the Kinky Solutions logo in row one. Notice also that the columns have already been properly sized for the report I am about to generate. Again, this is a limitation you need to be aware of. Now, I am going to specify that branded template as the based template to use when I create my new report:

  • <poi:document
  • name="REQUEST.ExcelData"
  • template="#ExpandPath( './branded_template.xls' )#">

Running the tag (example included in the POI Utility build), we get the following output:


Kinky Solutions Branded Report Created Using ColdFusion And The POI Utility  

Not bad, right? The newest code can be downloaded from the POI Utility Project page.

Reader Comments

So glad this post turned up. I was on the cusp of writing my own ColdFusion wrapper around POI. Your release of this code will be a huge time-saver for me and I'm sure will be much better than the wrapper I would have came up with. Thanks!

Hey Ben

Long time no post on one of the various blogs about the POI custom tags for me :)

I am attempting to use the tag with a 'template' for the first time to read in a branded file with all sorts of pre-defined cells.

What I can't figure out (or find a post about) is how to edit cells that already exist in a template when i read it in with the <poi:document template="xxxx"> tag. I can read it and just put an empty <poi:Sheets></poi:sheets> and close the </poi:document> and I get an exact copy of the file which is a good sign, but was hoping to then do some search and replacing for certain tags in the existing template to data from database, etc.

Thanks a lot!