cfspreadsheet + GetTempFile() = NullPointerException
Every once in a while I encounter a problem with something so blindingly simple that
- I can’t imagine I haven’t tried it before, and
- 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.
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.
getTempDirectory() and then choose your own filename after it.
Replace() on the generated filename to make it end in .xls.