Skip to main content
D
depth10
Known Participant
March 9, 2010
Answered

Export to Excel, via a Link on action page

  • March 9, 2010
  • 2 replies
  • 4985 views

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

    This topic has been closed for replies.
    Correct answer ilssac

    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.

    2 replies

    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    March 9, 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.

    ilssac
    ilssac
    Inspiring
    March 9, 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.

    D
    depth10Author
    Known Participant
    March 9, 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

    ilssac
    ilssacCorrect answer
    Inspiring
    March 9, 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.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices