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>
liltygaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.