|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
I'm trying to query a database and write the results to an Excel spreadsheet.
The page seems to be compiling correctly and I've tested the table outputting to a .cfm page which works fine. However as soon as I try and write to an Excel file I lose my table in to thin air (ie no output, a blank spreadsheet). Here's my code: <cfquery name="getReportDetails" datasource="myDataSourceName"> SELECT t1.RedemptionID, t2.Model, t2.AwardName, t1.Qty, SUM((t2.cost * t1.Qty) + t2.Est_Delivery) AS cost FROM tblo_memberorders t1, tblp_Awards t2 WHERE t1.AwardID = t2.AwardID AND t1.Status = 1 AND t2.SupplierID = #Form.Supplier# GROUP BY t1.RedemptionID, t2.Model, t2.AwardName, t1.Qty, cost </cfquery> <cfset totalCost = 0> <!--- write the column headers ---> <cffile action="WRITE" file="orderForm.xls" output=" <cfcontent type='application/ms-excel'> <table> <tr> <td><strong>RepemtionID</strong></td> <td><strong>Model No</strong></td> <td><strong>Product Name</strong></td> <td><strong>Contact Name</strong></td> <td><strong>Business Name</strong></td> <td><strong>Address</strong></td> <td><strong>Suburb</strong></td> <td><strong>State</strong></td> <td><strong>Postcode</strong></td> <td><strong>Phone</strong></td> <td><strong>Qty</strong></td> <td><strong>Cost</strong></td> </tr>" addnewline="Yes"> <!--- append the other data to the table ---> <cfloop query="getReportDetails"> <cffile action="APPEND" file="orderForm.xls" output=" <tr> <td><cfoutput>#getReportDetails.RedemptionID#</cfoutput></td> <td><cfoutput>#getReportDetails.Model#</cfoutput></td> <td><cfoutput>#getReportDetails.AwardName#</cfoutput></td> <td>Justine</td> <td>CiEvents</td> <td>115b Ferrars Street</td> <td>South Melbourne</td> <td>Victoria</td> <td>3205</td> <td>(03) 86967013</td> <td><cfoutput>#getReportDetails.Qty#</cfoutput></td> <td>$<cfoutput>#getReportDetails.cost#</cfoutput> <cfset totalCost = totalCost + getReportDetails.cost></td> </tr>" addnewline="Yes"> </cfloop> <!--- end table in the file ---> <cffile action="APPEND" file="orderForm.xls" output=" <tr> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td></td> <td><strong>Total Cost:</strong></td> <td><strong>$<cfoutput>#totalCost#</cfoutput></strong></td> </tr> </table>" addnewline="Yes"> <h2>Your report has been generated... <a href="orderForm.xls" target="_blank">Click here to complete the download</a></h2> Please help me find my table as I'm lost without her!!! Thanks in advance, Matty |
|
#2
|
|||
|
|||
|
This is how I accomplished the task...
<cfset TabChr = Chr(9)>
<cfset Heading= ListChangeDelims(#lookuppeople.ColumnList#,"#TabChr#", ",")> <cffile action="write" file="i:\web-docs\qopboard\reports\#auth_user##dateformat(Now() ,"mm-dd")##timeformat(now(),"hh-mm")#.xls" output="#Heading#" ADDNEWLINE="Yes"> <cfset temp = ""> <cfloop query="lookuppeople"> <cfloop index="X" LIST="#lookuppeople.ColumnList#"> <cfset temp= ListAppend(temp, #evaluate(x)#,#TabChr#)> </cfloop> <cffile ACTION="Append" FILE="i:\web-docs\qopboard\reports\#auth_user##dateformat(Now() ,"mm-dd")##timeformat(now(),"hh-mm")#.xls" OUTPUT="#temp#" ADDNEWLINE="Yes"> <CFSET temp = ""> </cfloop> <br> Report file is located <a href="<A href="http://infonet.ahc.comqopboardreportsahc">here</a">http://infonet.ahc.com\qopboard\reports\ahc">here</a>. |
![]() |
| Viewing: Dev Articles Community Forums > Programming > Cold Fusion Development > Exporting dynamic query to Excel |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|