Query result to spreadsheet

[toyt78]

How can I get a query to show up in an Excel Spreadsheet??

I have a query result that shows records on a page and I need the page to have a link or button that will bring up the query result in an Excel spreadsheet.

I can get a regular query to work and show an Excel spreadsheet but now I need it where Iam passing a query result to show in Excel Spreadsheet.

Please advise.

<cfquery name="myExcel" datasource="theData">
SELECT *
FROM tableOne 
</cfquery>


<cfset NewLine = Chr(13) & Chr(10)>
<cfheader name="Content-Disposition" value="attachement;filename=theFile.xls;">
<cfcontent type="application/vnd.ms-excel">
<table border=1 align="left" width="30%">
<tr bgcolor="#c0c0c0">
    <th width="10">State</th>
    <th width="10">City</th>
    <th width="10">County</th>


</tr>
</table>
<cfoutput query="myExcel">
<table border=1 align="left" align="center">
<tr>
<td width="10" valign="top">#state#</td>
<td width="10" valign="top">#city#;</td>
<td width="10" valign="top">#county#</td>
</TABLE>
</cfoutput>

 

[CJWilson3759]

Try modifying the code I wrote below to match your query. Supports multiple formats. Content that you want to display in all formats should be placed between the <cfdocument> tags. Content that you want only to show up should be placed between the <cfif format is "html"> tags. Oh, just as a side note. Don't break header and results into two tables. Use only one. Or they won't always line up.


<!--- Copy N Paste Below Code --->
<cfparam name="format" default="html">

<cfquery name="myExcel" datasource="theData">
SELECT *
FROM tableOne
</cfquery>

<cfif format is "html">
<cfoutput>
<a href="#cgi.script_name#?format=msxls">Download Excel</a><br>
<a href="#cgi.script_name#?format=msword">Download MS Word</a><br>
<a href="#cgi.script_name#?format=pdf">Download PDF</a><br> <!--- CFMX Server 7 Only --->
<a href="#cgi.script_name#?format=flashpaper">Download Flash Paper</a><br> <!--- CFMX Server 7 Only --->
</cfoutput>
</cfif>

<cfsavecontent variable="body">
<table border=1 align="left" width="30%">
<tr bgcolor="#c0c0c0">
<th width="10">State</th>
<th width="10">City</th>
<th width="10">County</th>
</tr>
<cfoutput query="myExcel">
<tr bgcolor="###iif(currentrow MOD 2,DE('efefef'),DE('ffffff'))#">
<td width="10" valign="top">#state#</td>
<td width="10" valign="top">#city#;</td>
<td width="10" valign="top">#county#</td>
</cfoutput>
</TABLE>
</cfsavecontent>

<cfswitch expression="#format#">

<cfcase value="html"><cfoutput>#body#</cfoutput></cfcase>

<cfcase value="msxls">
<cfheader name="Content-Disposition" Value="Attachment; filename=yourfilename.xls">
<cfcontent type="application/msexcel">
<cfoutput>#body#</cfoutput>
</cfcase>

<cfcase value="msword">
<cfheader name="Content-Disposition" Value="Attachment; filename=yourfilename.doc">
<cfcontent type="application/msword">
<cfoutput>#body#</cfoutput>
</cfcase>

<!--- CFMX Server 7 Only --->
<cfcase value="pdf">
<cfheader name="Content-Disposition" Value="Attachment; filename=yourfilename.pdf">
<cfcontent type="application/acrobat">
<cfdocument format="pdf" pagetype="letter" orientation="portrait" unit="in" encryption="none" fontembed="Yes" backgroundvisible="yes">
<cfoutput>#body#</cfoutput>
</cfdocument>
</cfcase>

<!--- CFMX Server 7 Only --->
<cfcase value="flashpaper">
<cfheader name="Content-Disposition" Value="Attachment; filename=yourfilename.swf">
<cfcontent type="application/x-shockwave-flash">
<cfdocument format="FlashPaper" pagetype="letter" orientation="portrait" unit="in" encryption="none" fontembed="Yes" backgroundvisible="Yes">
<cfoutput>#body#</cfoutput>
</cfdocument>
</cfcase>

</cfswitch>

 

[TruthInSatire]

there have been 3 recent posts similar to this, try keyword searching please.

thread232-1108598

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.