?
Solved

Excel export isn't formatting correctly in Coldfusion MX 7

Posted on 2007-10-03
4
Medium Priority
?
1,243 Views
Last Modified: 2008-01-09
I'm trying to export a report in Excel using Coldfusion, however all the data displays in just 1 cell.  How do I make the report display the way it want it to.  

Here is my code for the excel report

<cfswitch expression="#form.report#">
      <cfcase value="1">
      <!--- feature --->
            <cfinclude template="qry_feature.cfm">
            <cfset strReport = 'Feature Report'>
      </cfcase>
      
      <cfcase value="2">
      <!--- feature same day add/delete --->
            <cfinclude template="qry_featuresamedayadddelete.cfm">
            <cfset strReport = 'Features Same Day ADD/DEL Report'>
      </cfcase>
      
      <cfcase value="3">
      <!--- feature same day add/delete --->
            <cfinclude template="qry_servicesamedayadddelete.cfm">
            <cfset strReport = 'Services Same Day ADD/DEL Report'>
      </cfcase>
      
      <cfcase value="4">
      <!--- feature same day add/delete --->
            <cfinclude template="qry_servicedupes.cfm">
            <cfset strReport = 'Service Duplicates Report'>
      </cfcase>
      
      <cfcase value="5">
      <!--- feature same day add/delete --->
            <cfinclude template="qry_prepaydupe.cfm">
            <cfset strReport = 'Prepay Duplicates Report'>
      </cfcase>

<cfcase value="6">
      <!--- feature same day add/delete --->
            <cfinclude template="qry_contractdupes.cfm">
            <cfset strReport = 'Contract Duplicates Report'>
      </cfcase>
      
<cfcase value="7">
      <!--- feature same day add/delete --->
            <cfinclude template="qry_featuredupe.cfm">
            <cfset strReport = 'Feature Duplicate Report'>
      </cfcase>

      
      <cfdefaultcase>
            <p>error!</p>
      </cfdefaultcase>
</cfswitch>

<cfset strReport = strReport & '  Current Cycledates: ' & form.cycledates &'  '>
<cfset strReport = strReport & 'Current Markets: '>
<cfif form.which_markets NEQ ''>
      <cfset strReport = strReport & form.which_markets & '  '>
<cfelse>
      <cfset strReport = strReport & 'All Markets  '>
</cfif>      
<cfset strReport = strReport & 'Current Channels: '>
<cfif form.which_channels NEQ ''>
      <cfset strReport = strReport & form.which_channels & '  '>
<cfelse>
      <cfset strReport = strReport & 'All Channels  '>
</cfif>

<cfsavecontent variable="outitgoes"><cfoutput>#strReport##chr(13)#</cfoutput>
      <cfoutput>#Replace('#getmain.columnlist#',',',chr(0009), 'ALL')##chr(13)#</cfoutput>
      <cfoutput query="getmain">
            <cfloop index="i" list="#getmain.columnlist#">
                  #evaluate(i)##chr(0009)#
            </cfloop>
      </cfoutput>
</cfsavecontent>
<CFHEADER NAME="Content-Disposition" VALUE="Inline;filename=filtereddata#CreateUUID()#.xls">
<cfcontent type="application/vnd.ms-excel" >
<cfoutput>#outitgoes#</cfoutput>

I want it similiar to whats displayed on the screen when a user runs the report.  Here is an example of that code

<table class="reportTable">
      <tr>                          
            <th class="reportHeader">Market</th>
            <th class="reportHeader">Channel</th>
            <th class="reportHeader">Emplid</th>
            <th class="reportHeader">Accountnbr</th>
            <th class="reportHeader">Phonenbr</th>
            <th class="reportHeader">Rateplan</th>
            <th class="reportHeader">Recordtype</th>
            <th class="reportHeader">Servicetype</th>
            <th class="reportHeader">Product</th>
            <th class="reportHeader">Count(inputseq)</th>
      </tr>
      <cfoutput query="getmain" >
            <tr>
                  <td class="reportCell">#getmain.Market#</td>
                  <td class="reportCell">#getmain.Channel#</td>
                  <td class="reportCell">#getmain.Emplid#</td>
                  <td class="reportCell">#getmain.Accountnbr#</td>
                  <td class="reportCell">#getmain.Phonenbr#</td>
                  <td class="reportCell">#getmain.rateplan#</td>
                  <td class="reportCell">#getmain.Recordtype#</td>
                  <td class="reportCell">#getmain.Servicetype#</td>
                  <td class="reportCell">#getmain.Product#</td>
                  <td class="reportCell">#getmain.Countinputseq#</td>
            </tr>
      </cfoutput>
</table>
0
Comment
Question by:rudodoo
  • 2
  • 2
4 Comments
 
LVL 10

Accepted Solution

by:
js_vaughan earned 2000 total points
ID: 20012240
When outputting as an excel object, all your extra white space is going along with it.  Good programming practice, but not good for excel.  Keeping everything in your <output query...> all in one line should fix the problem fine:

<cfsavecontent variable="outitgoes"><cfoutput>#strReport##chr(13)#</cfoutput>
<cfoutput>#Replace('#getmain.columnlist#',',',chr(0009), 'ALL') & chr(13) & chr(10)#</cfoutput>
<cfoutput query="getmain"><cfloop index="i" list="#getmain.columnlist#">#evaluate(i) & chr(9)#</cfloop>#chr(13) & chr(10)#</cfoutput>
</cfsavecontent>
<CFHEADER NAME="Content-Disposition" VALUE="Inline;filename=filtereddata#CreateUUID()#.xls">
<cfcontent type="application/vnd.ms-excel" >
<cfoutput>#outitgoes#</cfoutput>
0
 

Author Comment

by:rudodoo
ID: 20014683
When I run a report that doens't have any data included it still shows all the column headers in one cell.  Is there a way fix that
0
 

Author Comment

by:rudodoo
ID: 20015039
Also, the problem is still happening on one of the reports titled Feature Report
0
 
LVL 10

Expert Comment

by:js_vaughan
ID: 20016348
Unfortunately I couldn't reproduce your problems.  I tried a query with 0 results and all my columns still return in their own cells.  If the code works the majority of the time, then all I can suggest is trial and error of pinpointing exactly what data in the database is making the results go haywire.

Also, take a look inside your cfincludes.  You may have code in there that is causing the disruption.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month16 days, 18 hours left to enroll

862 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