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 cf.Objective() 2011 (Minneapolis, MN) with:

Creating Microsoft Excel Files Using ColdFusion And POI

By Ben Nadel on
Tags: ColdFusion

I have been messing around with creating Excel documenting using ColdFusion and XML among various other techniques. I have heard people mention using the POI package with ColdFusion, but at the time I was already messing around with JExcel, so I didn't pursue. But then, just recently, Rob Brooks-Bilson pointed out to me that ColdFusion comes with a slightly older version of POI as part of its installation.

What?? Time to investigate!

After some very quick Goggling, I found a rather excellent summarization of the POI features by Dave Ross. This is one awesome summary - check it out if you have not. I can't believe that was posted back in 2004 and I am only NOW finding it. I am such a slacker!

Anyway, I took the basics of Dave's demo and build a ColdFusion function that takes a query and writes the data to a Microsoft Excel file. I haven't messed around with any formatting issues or multiple sheets or anything like that, just a straight up data grid.

To start with, I built a query from which to test:

  • <!--- Create query to ouptut to excel. --->
  • <cfset qMovie = QueryNew(
  • "id, name, date_watched, rating, has_fighting, has_boobies",
  • "CF_SQL_INTEGER, CF_SQL_VARCHAR, CF_SQL_DATE, CF_SQL_DECIMAL, CF_SQL_BIT, CF_SQL_BIT"
  • ) />
  •  
  • <!--- Add rows to query. --->
  • <cfset QueryAddRow( qMovie, 5 ) />
  •  
  • <!--- Set row data. --->
  • <cfset qMovie[ "id" ][ 1 ] = JavaCast( "int", 1 ) />
  • <cfset qMovie[ "name" ][ 1 ] = JavaCast( "string", "Terminator 2" ) />
  • <cfset qMovie[ "date_watched" ][ 1 ] = JavaCast( "float", "2006/05/25" ) />
  • <cfset qMovie[ "rating" ][ 1 ] = JavaCast( "float", 10.0 ) />
  • <cfset qMovie[ "has_fighting" ][ 1 ] = JavaCast( "boolean", true ) />
  • <cfset qMovie[ "has_boobies" ][ 1 ] = JavaCast( "boolean", false ) />
  •  
  • <cfset qMovie[ "id" ][ 2 ] = JavaCast( "int", 2 ) />
  • <cfset qMovie[ "name" ][ 2 ] = JavaCast( "string", "American Pie" ) />
  • <cfset qMovie[ "date_watched" ][ 2 ] = JavaCast( "float", "2005/08/02" ) />
  • <cfset qMovie[ "rating" ][ 2 ] = JavaCast( "float", 9.0 ) />
  • <cfset qMovie[ "has_fighting" ][ 2 ] = JavaCast( "boolean", false ) />
  • <cfset qMovie[ "has_boobies" ][ 2 ] = JavaCast( "boolean", true ) />
  •  
  • <cfset qMovie[ "id" ][ 3 ] = JavaCast( "int", 3 ) />
  • <cfset qMovie[ "name" ][ 3 ] = JavaCast( "string", "Friends With Money" ) />
  • <cfset qMovie[ "date_watched" ][ 3 ] = JavaCast( "float", "2006/06/21" ) />
  • <cfset qMovie[ "rating" ][ 3 ] = JavaCast( "float", 8.0 ) />
  • <cfset qMovie[ "has_fighting" ][ 3 ] = JavaCast( "boolean", false ) />
  • <cfset qMovie[ "has_boobies" ][ 3 ] = JavaCast( "boolean", false ) />
  •  
  • <cfset qMovie[ "id" ][ 4 ] = JavaCast( "int", 4 ) />
  • <cfset qMovie[ "name" ][ 4 ] = JavaCast( "string", "Better Than Chocolate" ) />
  • <cfset qMovie[ "date_watched" ][ 4 ] = JavaCast( "float", "2006/10/07" ) />
  • <cfset qMovie[ "rating" ][ 4 ] = JavaCast( "float", 8.5 ) />
  • <cfset qMovie[ "has_fighting" ][ 4 ] = JavaCast( "boolean", true ) />
  • <cfset qMovie[ "has_boobies" ][ 4 ] = JavaCast( "boolean", true ) />
  •  
  • <cfset qMovie[ "id" ][ 5 ] = JavaCast( "int", 5 ) />
  • <cfset qMovie[ "name" ][ 5 ] = JavaCast( "string", "Real Genius" ) />
  • <cfset qMovie[ "date_watched" ][ 5 ] = JavaCast( "float", "2006/12/12" ) />
  • <cfset qMovie[ "rating" ][ 5 ] = JavaCast( "float", 9.0 ) />
  • <cfset qMovie[ "has_fighting" ][ 5 ] = JavaCast( "boolean", false ) />
  • <cfset qMovie[ "has_boobies" ][ 5 ] = JavaCast( "boolean", false ) />

... then I defined the ColdFusion User Defined Function (UDF), CreateXlsFromQuery(), which takes a file path and ColdFusion query as required arguments. Optionally, this UDF can take a list of column names and a custom-ordered column list. The presence of the column names precipitates the creation of a header row.

  • <!--- Set up the function. --->
  • <cffunction
  • name="CreateXlsFromQuery"
  • access="public"
  • returntype="boolean"
  • output="false"
  • hint="Takes a query and a file path and creates an Microsoft Excel file.">
  •  
  • <!--- Define arguments. --->
  • <cfargument
  • name="FilePath"
  • type="string"
  • required="true"
  • hint="This is the expanded path of the target XLS file."
  • />
  •  
  • <cfargument
  • name="Query"
  • type="query"
  • required="true"
  • hint="This is the query from which we will get the data."
  • />
  •  
  • <cfargument
  • name="ColumnList"
  • type="string"
  • required="false"
  • default="#ARGUMENTS.Query.ColumnList#"
  • hint="This is list of columns provided in custom-ordered."
  • />
  •  
  • <cfargument
  • name="ColumnNames"
  • type="string"
  • required="false"
  • default=""
  • hint="This the the list of optional header-row column names. If this is not provided, no header row is used."
  • />
  •  
  • <cfargument
  • name="SheetName"
  • type="string"
  • required="false"
  • default="Sheet 1"
  • hint="This is the optional name that appears in the first workbook tab."
  • />
  •  
  • <cfargument
  • name="Delimiters"
  • type="string"
  • required="false"
  • default=","
  • hint="The list of delimiters used for the column list and column name arguments."
  • />
  •  
  • <cfscript>
  •  
  • // Set up local scope.
  • var LOCAL = StructNew();
  •  
  • // Set up data type map so that we can map each column
  • // name to the type of data contained.
  • LOCAL.DataMap = StructNew();
  •  
  • // Get the meta data of the query to help us create
  • // the data mappings.
  • LOCAL.MetaData = GetMetaData( ARGUMENTS.Query );
  •  
  • // Loop over meta data values to set up the data mapping.
  • for (
  • LOCAL.MetaIndex = 1 ;
  • LOCAL.MetaIndex LTE ArrayLen( LOCAL.MetaData ) ;
  • LOCAL.MetaIndex = (LOCAL.MetaIndex + 1)
  • ){
  •  
  • // Map the column name to the data type.
  • LOCAL.DataMap[ LOCAL.MetaData[ LOCAL.MetaIndex ].Name ] = LOCAL.MetaData[ LOCAL.MetaIndex ].TypeName;
  • }
  •  
  • // Create Excel workbook.
  • LOCAL.WorkBook = CreateObject(
  • "java",
  • "org.apache.poi.hssf.usermodel.HSSFWorkbook"
  • ).Init();
  •  
  • // Create the first sheet in the workbook.
  • LOCAL.Sheet = LOCAL.WorkBook.CreateSheet(
  • JavaCast(
  • "string",
  • ARGUMENTS.SheetName
  • )
  • );
  •  
  • // Set a default row offset so that we can keep add the
  • // header column without worrying about it later.
  • LOCAL.RowOffset = -1;
  •  
  • // Check to see if we have any column names. If we do,
  • // then we are going to create a header row with these
  • // names in order based on the passed in delimiter.
  • if (Len( ARGUMENTS.ColumnNames )){
  •  
  • // Convert the column names to an array for easier
  • // indexing and faster access.
  • LOCAL.ColumnNames = ListToArray(
  • ARGUMENTS.ColumnNames,
  • ARGUMENTS.Delimiters
  • );
  •  
  • // Create a header row.
  • LOCAL.Row = LOCAL.Sheet.CreateRow(
  • JavaCast( "int", 0 )
  • );
  •  
  • // Loop over the column names.
  • for (
  • LOCAL.ColumnIndex = 1 ;
  • LOCAL.ColumnIndex LTE ArrayLen( LOCAL.ColumnNames ) ;
  • LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)
  • ){
  •  
  • // Create a cell for this column header.
  • LOCAL.Cell = LOCAL.Row.CreateCell(
  • JavaCast( "int", (LOCAL.ColumnIndex - 1) )
  • );
  •  
  • // Set the cell value.
  • LOCAL.Cell.SetCellValue(
  • JavaCast(
  • "string",
  • LOCAL.ColumnNames[ LOCAL.ColumnIndex ]
  • )
  • );
  • }
  •  
  • // Set the row offset to zero since this will take
  • // care of the zero-based index for the rest of
  • // the query records.
  • LOCAL.RowOffset = 0;
  •  
  • }
  •  
  • // Convert the list of columns to the an array for
  • // easier indexing and faster access.
  • LOCAL.Columns = ListToArray(
  • ARGUMENTS.ColumnList,
  • ARGUMENTS.Delimiters
  • );
  •  
  • // Loop over the query records to add each one to the
  • // current sheet.
  • for (
  • LOCAL.RowIndex = 1 ;
  • LOCAL.RowIndex LTE ARGUMENTS.Query.RecordCount ;
  • LOCAL.RowIndex = (LOCAL.RowIndex + 1)
  • ){
  •  
  • // Create a row for this query record.
  • LOCAL.Row = LOCAL.Sheet.CreateRow(
  • JavaCast(
  • "int",
  • (LOCAL.RowIndex + LOCAL.RowOffset)
  • )
  • );
  •  
  • // Loop over the columns to create the individual
  • // data cells and set the values.
  • for (
  • LOCAL.ColumnIndex = 1 ;
  • LOCAL.ColumnIndex LTE ArrayLen( LOCAL.Columns ) ;
  • LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)
  • ){
  •  
  • // Create a cell for this query cell.
  • LOCAL.Cell = LOCAL.Row.CreateCell(
  • JavaCast( "int", (LOCAL.ColumnIndex - 1) )
  • );
  •  
  • // Get the generic cell value (short hand).
  • LOCAL.CellValue = ARGUMENTS.Query[
  • LOCAL.Columns[ LOCAL.ColumnIndex ]
  • ][ LOCAL.RowIndex ];
  •  
  • // Check to see how we want to set the value.
  • // Meaning, what kind of data mapping do we
  • // want to apply? Get the data mapping value.
  • LOCAL.DataMapValue = LOCAL.DataMap[ LOCAL.Columns[ LOCAL.ColumnIndex ] ];
  •  
  • // Check to see what value type we are working
  • // with. I am not sure what the set of values
  • // are, so trying to keep it general.
  • if (REFindNoCase( "int", LOCAL.DataMapValue )){
  •  
  • LOCAL.DataMapCast = "int";
  •  
  • } else if (REFindNoCase( "long", LOCAL.DataMapValue )){
  •  
  • LOCAL.DataMapCast = "long";
  •  
  • } else if (REFindNoCase( "double", LOCAL.DataMapValue )){
  •  
  • LOCAL.DataMapCast = "double";
  •  
  • } else if (REFindNoCase( "float|decimal|real|date|time", LOCAL.DataMapValue )){
  •  
  • LOCAL.DataMapCast = "float";
  •  
  • } else if (REFindNoCase( "bit", LOCAL.DataMapValue )){
  •  
  • LOCAL.DataMapCast = "boolean";
  •  
  • } else if (IsNumeric( LOCAL.CellValue )){
  •  
  • LOCAL.DataMapCast = "float";
  •  
  • } else {
  •  
  • LOCAL.DataMapCast = "string";
  •  
  • }
  •  
  • // Cet the cell value using the data map
  • // casting that we just determined and the value
  • // that we previously grabbed (for short hand).
  • LOCAL.Cell.SetCellValue(
  • JavaCast(
  • LOCAL.DataMapCast,
  • LOCAL.CellValue
  • )
  • );
  •  
  • }
  •  
  • }
  •  
  •  
  • // Create a file based on the path that was passed in.
  • // We will stream the work data to the file via a
  • // file output stream.
  • LOCAL.FileOutputStream = CreateObject(
  • "java",
  • "java.io.FileOutputStream"
  • ).Init(
  • JavaCast(
  • "string",
  • ARGUMENTS.FilePath
  • )
  • );
  •  
  • // Write the workout data to the file stream.
  • LOCAL.WorkBook.Write(
  • LOCAL.FileOutputStream
  • );
  •  
  • // Close the file output stream. This will release any
  • // locks on the file and finalize the process.
  • LOCAL.FileOutputStream.Close();
  •  
  • // Return TRUE since we created the file properly.
  • return( true );
  •  
  • </cfscript>
  • </cffunction>

I do my best to map the ColdFusion data types to the Java data types that are passed into the POI API. This should work for most basic types. The one issue is that I don't do anything special (formatting-wise) for date/time stamps. However, the value is correct (even if it is in float format), but you have to manually set the cell formatting after the fact.

This method is then called using the query created above:

  • <!---
  • Create a Microsoft Excel file based on the movie query.
  • Provide the optional column names and the column list.
  • --->
  • <cfset CreateXlsFromQuery(
  • FilePath = ExpandPath( "./function_test.xls" ),
  • Query = qMovie,
  • ColumnNames = "ID,Name,Date Watched,Rating,Has Fighting,Has Boobies",
  • ColumnList = "id,name,date_watched,rating,has_fighting,has_boobies"
  • ) />

Notice that I have included all possible query columns in my ColumnList argument. This will include all columns in the Excel file. However, by leaving out columns from this list, those columns will be excluded from the resultant Excel file.

Once all is said and done, here is what the Excel file looks like:


 
 
 

 
ColdFusion And POI To Create Excel Files  
 
 
 

Then, if you just format the date cells manually, you will see that the format does indeed come out correctly:


 
 
 

 
ColdFusion And POI To Create Excel Files  
 
 
 

This is some really cool stuff. I was surprised at how easy it was. How nice is it that ColdFusion comes with the POI package already installed?!? Freakin' sweet-ass is how nice it is.

So, to date, I now have five methods for creating Microsoft Excel files using ColdFusion:

  1. POI
  2. JExcel (still working on getting that up and running)
  3. HTML table
  4. CSV file
  5. XML Excel document

Life is looking pretty sweeeet!



Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

CRAP! I can't figure out where this 500 null is coming from! Where there any fields you left blank (or checkboxes not checked)? Thanks for alerting me - I never get the errors and the 500 nulls don't seem to get emailed to me.

Also, thanks a lot for liking the example. This POI stuff is pretty cool (I should learn to listen to people when the recommend things ;)).

Reply to this Comment

The Send me a copy thing is the only thing I have updated recently. It must be that! Thanks for helping me narrow it down. You rock.

Reply to this Comment

I tried running the example in 6.1 and get:

Object of type class java.lang.Class cannot be used as an array


The error occurred in D:\websites\resort\CreateXlsFromQuery.cfm: line 83

81 : for (
82 : LOCAL.MetaIndex = 1 ;
83 : LOCAL.MetaIndex LTE ArrayLen( LOCAL.MetaData ) ;
84 : LOCAL.MetaIndex = (LOCAL.MetaIndex + 1)
85 : ){

Bummer :(

Reply to this Comment

@Matt,

Wooohooo! I figured out the comment posting issue. I had cleaned up the COOKIE setup and had left in a line that referenced an old cookie. Since I still had my old cookie I was never getting the error. However, anyone who didn't have old cookie would get a 500 error - I was trying to reverence a key that was not in the COOKIE scope.

Should be all good now. Thanks for helping me narrow it down.

Reply to this Comment

@Peter,

I am working in ColdFusion MX 7, so I can't test the problem you are having with 6.1. However, from the Live Docs, it looks like GetMetaData() support for queries was only added in ColdFusion MX 7.

I am not even sure what that would do in MX 6. If you want, do:

<cfdump var="#GetMetaData( qMovie )#" />

And take a screen shot and sending to me and I will update the method based on what you send. Although, it might crap out for 6, in which case, I would have to re-engineer the way it is done.

To get around this, what you could do is pass in a list of data types to the method (which is basically what I am getting from the Meta Data). If you want, I can give that a go.

Reply to this Comment

Havent tested this on 6.1 fully, but I have used some of this in the past. Its part of the "Undocumented CFMX", so be aware of that.

<cfscript>
columnList = qMovie.getmetaData().getColumnLabels();
columnType = ArrayNew(1);
for(i=1;i lt ArrayLen(qMovie.getMetaData().getColumnLabels()); i = i+1)
{
metadata = qMovie.getMetaData();
column = metadata.getColumnType(qMovie.findColumn(columnList[i]));
type = qMovie.getColumnTypeName(column);
arrayAppend(columnType,type);
//writeOutput(i & ":" & type);
}
</cfscript>

columnList is an array of the column names in the same order as in your query.

columnType is an array of the types.

You will need to integrate this into Ben's method to replace his GetMetaData(query) call.

Reply to this Comment

To make the example work on CF MX 6.1 simply make the following changes:

Add an argument parameter:
<cfargument
name="DataTypes"
type="string"
required="false"
default=""
hint="This the the list of optional column data types. If this is not provided, all columns default to CF_SQL_VARCHAR."
/>

Change the metadata data-mapping code:
// Get the meta data of the query to help us create
// the data mappings.
LOCAL.MetaData = ARGUMENTS.Query;

// Loop over meta data values to set up the data mapping.
for (
LOCAL.MetaIndex = 1 ;
LOCAL.MetaIndex LTE ListLen( ARGUMENTS.ColumnList ) ;
LOCAL.MetaIndex = (LOCAL.MetaIndex + 1)
){

// Map the column name to the data type.
if (ListLen( ARGUMENTS.DataTypes ) EQ ListLen( ARGUMENTS.ColumnList )) {
LOCAL.DataMap[ ListGetAt(ARGUMENTS.ColumnList,LOCAL.MetaIndex) ] = ListGetAt(ARGUMENTS.DataTypes,LOCAL.MetaIndex);
}
else {
LOCAL.DataMap[ ListGetAt(ARGUMENTS.ColumnList,LOCAL.MetaIndex) ] = "CF_SQL_VARCHAR";
}
}

Remove the data types from the query instantiation:
<!--- Create query to ouptut to excel. --->
<cfset qMovie = QueryNew(
"id, name, date_watched, rating, has_fighting, has_boobies"
) />

And add the DataTypes attribute to the function call:
<cfset CreateXlsFromQuery(
FilePath = ExpandPath( "./function_test.xls" ),
Query = qMovie,
ColumnNames = "ID,Name,Date Watched,Rating,Has Fighting,Has Boobies",
ColumnList = "id,name,date_watched,rating,has_fighting,has_boobies",
DataTypes = "CF_SQL_INTEGER, CF_SQL_VARCHAR, CF_SQL_DATE, CF_SQL_DECIMAL, CF_SQL_BIT, CF_SQL_BIT"
) />

The simplest way to test this is to take the function definition, the query construction, and the function call and place them one after another in a single CF file. Make the edits above and run. It works perfectly.

Feel free to email me for modified code if you're still having trouble:

day@azimuthonline.com

Reply to this Comment

This may be an easy question, but I'm rusty at coding.

I want to create a report of awards processed the previous week - no problem. I've already got the report created.

I have also created a version of the report that creates an Excel file.

What I can't figure out is whether or not I can auto-save that Excel file to the web server and send it to someone via email.

Can you help? I've been searching online for the answer and can't find it.

Thanks!

My code for creating the Excel file is:
<cfset Filename= "WeeklyAwardDetailsReport.xls">
<cfheader name="Content-Disposition" value="attachment; filename=#FileName#">
<cfcontent type="application/vnd.ms-excel">

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.