Export Query Data to Excel

Discussion in 'Programming' started by bound4h, Feb 25, 2010.

  1. #1
    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>
    Code (markup):
    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>
    Code (markup):
    Thanks guys
     
    bound4h, Feb 25, 2010 IP