Export Query Data to Excel

Report · Feb 25, 2010

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>

Thanks guysq

Report · Feb 25, 2010

To actually get the data into excel, google "cold fusion excel poi" and look for Ben Nadel's cfc. Otherwise you might have issues with Office 2007.

For the icon or whatever, make your query a session variable. Then have the icon link to either a self closing popup or a very small iframe that exports the query to an excel file and then uses cfcontent to download it to the user.

Report · Feb 25, 2010

I'm so confused by POI cfc because I cannot find any documentation.

Can you give me start on making the query a session variable and having the icon link to a self-closing popup? Would the pop be a separate page with cf code?

Thanks

Report · Feb 25, 2010

For pop up windows, google "javascript window.open"