export to excel button

Hi finnstone,
Expert shooksm has suggested above mentioned solution. It will load the excel on browser to download by user.

Regards,
---Pinal

Hi finnstone,
https://www.experts-exchange.com/questions/20701038/how-to-export-from-SQL-query-to-excel-no-'COM'-objects-please.html
https://www.experts-exchange.com/questions/20918372/How-to-export-access-table-to-Excel.html
Regards,
---Pinal

there is a custom tag created by Ali Taleb, which i've modified a little to allow ordering columns.
save this code under the \CFUSION\CustomTags directory, naming the file Query2Excel.cfm

<cfif Not IsDefined("Attributes.Query")>
      <cfoutput><div align="center"><b>Error! 'Query' attribute is required in your custom tag.</b></div></cfoutput>
      <cfabort>
</cfif>

<cfset QueryName = "Caller." & #Attributes.Query#>
<cfset NumRows = Evaluate(QueryName & ".RecordCount")>

<cfparam name="Attributes.columnList" default=#Evaluate(QueryName & ".ColumnList")#>
<cfparam name="Attributes.Headers" default=#Evaluate(QueryName & ".ColumnList")#>

<cfif ListLen(Attributes.columnList) gt ListLen(Attributes.Headers)>
      <cfoutput><div align="center"><b>Error! There are more query fields than the items in the 'Headers' list.</cfoutput>
      <cfabort>
<cfelseif  ListLen(Attributes.Headers) gt ListLen(Attributes.columnList)>
      <cfoutput><div align="center"><b>Error! There are more values in the 'Headers' list than the fields returned by the query.</cfoutput>
      <cfabort>
</cfif>

<cfparam name="Attributes.AlternateColor" default="ffffff">    
<cfparam name="Attributes.Type" default="application/vnd.ms-excel">

<cfset Counter = 1>
<cfloop query="#QueryName#">
      <cfset TheValueList = "">
      <cfloop list="#Attributes.columnList#" index="TheValue">
            <cfset TheValue = #Trim(TheValue)#>
            <cfset TheValue = #Evaluate(QueryName & "." & TheValue)#>
            <cfset TheValue = Replace(TheValue, ",", "¦¦", "All")>
            <cfset TheValueList = ListAppend(TheValueList,TheValue)>
            <cfset temp =  SetVariable("ValueList#Counter#", "#TheValueList#")>
      </cfloop>    
      <cfset Counter = Counter + 1>
</cfloop>
<cfset AlternateColor = #Attributes.AlternateColor#>      
<cfcontent type="#Attributes.Type#">
<cfset CharWidth = 10>

<table border="1">
<tr bgcolor="#C0C0C0"><cfloop list="#Attributes.Headers#" index="TheColTitle">
<cfset TheString = "#TheColTitle#"><cfset TheLength = Len(#TheString#) * #charwidth#>
<cfoutput><th width="#TheLength#"><b>#TheString#</b></th></cfoutput></cfloop>
</tr>
<cfset ColorList = "ffffff,#AlternateColor#">
<cfloop from="1" to="#NumRows#" index="n">
      <cfif ColorList is "ffffff,#AlternateColor#">
            <cfset ColorList = "#AlternateColor#,ffffff">
      <cfelse>
            <cfset ColorList = "ffffff,#AlternateColor#">
      </cfif>
<cfloop list="#ColorList#" index="c">
      <cfset CurrentColor = #ListGetAt(c,1)#>
</cfloop>  
<cfset temp =  SetVariable("CurrentList", Evaluate("ValueList#n#"))>
<cfset CurrentList = Replace(CurrentList, ",,", ",&nbsp;,", "All")>
<cfset CurrentList = Replace(CurrentList, ",,", ",&nbsp;,", "All")>
<cfoutput><tr bgcolor="#CurrentColor#"></cfoutput>
<cfloop list="#CurrentList#" index="TheColValue"><cfset TheString = "#TheColValue#"><cfset TheLength = Len(#TheString#) * #charwidth#><cfif Len(TheColValue) is 0><cfset TheString = "&nbsp;"></cfif>
<cfset TheString = Replace(TheString, "¦¦", ",", "All")><cfoutput><td width="#TheLength#">#TheString#</td></cfoutput></cfloop>
</tr>
</cfloop>
</table>

then on your page you'll have:

<cfif isDefined('url.getxls')>
<cfoutput>

<!----------------------------------------------------------
this tag generates a well formed HTML table, which Excel can open as a workbook.
if you specify type=unknown, user will be prompted to save the file on local drive. then he can open it in Excel and save it as a regular workbook
if you don't specify the type, the browser itself will open the table in Excel format
------------------------------------------------------------>
<CF_Query2Excel
Query="view1"
columnList="projID,projName,etc"
headers="Project ID,Project Name,ETC"
type="unknown"
>
<cfabort>
</cfoutput>
</cfif>

<html>
<body>
<a href="thisPage.cfm">Get Excel</a>
</body>
</html>

sorry, the link calling the custom tag should obviously be:

<a href="thisPage.cfm?getxls=yes">Get Excel</a>

iamari, it tries to save the name of the page,

so it saves

thisPage.cfm to desktop and not an excel file...?

glad to help you,
Have a good day,
Regards,
---Pinal

how can i make sure that it does not jump to top of page though?

the table takes up all the space that was above it!

I am sorry, I did not understand what is jumiping on the top and which table is taking up all the space..
sorry for not getting the question.
REgards,
---Pinal

when i put in this code

<cfheader name="content-disposition" value="inline;filename=AccessToExcelDump.xls">
<cfcontent type="application/msexcel">


the table goes right to the top of the page! it covers up the text that used to appear above the table

i fixed it. i just put that code above the text  that was being cutoff :)

<cfheader name="content-disposition" value="inline;filename=AccessToExcelDump.xls">
<cfcontent type="application/msexcel">
your text goes here
<td></td>

or...you can put that text in the table too...like


<cfheader name="content-disposition" value="inline;filename=AccessToExcelDump.xls">
<cfcontent type="application/msexcel">
<td>your text goes here</td>
<td></td>

Or this is not what you asked...
Regards,
---Pinal

wonderful!
Regareds,
---Pinal

nope not working again, the whole doc loads in excel!

finnstone, what is saving is really an html file. you can either rename it to thisPage.xls or open it as is in Excel. then you can save it as a regular excel workbook

of course you can save it anywhere you want, not necesarily on the desktop.

What am I doing wrong? I use the code above, and I get html output in my excel file....

lrlarson