Creating "real" Excel files using JExcelAPI and ColdFusion

You probably know that most MS products will import HTML files, so long as you change the extension, e.g.: you create a HTML table and save it as a .XLS to have it open right in Excel. Or save the HTML as a .PPT and it will apparently open in PowerPoint. Stuff like that.

Or maybe you've played with the XML importing/exporting functions that are built into the latest versions of the Office line... wow, nifty- but maybe impractical for various reasons. You may need to support older versions of Excel, say, or just don't like the sheer volume of XML data and wankery that's needed to do so.

There is also Apache POI HSSF, which generates true Excel files, and I believe comes bundled with CFMX. A great alternative to the other two methods I've spoken of so far, but a pretty cumbersom API (POI does lots of stuff besides Excel). And I've heard that JExcelApi performs better on large spreadsheets, but I have not done any tests to verify. I think I heard formula handling was easier with JExcel, too... been a while since I checked out HSSF, so who knows.

Well, if you still want to try JExcelApi after checking out the other methods, you'll need to get the jxl.jar file from the project page of jexcelapi.sourceforge.com. Then you'll want to either drop it in your CF class-path, or use a JavaLoader, so CF can use it.

Once you've got that far, (and I can post an example using the javaloader, eventually) you can start making workbooks!

I really should post just a sample of how to do it plain, but I was working, so that sample is long since gone. Instead I've got a cffunction that will take a struct of queries, and generate sheets for each one in an excel workbook, which is then directly piped to the browser (I hate saving the file just to send it out and then delete anyway).
While this is good for people who don't care about the internals, it's not a very helpful learning example, as it uses a query. KISS, is better for explaining. Oh well, maybe later.

This code could be coded bad too... it works, which is generally what I care about, but I don't know if I'm var-ing stuff right or if reusing the same objects is kosher or whatever. Just to put it out there... I'm not a code guru. If it hurts your dog it's not my fault, blah blah blah
Bah. Here it is:

<cffunction name="queriesToXLS" output="false" hint="I create a XLS workbook from a struct of queries">
<cfargument name="inQueries" type="struct" required="true">
<cfscript>
var outStream = createObject("java","java.io.ByteArrayOutputStream").init();
var workbook = createObject("java","jxl.Workbook");
var ws = createObject("java","jxl.WorkbookSettings").init();
var locale=createObject("java","java.util.Locale").init("en","EN");
var labelObj=createObject("java","jxl.write.Label");
var numberObj=createObject("java","jxl.write.Number");
ws.setLocale(locale);
workbook = workbook.createWorkbook(outStream,ws);
for (i = 1; i lte structCount(inQueries); i = i + 1) {
sheetName = structKeyList(inQueries[i]);
sheetQuery = inQueries[i][structKeyList(inQueries[i])];
sheet = workBook.createSheet(sheetname, toString(i-1));
// ouput column headers
for (qCol = 1; qCol lte listLen(sheetQuery.columnlist); qCol = qCol + 1) {
thisLabel = labelObj.init(toString(val(qCol)-1),0,listGetAt(sheetQuery.columnlist,qCol));
sheet.addCell(thisLabel);
}
for (qRow = 1; qRow lte sheetQuery.recordcount; qRow = qRow + 1) {
for (qCol = 1; qCol lte listLen(sheetQuery.columnlist); qCol = qCol + 1) {
thisLabel = labelObj.init(toString(val(qCol)-1),toString(qRow),toString(sheetQuery[listGetAt(sheetQuery.columnlist,qCol)][qRow]));
sheet.addCell(thisLabel);
}
}
}
workbook.write();
outStream.flush();
workbook.close();
return outStream.toByteArray();
</cfscript>
</cffunction>

Then you'd use it like so:

<cfsetting enablecfoutputonly="true">
<cfquery name="getInfo" datasource="#request.thedsn#">
SELECT * FROM folks
</cfquery>
<cfquery name="getInfo2" datasource="#request.thedsn#">
SELECT * FROM peeps
</cfquery>
<cfset workbookSheets = structNew()>
<cfset workbookSheets[1]["Folks"] = getInfo>
<cfset workbookSheets[2]["Peeps"] = getInfo2>
<cfset daXls = queriesToXLS(workbookSheets)>
<cfset request.cvalue = 'attachment;'>
<cfset request.cvalue = request.cvalue & "filename=test.xls">
<cfheader name="Content-Disposition" value="#request.cvalue#">
<cfoutput><cfcontent type="application/msexcel" variable="#daXls#"></cfoutput>

And that should work. Sorry for lack of comments, etc. I'm a baaaaad coder!