ColdFusion: How can I generate a spreadsheet without saving it as a file?

Question

In a ColdFusion application I need to make it possible for users to save a spreadsheet to their hard drives.

I have been able to generate files on the fly by building them as XML or HTML tables, applying the application/vnd.ms-excel MIME type, and saving them with a .xls extension.

<cfheader name="Content-Disposition" value="attachment; filename=MySpreadsheet.xls"> 
<cfcontent type="application/vnd.ms-excel"> 

But when users load such a file, they invariably get an annoying message (due to Excel's Extension Hardening Feature):

The file you are trying to open, '_____.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

I would prefer to avoid this message.

I understand the <cfspreadsheet> tag and SpreadsheetWrite() functions will generate a proper Excel file that does not display this message -- but they both require an argument instructing where on the server the file is to be written. Because the download might contain sensitive information, and because I cannot easily enforce security once a file is written to the server, I do not want to create such a file -- even temporarily. (I know that I can provide a password, but this is not an ideal solution in the context of the business process in which the Excel file will be used after it is generated.)

How can I generate the Excel file and prompt for download without first writing it to a file?

Answer 1

After scouring Stack Overflow and other resources for this issue, I finally found the solution in the context of a different question. I'm now posting the question and my findings here, in the hope that others will find this information more easily than I did.

The solution revolves around the SpreadsheetReadBinary() function and the variable attribute in the <cfcontent> tag.

I built a spreadsheet using the ColdFusion spreadsheet functions, and then put the following at the end of the file (contrary to my habit of putting <cfheader> and <cfcontent> at the beginning).

<cfheader name="Content-Disposition" value="attachment; filename=MySpreadsheet.xls"> 
<cfcontent type="application/vnd.ms-excel" variable="#SpreadsheetReadBinary( objSpreadsheet )#">

If anyone would benefit from more detail, the following content in a .cfm file will build the spreadsheet and prompt for download as a properly-formed Excel file:

<!---
Building a spreadsheet that looks like:

+-----+-----+-----+
| FOO | BAR | BAZ |
+-----+-----+-----+
| 101 | 102 | 103 |
+-----+-----+-----+
| 201 | 202 | 203 |
+-----+-----+-----+

--->


<!--- Create a new spreadsheet. --->
<cfset objSpreadsheet = SpreadsheetNew()>

<!--- Create and format the header row. --->
<cfset SpreadsheetAddRow( objSpreadsheet, "FOO,BAR,BAZ" )>
<cfset SpreadsheetFormatRow( objSpreadsheet, {bold=TRUE, alignment="center"}, 1 )>

<!--- Populate the spreadsheet. --->
<!--- In a real situation, this would be looped programmatically; it is done cell-by-cell here for readability. --->
<cfset SpreadsheetSetCellValue( objSpreadsheet, 101, 2, 1, "NUMERIC" ) >
<cfset SpreadsheetSetCellValue( objSpreadsheet, 102, 2, 2, "NUMERIC" ) >
<cfset SpreadsheetSetCellValue( objSpreadsheet, 103, 2, 3, "NUMERIC" ) >
<cfset SpreadsheetSetCellValue( objSpreadsheet, 201, 3, 1, "NUMERIC" ) >
<cfset SpreadsheetSetCellValue( objSpreadsheet, 202, 3, 2, "NUMERIC" ) >
<cfset SpreadsheetSetCellValue( objSpreadsheet, 203, 3, 3, "NUMERIC" ) >

<cfheader name="Content-Disposition" value="attachment; filename=MySpreadsheet.xls"> 
<cfcontent type="application/vnd.ms-excel" variable="#SpreadsheetReadBinary( objSpreadsheet )#">