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() 2009 (Minneapolis, MN) with:

Creating Excel Files With ColdFusion, XML, And POI

By Ben Nadel on
Tags: ColdFusion

I have been fairly happy with my POIUtility.cfc ColdFusion component. It makes converting ColdFusion queries to Microsoft Excel files really easy. But, it's only easy and truly useful if the target Excel file doesn't need any kind of special formatting. It is true that even my current build has some CSS capabilities built into it, but I think there is just only so far you can go when you want to pass in an entire query with very little additional information.

As such, I have been fooling around with the idea of being able to pass a ColdFusion XML document to the POIUtility.cfc. This ColdFusion XML document would allow you to use very explicit data types and value formatting, not to mention cell-specific CSS and globally accessible CSS class rules. And, just off the top of my head, maybe even things like cell merging, targeted line-wrapping, and formulas. I figure it might look something like this:

  • <!---
  • Create the ColdFusion XML object that will be used
  • to create a Microsoft Excel document using ColdFusion
  • and the POI library.
  • --->
  • <cfxml variable="xmlData">
  •  
  • <workbook>
  •  
  • <!--- Define the global CSS classes. --->
  • <classes>
  •  
  • <!--- Global TD-Cell style. --->
  • <class
  • name="cell"
  • value="font: 12pt arial ;"
  • />
  •  
  • <!--- Additional CSS styles. --->
  • <class
  • name="header"
  • value="border-bottom: 2px solid black ; font-weight: bold ;"
  • />
  •  
  • <class
  • name="row"
  • value="border-bottom: 1px dotted gray ;"
  • />
  •  
  • </classes>
  •  
  • <!--- Define the sheets. --->
  • <sheets>
  •  
  • <sheet>
  •  
  • <name>Hot Actresses!</name>
  •  
  • <!--- Define the rows. --->
  • <rows>
  •  
  • <row class="header">
  • <cell>
  • Name
  • </cell>
  • <cell>
  • Hair Color
  • </cell>
  • <cell>
  • Hotness
  • </cell>
  • </row>
  •  
  • <row class="row">
  • <cell>
  • Christina Cox
  • </cell>
  • <cell>
  • Dirty Blonde
  • </cell>
  • <cell type="numeric" format="0.0">
  • 9.0
  • </cell>
  • </row>
  •  
  • <row class="row">
  • <cell>
  • Maura Tierney
  • </cell>
  • <cell>
  • Brunette
  • </cell>
  • <cell type="numeric" format="0.0">
  • 8.0
  • </cell>
  • </row>
  •  
  • <row class="row">
  • <cell>
  • Maria Bello
  • </cell>
  • <cell>
  • Brunette
  • </cell>
  • <cell type="numeric" format="0.0">
  • 9.5
  • </cell>
  • </row>
  •  
  • </rows>
  •  
  • </sheet>
  •  
  • </sheets>
  •  
  • </workbook>
  •  
  • </cfxml>

I haven't really thought this out fully, but as you can see from the XML, you can define CSS classes that can be applied to cells and rows (which will then be applied to all cells in that row). You can also defined the data type of each cell and even possible formatting for that data type. In my proof of concept code (below), I am not referencing most of these XML constructs, but those should be fairly easy to deal with. I just wanted to see how intuitive this kind of a setup would feel.

Here is my proof of concept code that takes the above ColdFusion XML document and creates a Microsoft Excel file using the Apache POI library:

  • <!---
  • Create a microsoft Excel workbook through the
  • POI system.
  • --->
  • <cfset objWorkbook = CreateObject(
  • "java",
  • "org.apache.poi.hssf.usermodel.HSSFWorkbook"
  • ).Init()
  • />
  •  
  •  
  • <!---
  • Get global CSS classes that have both a name
  • and a value attribute.
  • --->
  • <cfset arrCSS = XmlSearch(
  • xmlData,
  • "/workbook/classes/*[ @name and @value ]"
  • ) />
  •  
  •  
  • <!--- Get the sheet nodes. --->
  • <cfset arrSheets = XmlSearch(
  • xmlData,
  • "/workbook/sheets/*"
  • ) />
  •  
  •  
  • <!--- Loop over the sheet nodes. --->
  • <cfloop
  • index="intSheet"
  • from="1"
  • to="#ArrayLen( arrSheets )#"
  • step="1">
  •  
  • <!--- Get a short-hand pointer to the current sheet. --->
  • <cfset xmlSheet = arrSheets[ intSheet ] />
  •  
  • <!--- Grab the name text nodes from this sheet. --->
  • <cfset arrNames = XmlSearch( xmlSheet, "./name/text()" ) />
  •  
  • <!--- Grab the rows noes from this sheet. --->
  • <cfset arrRows = XmlSearch( xmlSheet, "./rows/row/" ) />
  •  
  •  
  • <!--- Check to see if we found a sheet name. --->
  • <cfif ArrayLen( arrNames )>
  •  
  • <!--- We found a sheet name, so set the value. --->
  • <cfset strSheetName = arrNames[ 1 ].XmlValue />
  •  
  • <cfelse>
  •  
  • <!--- No sheet name was found, so use default. --->
  • <cfset strSheetName = "Sheet1" />
  •  
  • </cfif>
  •  
  • <!---
  • Create an Excel sheet in the current workbook
  • with the given name.
  • --->
  • <cfset objSheet = objWorkbook.CreateSheet(
  • JavaCast( "string", strSheetName )
  • ) />
  •  
  •  
  • <!--- Loop over the row nodes. --->
  • <cfloop
  • index="intRow"
  • from="1"
  • to="#ArrayLen( arrRows )#"
  • step="1">
  •  
  • <!--- Get a short-hand pointer to the current row. --->
  • <cfset xmlRow = arrRows[ intRow ] />
  •  
  • <!--- Grab all the cells for this row. --->
  • <cfset arrCells = XmlSearch( xmlRow, "./cell/" ) />
  •  
  • <!--- Create the row in the current sheet. --->
  • <cfset objRow = objSheet.CreateRow(
  • JavaCast( "int", (intRow - 1) )
  • ) />
  •  
  •  
  • <!--- Loop over the cell nodes. --->
  • <cfloop
  • index="intCell"
  • from="1"
  • to="#ArrayLen( arrCells )#"
  • step="1">
  •  
  • <!--- Get a short hand pointer to the cell. --->
  • <cfset xmlCell = arrCells[ intCell ] />
  •  
  • <!--- Create a cell in the current row. --->
  • <cfset objCell = objRow.CreateCell(
  • JavaCast( "int", (intCell - 1) )
  • ) />
  •  
  • <!---
  • Set the cell value. Here is where would check
  • any kind of explicit data types and formatting.
  • However, for this proof of concept, we are only
  • going to deal with the String data type.
  • --->
  • <cfset objCell.SetCellValue(
  • JavaCast(
  • "string",
  • Trim( xmlCell.XmlText )
  • )
  • ) />
  •  
  • </cfloop>
  •  
  • </cfloop>
  •  
  • </cfloop>
  •  
  •  
  • <!---
  • ASSERT: At this point, we have populated our workbook with
  • zero or more sheets of data. Now, we have to write the
  • workbook to the file system.
  • --->
  •  
  •  
  • <!---
  • Create a file output stream to which we will write
  • the new workbook binary.
  • --->
  • <cfset objFileOutputStream = CreateObject(
  • "java",
  • "java.io.FileOutputStream"
  • ).Init(
  •  
  • JavaCast(
  • "string",
  • ExpandPath( "./xml_to_excel.xls" )
  • )
  •  
  • ) />
  •  
  •  
  • <!--- Write the workout data to the file stream. --->
  • <cfset objWorkbook.Write(
  • objFileOutputStream
  • ) />
  •  
  • <!---
  • Close the file output stream. This will release any
  • locks on the file and finalize the process.
  • --->
  • <cfset objFileOutputStream.Close() />

Not a whole lot of stuff going on here; basically, it's just sheet, row, and cell iteration. Running the above code, we get the following Excel file:


 
 
 

 
XML To Excel Using ColdFusion, XML, And POI  
 
 
 

Converting a query to an Excel file is so easy, I am not sure if anyone would even have any interest in this, but I think this is something that is definitely worth persuing. If nothing else, it would make the Excel file creation ColdFusion MX6 compatible since it doesn't rely on ColdFusion query object meta data (which didn't exist the way I needed until MX7). If anyone has any specific requests, now would be a good time to submit your feature requests.




Reader Comments

I'm interested in the query->excel. We use POI at work, but we use POI to build the excel sheet. This XML example is a much cleaner solution. Thanks Ben!

Reply to this Comment

Hi Ben,

I had a little play with creating Excel spreadsheets with XML a couple of weeks ago and had some good results. Using the new XML stuff that came out with Office 2007 you can set everything including formulae from within the XML.

Its quite useful to be able to set things like page layout on a sheet that you are creating dynamically for a client, as they don't have to change it a lot to be able to print etc..

Mat

Reply to this Comment

@Mat,

Unfortunately, I am not sure that the POI that comes with the ColdFusion install can handle anything that is Office 2007 formatted (from what I have heard - I don't actually have Office 2007). I assume that you are talking about the native XML that Office exports as (Save As -> XML). That XML is crazy, but is also very powerful. I am trying to create something intermediary that is not so complex, and maybe not as powerful, but easier to use.... we'll see how it goes.

Reply to this Comment

Apparently I haven't been paying attention. POI is built into CF8 natively now? o_O - I always dumped it in the WEB-INF/lib directory?

Reply to this Comment

@Todd,

I think POI may have started coming with CF MX6 :) But not sure if the versions have been upgrading. While I don't know this for a fact, I theorize that they started including it whenever CFReporting could export as Excel files.

Reply to this Comment

One thing I just noticed. The 'styles' you have defined in your XML isn't carrying through to the XLS file. Both your screenshot example and the example I just ran locally are un-styled.

Reply to this Comment

@Todd,

Yeah, I am not referencing those yet. Sorry that that is a bit misleading. The XML was supposed to be my "thoughts" on what it would do eventually. Then, the code that produces the XLS file is merely a "proof of concept" to see how using an XML document would work. Right now, everything is just stored as a string with no formatting.

Now that I see that iterating over the XML file is a piece of cake, I will go back and integrate with the POIUtility.cfc which as CSS abilities and stuff. More to come :)

Reply to this Comment

One thing that would be super useful is the ability to create new worksheets within the same workbook. There must be a way!

Reply to this Comment

There's totally a way :) One of this things I was thinking about was adding an action or something to the workbook, or maybe to the sheets, like:

<sheet action="overwrite" index="2"> ... </sheet>

which would overwrite sheet 2 of the existing workbook. Then, we could have things like action="append" if you just wanted to add to it (like for logging purposes or something).

Reply to this Comment

Well that would rock. I have a 3 page workbook that comes from 3 subqueries. Right now I'm dumping it into one sheet and then I have to manually seperate it.

Can you also make it so the excel file will burn itself to CD eject itself into a labeled case?

Reply to this Comment

Another cool thing this CFC could do is return an excel file to a variable instead of always creating a file on the server! I posted some code to CF-Talk recently that does this if you are interested in adding this functionality to you POI utility.

CoolJJ

Reply to this Comment

@CoolJJ,

That would be easy. The Workbook is stored in a variable until you actually explicitly write it to a file. Or did you mean to return it in some other form (like writing it to a Byte stream as opposed to a file stream giving you a binary file, but just not writing it to disk??)

Reply to this Comment

Yup, writing it to a ByteStream is what I meant. Here is some sample code:

<cfsilent> <cfscript> Excel = structnew(); Excel.WorkBook = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init(); Excel.Sheet1 = Excel.WorkBook.createSheet(JavaCast("string","test sheet one")); Excel.Row1 = Excel.Sheet1.createRow(0); Excel.Cell0 = Excel.Row1.createCell(0).setCellValue(JavaCast("string","Foo")); Excel.Cell1 = Excel.Row1.createCell(1).setCellValue(JavaCast("string","Bar")); jByteArrayOutputStream = createObject("java","java.io.ByteArrayOutputStream").init(); Excel.WorkBook.write(jByteArrayOutputStream); ExcelByteData = jByteArrayOutputStream.toByteArray(); jByteArrayOutputStream.close(); </cfscript> <cfheader name="content-length" value="#ArrayLen(ExcelByteData)#"/> <cfheader name="content-disposition" value="attachment; filename=MyTestFile1.xls" /> <cfcontent type="application/vnd.ms-excel" variable="#ExcelByteData#" /> </cfsilent>

Reply to this Comment

The POIUitility has been perfect for me for reading Excel 97-2003 files. I'm having a problem reading Excel 2007 files. Is there an upgrade I can do to Coldfusion's POI so that it can read Excel 2007 files?

Reply to this Comment

I cannot get Cold Fusion to release the excel spreadsheet I modify in the following code by doing file.close(). When I try to modify the spreadsheet in Excel after Cold Fusion has I'm told that it is being used. Any suggestions?

<cfset lcBeansFolder = #session.ServerDocumentStoragePath# & #session.BeansFolderName# & "\" & #form.txtFy# & "chkbk32">
<cfset lcExcelFileName = #Trim(form.txtSubHead)#>
<cfset loDataSheetName = #Trim(form.txtCostCode)# & ' ' & #Trim(tcdesc)#>
<cfset lcExcelFileFullPath = lcBeansFolder & '\' & lcExcelFileName & '.xls'>
<!--- Constant-valued Strings --->
<cfset lcFormat = 'Excel'>
<!--- store the packages in a variable for more compact code --->
<cfset loHssfPkg = "org.apache.poi.hssf.usermodel">
<cfset loPoifsPkg = "org.apache.poi.poifs.filesystem">
<!--- Modify a multi-sheet Excel report using POI-HSSF --->
<!--- Create the file input stream. --->
<cfset loFile = createObject("java","java.io.FileInputStream").init("#lcExcelFileFullPath#") />
<cfset lcMessageUpdatePath = "ReportErrorAction.cfm?AdminMode=METRICSRETURN&UpdateMessage=">
<!--- Capture errors:
An Object Instantiation Exception can be thrown if the file is empty. --->
<cftry>
<!--- Create the Excel file system object. This object is responsible
for reading in the given Excel file. A handle to the file. --->
<cfset loFs = createObject("java","#loPoifsPkg#.POIFSFileSystem").init("#loFile#") />
<cfcatch type="any">
<cfset lcReturnMessage = "The File " & lcExcelFileName & loFileEmptyMsg />
<cflocation url="#lcMessageUpdatePath##lcReturnMessage#">
</cfcatch>
</cftry>

<!--- Create a handle to the workbook. --->
<cfset loWorkBook = CreateObject("java", "#loHssfPkg#.HSSFWorkbook").init(loFs)>
<!--- Get total number of sheets in the workbook. --->
<cfset loSheetCount = loWorkBook.GetNumberOfSheets()>
<cfset loSheetCount = loSheetCount - 1>
<!--- Now that we have a sheets collection lets get the one we
want to update --->
<!--- This is an index loop, but is like a while loop here. Java is 0-based. --->
<cfset foundsheet = "N">
<cfloop index= "loSheetNum" from="0" to="#loSheetCount#" step = "1">
<!--- Obtain the name of the specific sheet in the workbook --->
<cfset loSheetName = loWorkBook.GetSheetName(JavaCast( "int", loSheetNum)) />
<!--- Search for a match to our sheet of interest. --->
<cfif UCASE(loSheetName) EQ loDataSheetName>
<!--- Get a reference to the Sheets in the Excel spreadsheet. It looks like
org.apache.poi.hssf.usermodel.HSSFSheet@af4653 .--->
<cfset loSheet = loWorkBook.GetSheetAt(JavaCast( "int", loSheetNum)) />
<cfset foundsheet = "Y">
<!--- Break out of loop if condition is met. This is like a while loop. --->
<cfbreak>
</cfif>
</cfloop>
<cfif foundsheet eq "N">
<script language="javascript">
alert("Worksheet does not exist for that Cost Code. It must be inserted and brought up-to-date.");
self.location.replace("BeansInitialBudget.cfm");
</script>
</cfif>
<!--- Get a handle on the first row that can accept data in this sheet--->
<cfif (#Left(form.txtSubHead, 2)# eq "84" and #Left(form.txtSubHead, 2)# eq "8C") and #form.txtFy# neq "93">
<cfset loRow = loSheet.getRow(JavaCast( "int", 3)) />
<cftry>
<cfset CheckRow = loRow><!--- see if row exists --->
<cfcatch type="any">
<cfset loRow = loSheet.createRow(3)><!--- add a new row if needed --->
</cfcatch>
</cftry>
<cfset loCell = loRow.getCell(JavaCast( "string", 11)) />
<cftry><!--- create a blank cell if a null is found --->
<cfset CheckForNull = loCell>
<cfcatch type="any">
<cfset loCell = loRow.createCell(11)>
</cfcatch>
</cftry>
<cfset loCell.setCellType(loCell.CELL_TYPE_STRING)>
<cfset loCell.SetCellValue(JavaCast("string",form.txtHqBalance))>
<cfelse>
<cfset loRow = loSheet.getRow(JavaCast( "int", 4)) />
<cftry>
<cfset CheckRow = loRow><!--- see if row exists --->
<cfcatch type="any">
<cfset loRow = loSheet.createRow(4)><!--- add a new row if needed --->
</cfcatch>
</cftry>
<cfset loCell = loRow.getCell(JavaCast( "string", 12)) />
<cftry><!--- create a blank cell if a null is found --->
<cfset CheckForNull = loCell>
<cfcatch type="any">
<cfset loCell = loRow.createCell(12)>
</cfcatch>
</cftry>
<cfset loCell.setCellType(loCell.CELL_TYPE_STRING)>
<cfset loCell.SetCellValue(JavaCast("string",form.txtHqBalance))>
</cfif>

<!--- Write the output to the file,
since we will not be continuing the code much further --->
<cftry>
<!--- Get a handle on the file. --->
<cfset loFileOut = createObject("java","java.io.FileOutputStream").init("#lcExcelFileFullPath#")/>
<cfcatch type="any">
<cfset lcReturnMessage = "The File " & lcExcelFileName>
<cflocation url="#lcMessageUpdatePath##lcReturnMessage#">
</cfcatch>
</cftry>
<cfset loWorkBook.write(loFileOut) />
<!--- Close the file output stream. This will release any locks on
the file and finalize the process. --->
<cfset loFileOut.flush() />
<cfset loFileOut.close() />
<cfset loFile.close() />

Reply to this Comment

Can this method be adapted to use an xml spreadsheet that has already been transformed using an xslt template? Is it just a matter of parsing the transformed xml file correctly?

Essentially I wrote an app in VB.NET to generate a report. I created a blank template using Excel, then made the necessary modifications to the xslt file for populating it with data. While .NET does allow for the direct conversion of xml spreadsheets to xls files, it requires that the machine running the conversion have office or excel installed. We need this to run as an automated process and do not what to install either app on the server that will be generating the report.

So I am writing a CF webservice to do the conversion for me(at least I am attempting to) using the Apache POI, but I am not sure what the best method may be or if it is even possible.

I am going to try and adapt this example to suit my needs but any advice would be greatly appreciated.

Reply to this Comment

Ben,

I want to use the email form you have posted in coldfusion to not only send an email, but to also send the information directly to an excel spreadsheet. is this possible? with out using SQL or a database?

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.