Goal: convert a ColdFusion query object into a spreadsheet, save the spreadsheet to the server, and download it in the browser (without the annoying Excel warning about the wrong file extension):


view plain print about
1<cfparam name="session.searchQuery" default="#queryNew('')#">
2    <cfset fileDir = expandPath('.' & '/searchdata/')>
3    <cfif not directoryExists(fileDir)>
4        <cfdirectory action="create" directory="#fileDir#">
5    </cfif>
7 <cfset filename = dateFormat(now(),'yyyy-mm-dd') & '-' & timeFormat(now(),'hh-mm-ss') & '.xls'>
9    <cfset fileLocation = fileDir & filename>        
10    <cfspreadsheet action="write" filename="#fileLocation#" query="session.searchquery">
12    <cfheader name="Content-Disposition" value="attachment;filename=#filename#">
13    <cfcontent type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" file="#fileLocation#">

When I used content type of mxexcel, or x-msexcel (or a few others I tried), i would get a warning from excel that the format specified did not match the extension.

This method, cobbled together from a few different forum threads and blog posts, seems to get around those issues.