POI ColdFusion Custom Tags Almost There

Posted March 24, 2008 at 9:24 AM

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:

 Launch code in new window » Download code as text file »

  • <!--- 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):

 Launch code in new window » Download code as text file »

  • <!--- 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.

Download Code Snippet ZIP File

Comments (0)  |  Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page




Adobe ColdFusion 8.0.1 Update - Helping Programmers To Be Signifanctly Less Girlie - Download ColdFusion 8 Update 8.0.1 Now.

Reader Comments

There are no comments posted for this web log entry.


Post Comment  |  Ask Ben


Home   |   Web Log   |   ColdFusion   |   Projects   |   Resume   |   Job Form   |   Search   |   Contact
Epicenter Consulting - Custom Software Solutions for Business Evolution HostMySite.com - The Leader In ColdFusion Hosting