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.

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

[in]View Kelson Vibber's profile on LinkedIn

4 thoughts on “cfspreadsheet + GetTempFile() = NullPointerException

    1. brad

      Correction, the proper command is:
      cfset filename=GetDirectoryFromPath(GetCurrentTemplatePath()) & “#URL.TRNo#_Final_Report.xlsx”

      Reply

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.