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
Brandon
May 23, 2013 at 9:52 PM
Preventing Links In Standalone iPhone Applications From Opening In Mobile Safari
@Muhmmadibn Did you figure out a solution to launching PDFs? I am running into the same issues myself. There is no way to close the PDF or go back once you launch it. Thanks in advance! ... read »
Dr Amadi
May 23, 2013 at 6:06 PM
The Girl Who Broke My Heart, And Made Me A Better Person
Good day,ladies and gentle men, my name is Dr AMADI the great spell caster in Africa, i have help so many people for different kind of problems,who say there is no solution to problems on earth, that ... read »
Ben Nadel
May 23, 2013 at 4:26 PM
ColdFusion QueryAppend( qOne, qTwo )
@Heather, Glad people are still getting value out of this! ... read »
Ben Nadel
May 23, 2013 at 3:49 PM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@WebManWalking, I meant the code at the bottom (not the video). I did try to experiment with an intermediary variable, like: value = users.id[ i ]; arrayContains( userIDs, value ); ... but t ... read »
WebManWalking
May 23, 2013 at 11:06 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Ben, Are you talking about As Number: YES As String: YES As Java: YES? If so, that's with 3 different ways of referencing the constant 1, not users.id[1]. Query object references(*) are what seem ... read »
Ben Nadel
May 23, 2013 at 9:55 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Dan, According to the CF Admin, I'm running Java "1.6.0_45". As far as the DB column, in the database it's an INT. I'll see if I can dig into what CF sees it as. @WebManWalking, But h ... read »
WebManWalking
May 23, 2013 at 9:49 AM
Strange Interaction Between DeserializeJson(), ArrayContains(), And Database Values In ColdFusion
@Ben, I think the problem is that we're used to loose typing in ColdFusion, like JavaScript. If a value is a number but it's needed in an expression to be a string, noooo problem. I've encountered ... read »
Heather
May 23, 2013 at 9:47 AM
ColdFusion QueryAppend( qOne, qTwo )
You rock! Thank you, thank you, thank you!!! ... read »
InVision App - Prototyping Made Beautiful With Prototyping Tools