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
InVision App - Prototyping Made Beautiful With Prototyping Tools Ben Nadel's Company - Epicenter Consulting Recent Blog Comments
Misty
Feb 12, 2012 at 3:37 AM
Learning ColdFusion 8: CFImage Part III - Watermarks And Transparency
Hi Ben, Just to ask currently it is placed bottom right corner, if i need to replace the same rendered image on the bottom left side or in the bottom center, how that can be calculated. bottom ce ... read »
Raphael Mobis
Feb 11, 2012 at 9:29 PM
Use jQuery's SlideDown() With Fixed-Width Elements To Prevent Jumping
I can't say how glad I am that I found your post. Thank you very much. ... read »
Bartezz
Feb 10, 2012 at 7:21 PM
jQuery AJAX Strips Script Tags And Inserts Them After Parent-Most Elements
Update! Instead of $(eval(options.insertAfter)).after(data['insertData']); I now use: var ajaxNode = document.createElement('span'); var parent = $(eval(options.insertAfter))[0].parentNode; ... read »
Bartezz
Feb 10, 2012 at 6:18 PM
jQuery AJAX Strips Script Tags And Inserts Them After Parent-Most Elements
encountered this same, what I consider, jQuery bug last week. I'm building a site in which I load some content via AJAX. This content contains Linkedin share button placeholders which Linkedin API ne ... read »
Drew Wells
Feb 10, 2012 at 11:30 AM
Cross-Origin Resource Sharing (CORS) AJAX Requests Between jQuery And Node.js
After you understand the concepts here, this is an awesome cheatsheet for enabling CORS in just about anything http://enable-cors.org/ ... read »
JM
Feb 10, 2012 at 9:10 AM
My Safari Browser SQLite Database Hello World Example
@Amy, Here is a very good tutorial on how to use JOIN: http://www.sqltutorial.org/sqljoin-innerjoin.aspx ... read »
Stefan Richter
Feb 10, 2012 at 4:42 AM
Building A Twitter-Inspired RESTful API Architecture In ColdFusion
This is great, very useful Ben. I spotted a small typo in the api.cgm listing: <cfthrow type="Unauthroized" /> Cheers Stefan ... read »
Hugh
Feb 9, 2012 at 10:35 PM
CFDirectory Filtering Uses Pipe Character For Multiple Filters (Thanks Steve Withington)
I was wondering if there would be a filter you could apply so that you got everything but what you included in the filter. As in show me all docs that are not a .pdf. ... read »