Creating Excel Files With ColdFusion, XML, And POI

Posted August 20, 2007 at 8:42 AM by Ben Nadel

Tags: ColdFusion

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!

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

@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.

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?

@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.

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.

@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 :)

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

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).

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?

Ha ha ha :) I'll work on it.

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

@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??)

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>

^-- comment is spam. :|

Removed, Thanks!

Thank you, Gyes! Veru usefull!

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?

@Tom,

I am not sure if this will be compatible with 2007. Sorry.

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() />

Just a quick note to let you know that I added a link to your component to the wikipedia page on POI, under the 'Architecture' section.

See http://en.wikipedia.org/wiki/Apache_POI#Architecture for more info.

Peace

</cliff>

@Cliff,

Awesome gesture... but it looks like they removed the edit :(

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.

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?

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>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment