CODEWITHSUNDEEP
coldfusionadobecoldfusion-11
user2734498
user2734498

Reputation: 85

Preview spreadsheet prior to excel download ColdFusion 11

I'm trying to export data from a query to an excel spreadsheet. I used the following code to successfully download an HTML table to a spreadsheet via Chrome and IE (picked up from Raymond) but I am now looking for a way to display the data prior to download. Currently this immediately downloads when the submit button is selected.

enter image description here

<cfset request.isIE = false />
<cfif cgi.http_user_agent contains "MSIE">
 <cfset request.isIE = true />
</cfif>

<cfheader name="Content-Disposition" value="inline; filename=SearchResults.xls">
<cfcontent type="application/vnd.ms-excel">
<cfset ThisOrganism="#Form.GenusSpecies#">

<cfquery name="BSAFbyOrganism" datasource="BSAFweb">
SELECT ID, StudyType, Chem_Name
FROM Summary
WHERE   GenusSpecies='#ThisOrganism#'
ORDER BY Chem_Name

</cfquery>
 <table width="100%" border="1" cellpadding="5px">
   <tr>
     <th>ID</th>
     <th>StudyType</th>
     <th>Chem_Name</th>
   </tr>
 <cfoutput query="BSAFbyOrganism">
   <tr>
     <td>#ID#</td>
     <td>#StudyType#</td>
     <td>#Chem_Name#</td>
   </tr>                  
 </cfoutput>
</table>

<cfset q= BSAFbyOrganism>
    <cfset filename = expandPath("./myexcel.xls")>
    <cfspreadsheet action="write" query="BSAFbyOrganism" filename="#filename#" overwrite="true">

    <!--- Make a spreadsheet object --->
    <cfset s = spreadsheetNew()>
    <!--- Add header row --->
    <cfset spreadsheetAddRow(s, "ID,StudyType,Chem_Name")>
    <!--- format header --->
    <cfset spreadsheetFormatRow(s,
        {
            bold=true,
            fgcolor="lemon_chiffon",
            fontsize=14
        },
    1)>
    <!--- Add query --->
    <cfset spreadsheetAddRows(s, q)>
    <cfset spreadsheetWrite(s, filename, true)>
    <cfheader name="content-disposition" value="attachment; filename=myexcel.xls">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

Upvotes: 0

Views: 592

Answers (2)

user2734498
user2734498

Reputation: 85

I found some code from Raymond Camden's blog post that I was able make work for IE. So the user queries data and they are brought to the table posted above. On that page is a button to create a cfspreadsheet. This button calls the same query again and at the bottom I inserted the code below which writes to a spreadsheet and off to excel. There is probably a way to reduce a step, but for now this is working. Thanks for pointing me towards cfspreadsheet. Appreciate your time. If I improve on it I will come back and post it.

<cfset q= Organism>
<cfset filename = expandPath("./file/myexcel.xls")>
<cfspreadsheet action="write" query="Organism" filename="#filename#" overwrite="true">

<!--- Make a spreadsheet object --->
<cfset s = spreadsheetNew()>
<!--- Add header row --->
<cfset spreadsheetAddRow(s,  "ID,StudyType,Chem_Name")>
<!--- format header --->
<cfset spreadsheetFormatRow(s,
    {
        bold=true,
        fgcolor="lemon_chiffon",
        fontsize=12
    },
1)>
<!--- Add query --->
<cfset spreadsheetAddRows(s, q)>
<cfset spreadsheetWrite(s, filename, true)>
<cfheader name="content-disposition" value="attachment; filename=myexcel.xls">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(s)#" reset="true">

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20794

Solve one problem at a time. For downloading, create a simple spreadsheet wtih Excel and save it to a place available to ColdFusion. Then try these tags

<cfheader
name="content-disposition" 
value="Attachment;filename=#NameOfYourFile#">

<cfcontent  file="#NameOfYourFile#" 
type="application/vnd.ms-excel">

Notice that the value attribute of the cfheader tag specifies attachment, not inline.

As mentioned in the comments, use <cfspreadsheet> to create the file from your query.

Upvotes: 2

Related Questions