Using CF 6.1 I have a task where I need to export a bunch of data to an excel spreadsheet. Not wanting to use the html table with cfcontent headers method for streaming the excel data to the browser I decided to dive into the pond with Apache Poi. Thanks to some blog posts by Dave Ross and Christian Cantrell I was able to build my spreadsheet pretty quickly AND stream the contents to the user without having to create a spreadsheet on the server’s filesystem.

I’m not going to go into much detail on creating the spreadsheet since Dave’s post goes into that with great detail. However, I did have to add a touch of new logic to Christian’s streaming solution in order to get it to work.

The following function is a method within a “ExcelWriter” wrapper CFC I have for working with POI - just so my code elsewhere isn’t muddied up with java calls.

<cffunction access="public" output="true" name="stream" returntype="void"
hint="writes the spreadsheet to a bytearray output stream">
<cfset var o = "" />
<cfset var book = getBook() />
<cfset var context = "" />
<cfset var response = "" />
<cfset var outStream =
createObject("java","java.io.ByteArrayOutputStream").init()/>
<cfset var len = 0 />

<cfset book.write(outStream) />
<cfscript>
book.write(outStream);
len = arrayLen(outStream.toByteArray());
outStream.flush();
outStream.close();

context = getPageContext();
context.setFlushOutput(false);
// must call getResponse twice to get to the correct output stream (otherwise
it is a character output stream which will corrupt my binary data)
response = context.getResponse().getResponse();
out = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setContentLength(len);
book.write(out);
out.flush();
out.close();
</cfscript>
</cffunction>

I’ll be the first to admit that there may be a better way to get the length of the spreadsheet bytearray. But this is the only way I know to do it. If I didn’t determine the byte array length then JRUN kind of hangs up if I try another request after the excel was streamed (unless I close the requesting browser). I think, if you fail to provide a contentLength value to the response stream that it just keeps on streaming until it either times out or the request is killed (browser close).