Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

export to excel button

Posted on 2004-08-31
17
Medium Priority
?
4,771 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
  • +1
17 Comments
 
LVL 21

Accepted Solution

by:
pinaldave earned 2000 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
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
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

Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

Question has a verified solution.

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

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
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 …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

721 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