POI Utility Freeze Frames, Bug Fixes, And Branded Templates
Posted June 6, 2008 at 9:49 AM
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:
Launch code in new window » Download code as text file »
- <poi:sheet name="Sheet One" freezerow="3" freezecolumn="1">
Or, you can set the freeze="true" attribute on the Column tag:
Launch code in new window » Download code as text file »
- <poi:column freeze="true" />
Or, you can set the freeze="true" attribute on the Row tag:
Launch code in new window » Download code as text file »
- <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:
Launch code in new window » Download code as text file »
- <poi:sheet name="Smokin' Hotties" freezerow="2">
... gives us this output:
| | | | ||
| |  | | ||
| | | |
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:
| | | | ||
| |  | | ||
| | | |
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:
Launch code in new window » Download code as text file »
- <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:
| | | | ||
| |  | | ||
| | | |
Not bad, right? The newest code can be downloaded from the POI Utility Project page.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print 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!
