Solved

export to excel button

Posted on 2004-08-31
17
4,763 Views
Last Modified: 2013-12-24
i have a query output into a table on the web. how can i make this table exportable into excel, i want to have a link that says export to excel

heres the query

<cfquery name="view1" datasource="PR">
SELECT projects.projID, projects.projname, History.period, Sum(History.pmhrsused) AS SumOfpmhrsused, Sum(History.pm2hrsused) AS SumOfpm2hrsused, Sum(History.sehrsused1) AS SumOfsehrsused1, Sum(History.sehrsused2) AS SumOfsehrsused2, Sum(History.sehrsused3) AS SumOfsehrsused3, Sum(History.sehrsused4) AS SumOfsehrsused4, Sum(History.sehrsused5) AS SumOfsehrsused5, projects.pmsell, projects.pm2sell, projects.sesell1, projects.sesell2, projects.sesell3, projects.sesell4, projects.sesell5, projects.PMname, projects.PMname2, projects.SEname1, projects.SEname2, projects.SEname3, projects.SEname4, projects.SEname5
FROM History RIGHT JOIN projects ON History.projID = projects.projID
GROUP BY projects.projID, projects.projname, History.period, projects.pmsell, projects.pm2sell, projects.sesell1, projects.sesell2, projects.sesell3, projects.sesell4, projects.sesell5, projects.PMname, projects.PMname2, projects.SEname1, projects.SEname2, projects.SEname3, projects.SEname4, projects.SEname5;
</cfquery>
0
Comment
Question by:finnstone
  • 7
  • 6
  • 3
  • +1
17 Comments
 
LVL 21

Accepted Solution

by:
pinaldave earned 500 total points
ID: 11945332
Hi finnstone,
<cfquery datasource="MyDataSourceHere" name="qryAccessData">
     SELECT Field1, Field2, FieldN
          FROM AccessTable
</cfquery>
<cfheader name="content-disposition" value="inline;filename=AccessToExcelDump.xls">
<cfcontent type="application/msexcel">
<table border="1">
     <tr>
          <th>Field1</th>
          <th>Field2</th>
          <th>FieldN</th>
     </tr><cfoutput query="qryAccessData">
     <tr>
          <td>#qryAccessData.Field1#</td>
          <td>#qryAccessData.Field2#</td>
          <td>#qryAccessData.FieldN#</td>
     </tr></cfoutput>
</table>

Regards,
---Pinal
0
 
LVL 21

Expert Comment

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

Regards,
---Pinal
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11945357
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 2

Expert Comment

by:iamari
ID: 11945405
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>
0
 
LVL 2

Expert Comment

by:iamari
ID: 11945522
sorry, the link calling the custom tag should obviously be:

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

Author Comment

by:finnstone
ID: 11954249
iamari, it tries to save the name of the page,

so it saves

thisPage.cfm to desktop and not an excel file...?
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11954468
glad to help you,
Have a good day,
Regards,
---Pinal
0
 

Author Comment

by:finnstone
ID: 11954502
how can i make sure that it does not jump to top of page though?
0
 

Author Comment

by:finnstone
ID: 11954507
the table takes up all the space that was above it!
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11954527
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
0
 

Author Comment

by:finnstone
ID: 11954707
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
0
 

Author Comment

by:finnstone
ID: 11954745
i fixed it. i just put that code above the text  that was being cutoff :)
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11954750
<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
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 11954756
wonderful!
Regareds,
---Pinal
0
 

Author Comment

by:finnstone
ID: 11955321
nope not working again, the whole doc loads in excel!
0
 
LVL 2

Expert Comment

by:iamari
ID: 11972360
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.
0
 

Expert Comment

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

lrlarson
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question