POI ColdFusion Custom Tags Almost There

Posted March 24, 2008 at 9:24 AM by Ben Nadel

Tags: ColdFusion

Since I have been quiet for a while, I wanted to just drop a quick note that my POI ColdFusion custom tags (for Excel file creation) are coming along quite nicely. Currently, I can output sheets, rows, and cells with pretty good CSS control. I can format dates and numbers pretty well, although the data formatting needs a lot more exploration - I am not sure what the limitations are just yet.

To see what can be done so far, let's take a look at a quick demo. As always, I will need to build a sample query for which the Excel file will be generated. I am going to manually build a query that contains hot celebrity data:

  • <!--- Create out data query. --->
  • <cfset qPeople = QueryNew(
  • "rank, name, hair, best_feature, hotness, last_fantasy",
  • "integer, varchar, varchar, varchar, decimal, timestamp"
  • ) />
  •  
  • <!--- Populate query. --->
  • <cfset QueryAddRow( qPeople, 5 ) />
  •  
  • <cfset qPeople[ "rank" ][ 1 ] = JavaCast( "int", 1 ) />
  • <cfset qPeople[ "name" ][ 1 ] = JavaCast( "string", "Christina Cox" ) />
  • <cfset qPeople[ "hair" ][ 1 ] = JavaCast( "string", "Dirty Blonde" ) />
  • <cfset qPeople[ "best_feature" ][ 1 ] = JavaCast( "string", "Lips" ) />
  • <cfset qPeople[ "hotness" ][ 1 ] = JavaCast( "float", 9.0 ) />
  • <cfset qPeople[ "last_fantasy" ][ 1 ] = ParseDateTime( "03/15/2008" ) />
  •  
  • <cfset qPeople[ "rank" ][ 2 ] = JavaCast( "int", 2 ) />
  • <cfset qPeople[ "name" ][ 2 ] = JavaCast( "string", "Meg Ryan" ) />
  • <cfset qPeople[ "hair" ][ 2 ] = JavaCast( "string", "Blonde" ) />
  • <cfset qPeople[ "best_feature" ][ 2 ] = JavaCast( "string", "Smile" ) />
  • <cfset qPeople[ "hotness" ][ 2 ] = JavaCast( "float", 9.0 ) />
  • <cfset qPeople[ "last_fantasy" ][ 2 ] = ParseDateTime( "07/02/2005" ) />
  •  
  • <cfset qPeople[ "rank" ][ 3 ] = JavaCast( "int", 3 ) />
  • <cfset qPeople[ "name" ][ 3 ] = JavaCast( "string", "Winonna Ryder" ) />
  • <cfset qPeople[ "hair" ][ 3 ] = JavaCast( "string", "Brunette" ) />
  • <cfset qPeople[ "best_feature" ][ 3 ] = JavaCast( "string", "Eyes" ) />
  • <cfset qPeople[ "hotness" ][ 3 ] = JavaCast( "float", 8.0 ) />
  • <cfset qPeople[ "last_fantasy" ][ 3 ] = ParseDateTime( "11/22/2002" ) />
  •  
  • <cfset qPeople[ "rank" ][ 4 ] = JavaCast( "int", 4 ) />
  • <cfset qPeople[ "name" ][ 4 ] = JavaCast( "string", "Angela Bassett" ) />
  • <cfset qPeople[ "hair" ][ 4 ] = JavaCast( "string", "Brunette" ) />
  • <cfset qPeople[ "best_feature" ][ 4 ] = JavaCast( "string", "Angularity" ) />
  • <cfset qPeople[ "hotness" ][ 4 ] = JavaCast( "float", 8.0 ) />
  • <cfset qPeople[ "last_fantasy" ][ 4 ] = ParseDateTime( "05/15/2003" ) />
  •  
  • <cfset qPeople[ "rank" ][ 5 ] = JavaCast( "int", 5 ) />
  • <cfset qPeople[ "name" ][ 5 ] = JavaCast( "string", "Michelle Rodriguez" ) />
  • <cfset qPeople[ "hair" ][ 5 ] = JavaCast( "string", "Brunette" ) />
  • <cfset qPeople[ "best_feature" ][ 5 ] = JavaCast( "string", "Muscularity" ) />
  • <cfset qPeople[ "hotness" ][ 5 ] = JavaCast( "float", 8.0 ) />
  • <cfset qPeople[ "last_fantasy" ][ 5 ] = ParseDateTime( "01/01/2008" ) />

This is a just a simple query. Now, we are going to output it to an Excel document as well as a ColdFusion variable (in the form of a Byte Array Output Stream):

  • <!--- Import the POI tag library. --->
  • <cfimport taglib="./poi/" prefix="poi" />
  •  
  •  
  • <!---
  • Create an excel document and store binary data into
  • REQUEST variable.
  • --->
  • <poi:document
  • name="REQUEST.ExcelData"
  • file="#ExpandPath( './test.xls' )#"
  • style="font-family: verdana ; font-size: 10pt ; color: black ; white-space: nowrap ;">
  •  
  • <!--- Define style classes. --->
  • <poi:classes>
  •  
  • <poi:class
  • name="title"
  • style="font-family: arial ; color: white ; background-color: green ; font-size: 18pt ; text-align: left ;"
  • />
  •  
  • <poi:class
  • name="header"
  • style="font-family: arial ; background-color: lime ; color: white ; font-size: 14pt ; border-bottom: solid 3px green ; border-top: 2px solid white ;"
  • />
  •  
  • </poi:classes>
  •  
  • <!--- Define Sheets. --->
  • <poi:sheets>
  •  
  • <poi:sheet name="Smokin' Hotties">
  •  
  • <!--- Define global column styles. --->
  • <poi:columns>
  • <poi:column style="width: 50px ; text-align: center ;" />
  • <poi:column style="width: 150px ;" />
  • <poi:column style="width: 130px ;" />
  • <poi:column style="width: 100px ; text-align: center ;" />
  • <poi:column style="width: 150px ; text-align: left ;" />
  • </poi:columns>
  •  
  •  
  • <!--- Title row. --->
  • <poi:row class="title">
  • <poi:cell value="Hot Celebrity Action" colspan="5" />
  • </poi:row>
  •  
  • <!--- Header row. --->
  • <poi:row class="header">
  • <poi:cell value="Rank" />
  • <poi:cell value="Name" />
  • <poi:cell value="Best Feature" />
  • <poi:cell value="Hotness" />
  • <poi:cell value="Last Fantasy" />
  • </poi:row>
  •  
  • <!--- Output the people. --->
  • <cfloop query="qPeople">
  •  
  • <poi:row>
  • <poi:cell type="numeric" value="#qPeople.rank#" />
  • <poi:cell value="#qPeople.name#" />
  • <poi:cell value="#qPeople.best_feature#" />
  • <poi:cell type="numeric" numberformat="0.00" value="#qPeople.hotness#" />
  • <poi:cell type="date" value="#qPeople.last_fantasy#" />
  • </poi:row>
  •  
  • </cfloop>
  •  
  • </poi:sheet>
  •  
  • </poi:sheets>
  •  
  • </poi:document>

A few little cool things here. For starters, notice that we are using both the File and Name property of the document tag. These values are not mutually exclusive; you can store to a variable and to a file in the same stroke. Notice also that the document tag as a Style attribute. This sets up the default Cell CSS for the entire workbook. Every cell will inherit from that value before it checks the Column, Row, and then Cell class and style attributes. The rest is pretty self-explanatory. To output values, you can use either the Value attribute of the cell or generated content (space between the opening and closing cell tags).

On both rows and cells, you have the option to use the Index attribute. By default, these rows and cells fall one after another; however, by using the Index value, you have the ability to skip rows and cells in the output. I didn't use it in this example, but it is a useful feature, especially when you want to skip rows for formatting issues.

Running the above code, we get the following native Excel file, complete with CSS-driven formatting:


  
  
  

 
Excel Document Created In ColdFusion Using POI Custom Tags  
 
  
  

I think that's looking pretty good and with very minimal amount of ColdFusion Custom Tag coding on the user's end. I will probably clean this up a bit and release an Alpha of the custom tags. There's a bunch more that I plan on doing to this.

Thanks to everyone for being patient with my absence lately.




Reader Comments

Bharat Patel
Jul 11, 2011 at 4:53 AM // reply »
1 Comments
12 Points

I have used POI Utility in my one of project. I have shown percentage in excel column but I can not take it in numeric with numberformat 0.0.

Actually, I want percentage in numeric with one decimal point. I have used ColdFusion to render excel export code.


Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:
Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>







  • Help Wanted - Find Your Next ColdFusion Job
Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Joel
May 22, 2013 at 5:35 PM
Script Tags, jQuery, And Html(), Text() And Contents()
This is still an issue 2 years later. jQuery is supposed to remediate these cross browser issues, no? I have been unable to find any statement from the jQuery team calling this behavior "by de ... read »
Phillip Senn
May 22, 2013 at 12:44 PM
Ask Ben: Query Loop Inside CFScript Tags
In cf10, if you call a function that has: local.result = {}; local.result.msg = ""; local.svc = new query(); local.svc.setSQL("SELECT * FROM..."); local.obj = local.svc.exe ... read »
Dan G. Switzer, II
May 22, 2013 at 12:29 PM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Ben: What version of Java are you using? Also, did you test users.id to see what Java reports as the data type? I wonder if it's not a Java primitive data type, but getting returned as something ... read »
Ben Nadel
May 22, 2013 at 11:47 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Dana, Awesome - so it looks like this bug was fixed in ColdFusion 10. Thanks so much for double-checking that. ... read »
DanaK
May 22, 2013 at 11:37 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
When I c&p and run on cf10, I get: Selected User IDs: 1,4 User 1 selected: YES - YES User 2 selected: NO - NO User 3 selected: NO - NO User 4 selected: YES - YES User 5 selected: NO - ... read »
Ben Nadel
May 22, 2013 at 11:27 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Tom, Good thought, but no dice. Both of these still exhibit the same behavior: users.id[ users.currentRow ] users[ "id" ][ users.currentRow ] It's just something whacky happening with ... read »
Tom Eldredge
May 22, 2013 at 11:07 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
Could your problem be that "users.id" is actually an ARRAY, not a single value? Perhaps try it again with "users.id[1]" (I only have CF8 here at work). ... read »
mrsean2k
May 22, 2013 at 7:52 AM
Nested Views, Routing, And Deep Linking With AngularJS
Hi, Just a quick thank you. As it happens, for my own purposes, the pending ui-router work being done in native angular is likely the one I'll adopt, but your exploration, code and documentation of ... read »
InVision App - Prototyping Made Beautiful With Prototyping Tools