• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

Generating Excel Files With ColdFusion

Hi, I am generating Excel files from ColdFusion, and most of the time the Excel generation is working, but in some instances (from what I can tell, when the query is very large), instead of outputting the formatted HTML within Excel, the actual HTML code displays within Excel.  Does anyone have any ideas as to why this may be happening?

Here is what the code basically looks like:

<cfif isDefined ('url.excel') and url.excel eq 1>
    <cfcontent type="application/vnd.ms-excel">
    <cfheader name="Content-Disposition" value="xyzReport_#dateFormat(now(),'mmddyyyy')#.xls>
</cfif>

<cfquery name="queryName" datasource="#dsn#">
   SQL Code
</cfquery>

<cfoutput>
    <h1>Report Name</h1>
   <cfloop query="queryName">
       <table>
           <tr>
              <td>Data output goes in this table</td>
           </tr>
        </table>
     </cfloop>
</cfoutput>
0
liltyga
Asked:
liltyga
  • 4
  • 3
1 Solution
 
Rory ArchibaldCommented:
How many rows does the query output?
0
 
liltygaAuthor Commented:
For these larger reports, sometimes over 3,000 rows.
0
 
_agx_Commented:
Yeah, excel-like-html reports can be temperamental sometimes.  Especially if it's a dual purpose report (on-screen and excel download). Sometimes you end up with extra output (headers, js/css includes, etc..) that break the html as far as Excel is concerned.

So if you save the HTML to a file, and open it with Excel - does Excel interpret it correctly?

1) It may not be the issue, but try swapping your cfcontent and cfheader:
    <cfoutput>
       <cfheader name="Content-Disposition" value="xyzReport_#dateFormat(now(),'mmddyyyy')#.xls>
       <cfcontent type="application/vnd.ms-excel">
    </cfoutput>

2) Try using CFSETTING enableCFoutputOnly="true".  So you're _only_ outputting stuff enclosed in cfoutput tags. It will also get rid of extra white space.

3) Are you really generating a separate <table> for each record? That's 3000 tables ...

4) You could also try capturing the report data in variable instead using cfsavecontent. They cfoutputting everything in one shot.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
liltygaAuthor Commented:
Hi aqx:

Thanks for the feedback! Sorry, no, I'm looping the <tr>s, not the tables; just quickly trying to recap what was going on. I'll give your ideas a try in the morning and let you know how they work out.
0
 
_agx_Commented:
Sounds good.  

(BTW: For tomorrow ...

If that's the real cfheader, it's missing a few attributes. It should be more like

 <cfheader name="Content-Disposition"
      value="attachment; filename=xyzReport_#dateFormat(now(),'mmddyyyy')#.xls>
OR....
 <cfheader name="Content-Disposition"
      value="inline; filename=xyzReport_#dateFormat(now(),'mmddyyyy')#.xls>
)
0
 
_agx_Commented:
With the proper quotes of course ...;)
0
 
liltygaAuthor Commented:
Thanks again! I think I'm on to something; even after reducing the query to output 15 rows max, use the cfsetting  enableCFOutputOnly = true, and switched the the order of cfheader and cfoutput, I get the finicky HTML output. (There are several other reports that are working just fine, so I am stepping through the code; when I remove the last half of the report, it generates just fine, so it seems to be a matter of figuring out what the offending code is. I may not have gotten to this point as quickly though, without your help!
0
 
_agx_Commented:
Glad I could help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now