Solved

export to excel button

Posted on 2004-08-31
17
4,761 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
 
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
Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

919 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now