Export to Excel, via a Link on action page

Report · Mar 09, 2010

I have two questions.

1) How can I export to excel, via a link. So for example I have a form and a user clicks submit and I cfoutput the data in a table on the same page (action page is the same as the input page). Now, once the data is output, I would like a link on that same page so the user can click and it will export the table to Excel.

I have tried:

    <cfheader name="Content-Disposition" value="attachment; filename=test.xls">
    <cfcontent type="application/msexcel">
    <cfoutput query="qTest">
    <table>
    <tr>
    <th>Account</th>
    <th>Amount</th>
    </tr>
    <tr>
    <td>#qTest.ACCOUNT#</td>
    <td>#NumberFormat('#qTest.TotalAmt#', "_(999,999,999.99)")#</td>
    </tr>
    </table>
    </cfoutput>

But once the page is loaded it tries to export automatically. How can I get it to export via a link?

Also, someone suggested wrapping the cfoutput in a <cfsavecontent variable="xyz"> tag, but once I do that, how do I put the "xyz" variable into the <cfcontent> tag? Or where do I use it?

Thanks guys

Report · Mar 09, 2010

You just need the link to triger an action page that uses the <cfheader..><cfcontent...> combonation you have in your post.

You can either have the link go to a seperate action page that is dedicated to building the output for the excel format. Or you could have the link go back to the same action page you are displying the content on, but use some type of <cfif...> block triggered by one or more values passed through the link url to run the <cfheader...><cfcontent...> code.

If you wanted to store the content in a variable with the <cfsavecontent...> tag, you would then just put an <cfoutput>#contentVariable#</cfoutput> right after the <cfcontent...> tag.

Report · Mar 09, 2010

Ian,

I got the export working, but when I wrap <cfsavecontent> around my <cfoutput> table, it does not display on the screen when a user clicks Submit. What I mean is, if a user clicks Submit, the db is queried, the results are stored in the session variable "export" I created, but there is no data on screen. Just an export link that I created. How do I get the table to display on screen and still have the cfsavecontent tag around it?

Thanks

Report · Mar 09, 2010

Output the variable that contains the table. Just put another <cfoutput>#session.export#</cfoutput> in the appropiate place in your logic to be displayed on the screen in that use scenario.

Report · Mar 09, 2010

That did it, thanks man.

Report · Mar 09, 2010

Here's some additional ways that you can format your output Excel Spreadsheet using CSS - I just copied this from one of my applications to give you an idea. Outputting to Excel is really nice because you can email the file off and you can put links into the cells or whatever. I normally display on the screen, then have the Excel Output button and just copy the whole query over to the Excel output page - it works for Office 2000 to 2007, haven't checked it on later versions. I kept this simple, but you can do all sorts of things with the Styles to make it look pretty. I did not include the query stuff as that would just clutter this up, but you'll get the picture.

<CFCONTENT
    TYPE="application/msexcel">
<CFHEADER
    NAME="Content-Disposition"
    Value="filename=JP1_Report.xls">
<html
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns="http://www.w3.org/TR/REC-html40">
<head>
    <title>Receipt List</title>
<Style Type="text/css">
.rowHeads {font-family:verdana;font-size:10pt;color:white;background:blue;font-size:8pt}
</STYLE>
</head>

<body bgcolor="#0070a8">
<TABLE>
<TR>
<TH class="rowHeads">PCT</TH>
<TH class="rowHeads">Ticket</TH>
<TH class="rowHeads">Cause</TH>
<TH class="rowHeads">Defendant</TH>
<TH class="rowHeads">Agency</TH>
<TH class="rowHeads">Amnt<BR>Recd</TH>
<TH class="rowHeads">Fine</TH>
<TH class="rowHeads">Arrest</TH>
<TH class="rowHeads">CVCF</TH>
<TH class="rowHeads">CCC</TH>
<TH class="rowHeads">JCPT</TH>
<TH class="rowHeads">CHS</TH>
<TH class="rowHeads">STF</TH>
<TH class="rowHeads">Admin</TH>
<TH class="rowHeads">DAO</TH>
<TH class="rowHeads">FA</TH>
<TH class="rowHeads">Time<BR>Pay</TH>
<TH class="rowHeads">Traffic<BR>Fee</TH>
<TH class="rowHeads">DDC</TH>
<TH class="rowHeads">TPW</TH>
<TH class="rowHeads">JCAD</TH>
<TH class="rowHeads">FTA</TH>
<TH class="rowHeads">JRF</TH>
<TH class="rowHeads">JSF</TH>
<TH class="rowHeads">JCTF</TH>
<TH class="rowHeads">IDF</TH>
</TR>
<CFOUTPUT Query="GetReceipt">
<TR>
<TD style="border:solid 1px" align="center">#PrecinctID#</TD>
<TD style="border:solid 1px" align="center">#ReceiptID#</TD>
<TD style="border:solid 1px" align="center">#CauseNo#</TD>
<TD style="border:solid 1px" align="left">#Off_LName#, #Off_FName# #Off_MI#</TD>
<TD style="border:solid 1px"align="Left">#AgencyCode#</TD>
<TD style="border:solid 1px">#NumberFormat(TotalCredit,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocFine,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocArrestFee,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocCVCF,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocCCC,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocJCPT,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocCHS,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocSTF,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocAdminFee,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocDAO,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocFA,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocTimePay,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocTrafficFee,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocDDC,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocPAW,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocJCAD,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocTrafficFTA,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocJRF,"9999.99")#</TD>
<TD style="border:solid 1px">#Numberformat(AllocJSF,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocJCTF,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(AllocIDF,"9999.99")#</TD></TR>
</CFOUTPUT>
<CFOUTPUT Query="GetTotals">
<TR>
<TD colspan="5">TOTAL</TD>
<TD style="border:solid 1px">#NumberFormat(TotCred,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotFine,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotArrest,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotCVCF,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotCCC,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotJCPT,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotCHS,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotSTF,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotAdminFee,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotDAO,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotFA,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotTimePay,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotTrafficFee,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotDDC,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotPAW,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotJCAD,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotTrafficFTA,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotJRF,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotJSF,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotJCTF,"9999.99")#</TD>
<TD style="border:solid 1px">#NumberFormat(TotIDF,"9999.99")#</TD>
</TR>
</CFOUTPUT>

</TABLE>

</body>
</html>

Report · Mar 09, 2010

If I understand your problem you have a page containing a query that displays some data in a table. You would like to be able to export this data to excel.

To make this work as a link add an URL optional parameter to your page that specifies that output should target Excel. Below is a quick sample of how this might be implemented.

<!--- set up default URL parameter value 

Possible values:
     "html" (default)
     "excel" 

--->
<cfparam name="url.format" default="html" />



<!--- execute query

You might consider moving this logic to a coldfusion component so that it can be reused across various pages in your application.

 --->
<cfquery name="qTest" datasource="mydsn">
     SELECT Account, TotalAmount
     FROM Accounts
     WHERE SomeField = 'Some Value'
</cfquery>


<cfif url.format eq "html">

     <!--- handle output to HTML --->

     <!--- create link to Excel version  --->
     <a href="page.cfm?format=excel">Excel Version</a> 

<cfelseif url.format eq "excel">
     
     <!--- handle output to Excel

     If you find yourself needing to support Excel output considering moving this logic into a coldfusion component so that it can be reused across various pages in your application.  You could create a CFC method that takes a query object and returns a output in Excel friendly format.

     Also be aware that ColdFusion 9 has a CFSPREADSHEET tag that might be useful to you
     
      --->
     <cfheader name="Content-Disposition" value="attachment; filename=test.xls">
     <!--- consider using reset attribute to clear any output generated above this line --->
    <cfcontent type="application/msexcel" reset="yes"> 
    <cfoutput query="qTest">
     <!--- etc --->

     <cfabort /> <!--- I'd end page output here for the Excel version --->
</cfif>

In regards to your second question take a look at the CF documentation.
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7d57.html

If you have questions please post them.

Export to Excel, via a Link on action page

1 Correct answer