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
Solved

export to excel button

Posted on 2004-08-31
17
4,764 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
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 
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

Title # Comments Views Activity
Running Guacamole on port 80 5 250
how to enable php7 module in nginx configuration file 8 463
htaccess restrict subdomain 4 131
Ways to assess https/ssl websites 3 103
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

828 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