[bound4h]

Hey guys, I have a cfm page that I am using to query data, and the result set is displayed on the same page when a user clicks submit.

My question is, I would like to create a clickable icon where, after a user runs the query and the data table displays, I want the user to be able to click a little Excel icon that will allow them to download the data in Excel.

So, a user clicks on a little icon somewhere on the page and IE or Firefox or whatever pops up a little dialog box asking them if they want to OPEN or SAVE the file results.xls. How can I do this?

Here is my current code, but where do I implement the cfoutput stuff to export? On the same page?

<cfquery name="qActivity" datasource="khamp" result="resultInfo">
     SELECT KHAMELEON.GL_DETAIL.ACCOUNT, KHAMELEON.GL_ACCOUNT.DES1, KHAMELEON.GL_DETAIL.ENTITY, 
    SUM (KHAMELEON.GL_DETAIL.AMOUNT) AS "TotalAmt"           
     FROM KHAMELEON.GL_ACCOUNT, KHAMELEON.GL_DETAIL
    WHERE 0=0
    <cfif Form.Entity IS NOT "">
          AND KHAMELEON.GL_DETAIL.ENTITY = '#Form.Entity#'
     </cfif>
    AND KHAMELEON.GL_DETAIL.ACCTG_DATE <= '#Form.asofday#-#Form.asofmonth#-#Form.asofyear#'
    <cfif Form.accountnum IS NOT "">
    AND KHAMELEON.GL_ACCOUNT.ACCOUNT = '#Form.accountnum#'
    </cfif>
    AND KHAMELEON.GL_ACCOUNT.ACCOUNT=KHAMELEON.GL_DETAIL.ACCOUNT
    GROUP BY 
KHAMELEON.GL_ACCOUNT.ACCOUNT,
KHAMELEON.GL_DETAIL.ACCOUNT, 
KHAMELEON.GL_ACCOUNT.DES1, 
KHAMELEON.GL_DETAIL.ENTITY
     HAVING SUM(KHAMELEON.GL_DETAIL.AMOUNT)<>0
     ORDER BY KHAMELEON.GL_ACCOUNT.ACCOUNT ASC
     </cfquery>
  <cfif resultInfo.Recordcount eq 0>
    No Records Match the Search Criteria.
    <cfelse>
    <hr/>
    <br/>
    <table border="1" class="displaytable">
    
<!--Headings Row-->   
          
        <tr>
           <th>Account</th>
           <th>Description</th>
           <th>Entity</th>
           <th>Book 1</th>
      </tr>
      
<!--Result Rows-->    
  
      <cfoutput query="qActivity">
      <tr>
        <td>#qActivity.ACCOUNT#</td>
        <td>#qActivity.DES1#</td>
        <td>#qActivity.ENTITY#</td>
        <td style="text-align:right">#NumberFormat('#qActivity.TotalAmt#', "_(999,999,999.99)")#</td>
      </tr>
      </cfoutput>

I got the following code off of a thread in the forum, but it trys to download the excel file as soon as the query is run (the excel download doesn't work though, it trys to download the actual cfm page instead):

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



<table border="2">
<tr>
<td> Account </td><td> Description </td><td> Entity </td><td> Book1 </td>
</tr>
 <cfoutput query="qActivity">
<tr>
<td>#qActivity.ACCOUNT#</td><td>#qActivity.DES1#</td><td>#qActivity.ENTITY#</td><td>#NumberFormat('#qActivity.TotalAmt#', "_(999,999,999.99)")#</td>
</tr>
</cfoutput>
</table>

</cfcontent>

[Craig328]

Nothing to add to that response about the Excel export file on demand.

I will say that your query example in the first case needs cfqueryparams on it. You're putting raw form data into a query that's going to your database which could make for a bad day should someone decide to test your site for a SQL injection attack.

Don't know if you're a CF beginner or not but I try and pass on that tip whenever it seems necessary. Just sayin'.

Good luck!

[Craig328]

Mike, on 26 February 2010 - 12:04 PM, said:

After changing to attachement, it tries to send the whole page as the excel document. So in my case, it asks me to OPEN or SAVE the file called "action_cfm" which obviously isn't xls.

I tried out the POI utility but how do I have the POI code on a different page (through a link) so that the POI code can "get" the query data from the original page? When I use query="qActivity" it doesn't seem to pull the info over.

You might consider creating a link on your page that fires a new .cfm page into a popup. That .cfm page could run the query, create the table for Excel and prompt the user to download the generated Excel file by using the cfheader tag and appropriate attributes as described earlier.

Have a look at this page if you need some sample code.

Good luck!

[TJCrowl]

try <cfcontent type="application/msexcel">