Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with:

POI ColdFusion Custom Tags First Release

By Ben Nadel on
Tags: ColdFusion

I have just posted up a fresh build of my POIUtility.cfc project. Now, the project contains more than just the POIUtility.cfc; it also contains all the ColdFusion custom tags and some additional CSS-based components that can be used to create Microsoft Excel files in ColdFusion. Here is the ColdFusion custom tag read me that I have included in the build:

These ColdFusion custom tags allow you to create native Microsoft Excel binary files. They create PRE-2007 compatible files. The following is a list of the currently supported tags and the current attributes.

NOTE: All tags in the POI systems require the use of both an OPENING and CLOSING tag. If you leave out a closing tag (or self-closing tag), you will get unexpected results.

Document

Name: [optional] If provided, will store a copy of the Excel file in a ByteArrayOutputStream that can easily be converted to a byte array and streamed to the browser using CFContent or written to the file system using CFFile.

File: [optional] If provided, will store a copy of the Excel file at the given expanded file path.

Style: [optional] Sets default CSS styles for all cells in the document.

** Note: Name and File are optional, but ONE of them is required.

Classes

** No functional value other than containership at this time.

Class

Name: The name of the class (to be used as a struct-key) holding the given CSS styles.

Style: The CSS style for this class.

** Note: You can use the class name "@cell" to override the default cell style for the entire workbook.

Sheets

** No functional value other than containership at this time.

Sheet

Name: The name of the sheet to be displayed in the tab at the bottom of the workbook.

Columns

** No functional value other than containership at this time.
** This section is optional.

Column

Index: [optional] The zero-based index of the column. By default, this will start at zero and increment for each column.

Class: [optional] The class names (defined above) that should be applied to this column. This can be a single class or a space-delimited list of classes (to be taken in order).

Style: [optional] The CSS styles that should be applied to this column.

Row

Index: [optional] The zero-based index of this row. By default, this will start at zero and increment for each row. If you set this manually, all subsequent rows will start after the previous one.

Class: [optional] The class names (defined above) that should be applied to this row. This can be a single class or a space-delimited list of classes (to be taken in order).

Style: [optional] The CSS styles that should be applied to this row.

Cell

Type: [optional] Type of data in the cell. By default, everything is a string. Currently, can also be Numeric or Date.

Index: [optional] The zero-based index of this cell. By default, this will start at zero and increment for each cell. If you set this manually, all subsequent cells in this row will start after the previous one.

Value: [optional] The value to be used for the cell output. If this is not provided, then the GeneratedContent of the cell tag will be used (space between the opening and closing tags).

ColSpan: [optional] Defaults to one; allows you to create merged cells in a horizontal way.

NumberFormat: [optional] The number mask of the numeric cell. Only a limited number of masks are available.

DateFormat: [optional] The date mask of the date cell. Only a limited number of masks are available.

Class: [optional] The class names (defined above) that should be applied to this cell. This can be a single class or a space-delimited list of classes (to be taken in order).

Style: [optional] The CSS styles that should be applied to this cell.

While I don't yet understand all of them yet, here are the data masks that are available:

Available Number Formatting Masks

"General"
"0"
"0.00"
"#,##0"
"#,##0.00"
"($#,##0_);($#,##0)"
"($#,##0_);[Red]($#,##0)"
"($#,##0.00);($#,##0.00)"
"($#,##0.00_);[Red]($#,##0.00)"
"0%"
"0.00%"
"0.00E+00"
"# ?/?"
"# ??/??"
"(#,##0_);[Red](#,##0)"
"(#,##0.00_);(#,##0.00)"
"(#,##0.00_);[Red](#,##0.00)"
"_(*#,##0_);_(*(#,##0);_(* \"-\"_);_(@_)"
"_($*#,##0_);_($*(#,##0);_($* \"-\"_);_(@_)"
"_(*#,##0.00_);_(*(#,##0.00);_(*\"-\"??_);_(@_)"
"_($*#,##0.00_);_($*(#,##0.00);_($*\"-\"??_);_(@_)"
"##0.0E+0"
"@" - This is text format.
"text" - Alias for "@"

Available Date Formatting Masks

"m/d/yy"
"d-mmm-yy"
"d-mmm"
"mmm-yy"
"h:mm AM/PM"
"h:mm:ss AM/PM"
"h:mm"
"h:mm:ss"
"m/d/yy h:mm"
"mm:ss"
"[h]:mm:ss"
"mm:ss.0"

There is, of course, also an example write file that shows you how to use the ColdFusion custom tags. It both writes the Excel file to disk and streams it as binary content to the user's browser. For any of those interested, here is the example (notice the streaming code at the end):

  • <!--- 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" ) />
  •  
  •  
  • <!--- 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( './celebrities.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>
  •  
  •  
  •  
  • <!--- Tell the browser to expect an Excel file attachment. --->
  • <cfheader
  • name="content-disposition"
  • value="attachment; filename=celebrities.xls"
  • />
  •  
  • <!---
  • Tell browser the length of the byte array output stream.
  • This will help the browser provide download duration to
  • the user.
  • --->
  • <cfheader
  • name="content-length"
  • value="#REQUEST.ExcelData.Size()#"
  • />
  •  
  • <!--- Stream the binary data to the user. --->
  • <cfcontent
  • type="application/excel"
  • variable="#REQUEST.ExcelData.ToByteArray()#"
  • />

I have a lot more than I want to do (and have been asked to do) with this. So hopefully more updates will start popping up with this.

Tweet This Titillating read by @BenNadel - POI ColdFusion Custom Tags First Release Thanks my man — you rock the party that rocks the body!



Reader Comments

@Ryan,

Thanks man. Glad to know someone is finding it exciting. I am definitely looking forward to using this in my projects.

Soon, I want to add things like freezing areas and adding images and graphics and what not (fingers crossed).

Reply to this Comment

Ben,

Thank you so much for updating this. I've been using the CFC for a while for processing XLS file uploads and importing data into a database.

This tool has saved me countless hours.

Great work.

Reply to this Comment

Ben,

This looks really cool. Of course I have a fetaure request... Are you planning to add the ability to edit an existing excel file?

Reply to this Comment

In case you need landscape mode for a sheet (and I might have missed it if it was built in already in the code somewhere else), add this to the sheet.cfm:

<cfparam name="ATTRIBUTES.Landscape"
type="boolean"
default="false"
/>

<cfset VARIABLES.printSetup = VARIABLES.Sheet.getPrintSetup() />
<cfset VARIABLES.printSetup.setLandscape(attributes.landscape) />

Then in your poi tags, do something like so:

<poi:sheet name="#reportTitle#" landscape="true">

The important part being to set landscape="true".

Reply to this Comment

@Ed,

I am not sure how I will do that with the ColdFusion Custom Tags; I think just maybe an [action="insert" index="2"] on the sheet or something.

@Dan,

Thanks for the quality suggestion. I will try to integrate this.

Reply to this Comment

Ben, This POI Utility for CF totally rocks. I have been getting pretty frustrated will some of the other cf2excel solutions out there.

FYI: I was digging around in your POI customtags for the XML based version trying to solve a problem I was having with Dollar Formating. Specifically I was in the "cell.cfm" file where I found a reference to "ATTRIBUTES.DateFormat" in the "numeric" cfswith/case where you are checking for bad NumberFormats being passed in. It looks like it needs to be "ATTRIBUTES.NumberFormat"... Is my thinking correct?

Thanks for all your work on this!!!
Greg

Reply to this Comment

@Greg,

When I started writing that, I thought I could separate out date and numeric formatting. However, after further reading, it looks like date and numeric formatting are handled in the same way in th Excel document.

I have to make another round of updates, especially to handle the FONT problem. I will clean that stuff up.

Reply to this Comment

How can I use this with my server. I don't have ColdFusion admin rights but I do have admin rights to my space.

Reply to this Comment

Ben,
I like what you are doing here. I have tried using XML, but I am not having much success. I thought I'd try this code.
What I need is a way to take a HTML page that displays data and export it out to an excel file. Yes this is easy on a basic level. However, I need the excel file to print out in landscape. I have been looking at this POI stuff, and it seems promising. Maybe I am missing something in how to export HTML data into an excel file...if I am can you give me an explanation. If there is not an easy way to get the data into a landscape excel file, is there a way you could update your custom tags to add the print orientation variable?

thanks
dan

Reply to this Comment

Ben,

This tag is amazing and is something that should have been created years ago. I can recall trying to get this in-depth with Excel yrs ago via Coldfusion and nothing was available. On another note, it is nice to see someone we can now turn to with CF help/ideas. The CF community I felt has shrunk a great deal over the past year or two.

Quick quesiton. I am trying to develop a new number mask which seems to be in the 'Custom' mask settings in Excel when formatting a cell. Essentially I would like to use the percentage mask '0.00%' but add a comma to it for percentages which are large like 333,434%.

I have taken the steps of adding a new mask, "(##,####0.00%);[Red](-##,####0.00%)", to the document.cfm variables page and then applied it to the numbermask in the actual page itself. When I run the query I do not get the % at the end of the number. Am I missing something?

Thanks,

Jeremy

Reply to this Comment

@Jeremy,

I am not 100% if / how custom masks get added. When I have some time to looking into it, I will. Sorry I could not be more help.

Reply to this Comment

Hi Ben,

Is there any way to retrieve data from .xls sheet to query object without using POI components ?

Reply to this Comment

Hey Ben

This poi utitlity is really great and I have been able to do a lot with it.

My only issue is that on some of my longer pages i like to have output to the page saying which company we are processing or a percentage of where we are in the page, etc. It seems when invoking the poi you can not do a cfflush and see any output in that same page

Any ideas how to change that so we can have some output status via scfflush while using the poi custom tag on the page?

Thanks again
Matt

Reply to this Comment

@Matt,

I am not sure that we can gain any insight into the process while the custom tags are processing since ColdFusion doesn't allow custom tag content to be flushed (as the end-tag has a change to reset it). Sorry :(

Reply to this Comment

@Ben,

How about if the majority of the page was created in a cfsavecontent and then output at the end of the page so that we could output progress as the page is running before calling poi:document. Do you think that would work to start with the first poi:Sheet call in a savecontent through the last </poi:sheet> call and then at the end of the page do the <poi:document> <poi:sheet> <cfoutput>#thesavecontentvariable#</cfoutput></poi:sheets></poi:document>

Thanks again,
Matt

Reply to this Comment

@Matt,

Nothing can actually be flushed to the client from within a custom tag; ColdFusion just won't let you do it. If you wanted to get really tricky, what you could probably do is launch the POI stuff in a CFThread tag and then post updates to "VARIABLES" scope of the main page as the document is processing (and have the main page - outside of the CFThread - communicate back to the client.

The biggest problem with this is that the file cannot be returned with that request; once output is committed to the client, you can no longer overwrite the output with the XLS binary. However, you could write the file to disk and then re-direct the user to it once it was completed.

... that's actually an interesting idea. Perhaps I can play around with the concept a bit.

Reply to this Comment

@Ben,

Nice - i knew if you wrote out how one couldn't do it you'd probably stumble on how one actually can do it. I have not used cfthread before so will need to research and play with that some but let me know if you come up with something as I will not be able to get to this for a minute and you'll probably nail it much quicker than i would!

Thanks again,
Matt

Reply to this Comment

Ah mate thanks a lot. I've been looking for a lot of jQuery tutorials regarding charts and i stumbled upon this.Just wanted to say thank you for the help.

Reply to this Comment

Hey Ben

I had added the ability to pass headers and footers for printing, as well as setup a default print area to fit layout in 1 page width to the POIUtility.CFC

I just went ahead and added them into the sheet.cfm in case any one searches for print area, printer header and footer, etc. You can use left, center and right in header and footer.

There may be a better way to pass it but I pass it as 6 comma separated strings. Haven't really played with or tested maximums, but it gives nice control and in one usage I have an account name and address with no issue in the header left section.

Feel free to include it in an update and/or improve upon it!

<!---List of up to 6 comma separated strings for header left,header center, header right,footer left,footer center,footer right --->
<cfparam name="ATTRIBUTES.HeaderFooter"
type="string"
default=""
/>

then in the "End" section after the FreezeRow logic and before the landscape/portrait logic, first we make the page work on 8.5" x 11" print out with basic functionality (that could be expanded and made an argument)..

<!--- Fit to an 8" wide page...for a small level of auto formatting pre-Excel... this could be made dynamic for different paper sizes--->
<cfset VARIABLES.Sheet.setAutobreaks(
JavaCast( "boolean", true )
) />
<cfset VARIABLES.Sheet.GetPrintSetup().setFitHeight(
JavaCast( "int", 7 )
) />
<cfset VARIABLES.Sheet.GetPrintSetup().setFitWidth(
JavaCast( "int", 1 )
) />

Finally the code to set the up to 6 sections of header and footer...

<cfset printHeaderFooter = ListToArray(
ATTRIBUTES.HeaderFooter,
","
)>
<cfloop from="1" to="#listlen(printHeaderFooter)#" index="j">
<cfswitch expression="#j#">
<cfcase value=1>
<cfset VARIABLES.Sheet.getHeader().setLeft(
JavaCast(
"string",
printHeaderFooter[1]
)
) />
</cfcase>
<cfcase value=2>
<cfset VARIABLES.Sheet.getHeader().setCenter(
JavaCast(
"string",
printHeaderFooter[2]
)
) />
</cfcase>
<cfcase value=3>
<cfset VARIABLES.Sheet.getHeader().setRight(
JavaCast(
"string",
printHeaderFooter[3]
)
) />
</cfcase>
<cfcase value=4>
<cfset VARIABLES.Sheet.getFooter().setLeft(
JavaCast(
"string",
printHeaderFooter[4]
)
) />
</cfcase>
<cfcase value=5>
<cfset VARIABLES.Sheet.getFooter().setCenter(
JavaCast(
"string",
printHeaderFooter[5]
)
) />
</cfcase>
<cfcase value=6>
<cfset VARIABLES.Sheet.getFooter().setRight(
JavaCast(
"string",
printHeaderFooter[6]
)
) />
</cfcase>
</cfswitch>
</cfloop>

Anyways hope it helps some folks.

Matt

Reply to this Comment

Sorry the loop in the paste above is not correct it should be

<cfloop from="1" to="#listlen(ATTRIBUTES.HeaderFooter)#" index="j">

Matt

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
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.