cfspreadsheet + GetTempFile() = NullPointerException

Every once in a while I encounter a problem with something so blindingly simple that

1. I can’t imagine I haven’t tried it before, and
2. I can’t believe no one else has documented the problem either.

ColdFusion 9 and later has a tag called <cfspreadsheet> which makes it dead simple to read, update, or create an Excel file. Want to plug the results of a query into a spreadsheet? Literally one tag with just three parameters.

There’s also function GetTempFile() that will create a guaranteed unique file on the server that you can then manipulate.

The obvious solution if you want to create a spreadsheet to download is to create a temp file, write to it with <cfspreadsheet>, then serve it up (with a Content-disposition header containing the real filename that the user will get).

<code><cfset filename=GetTempFile(GetTempDirectory()) >
<cfspreadsheet action="write" overwrite="true" filename="#filename#" query="myquery">
<cfheader name="Content-disposition" value="attachment;filename=MyFile.xls">
<cfcontent type="application/msexcel" file="#filename#">
<cffile action="delete" file="#filename#"></code>

It turns out this won’t work. You’ll get a Null Pointer Exception in the logs. Not because of anything you’d expect, though…it turns out that <cfspreadsheet> is relying on the file extension, and since GetTempFile() gives you a file ending in .tmp instead of .xls, it isn’t sure what to do with the file.

Solution

Find some other way to name your temporary file, and give it an .xls extension, before you tell <cfspreadsheet> to write to it.

In comments on the original version of this post, two readers suggested specific approaches.

Brad: Use getTempDirectory() and then choose your own filename after it.

Danielle: Use Replace() on the generated filename to make it end in .xls.