POI ColdFusion Custom Tags First Release
Posted March 25, 2008 at 9:02 AM
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):
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" ) />
-
-
- <!--- 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.
Download Code Snippet ZIP File
Post Comment | Ask Ben | Other Searches | Print Page
Newer Post
Pete Freitag At The New York ColdFusion User Group
Older Post
POI ColdFusion Custom Tags Almost There
Reader Comments
Dude, these tools rock. Thanks. Great posts in general by the way.
@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).
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.
@Mario,
Sounds like a cool process you have going on there.
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?
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".
@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.
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
@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.
How can I use this with my server. I don't have ColdFusion admin rights but I do have admin rights to my space.
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
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
@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.
Hi Ben,
Is there any way to retrieve data from .xls sheet to query object without using POI components ?
