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.
<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#">
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.