Sunday, August 27, 2006

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!

7 comments:

Joel said...

does this read/write excel as well?

Anonymous said...

How i can save the file instead of open directly from the browser?

If i wanna create more than 1 .xls file, one bye one, and save all the excel file into one location, how i can do this?

eg

call query 1
create 1 xls file
save into c:\excel

call query 2
create 2 xls file
save into c:\excel

etc etc...


Thanks

Denny said...

@joel Yes, there is a cfc for reading excel. Same location as query2xls (xls2query).

@anonymous if you want to save the file to disk, replace

cfcontent type="application/msexcel" variable="#daXls#"

with

cffile action = "write"
file = "full_path_name"
output = "#daXls#"

Add a few >, <, of course. :)

Dom said...

Uber nice. No need for comments in the code - its self explanatory (good code = less comments init) ;)

Denny said...

Thanks Dom! I hold myself to standards I can't possibly meet, which can be depressing at times... lol. :-)

jones said...

Nice blog...
visit also coldfusion example

Alex said...

My sister asked me an advice about crashed excel files. I advised it an one tool, which to my mind is the best solution in this and probably other cases - excel 2007 recovery.