Solved

export to excel button

Posted on 2004-08-31
17
4,765 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
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…

763 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