Skip navigation
srushing
srushing
Currently Being Moderated

CFCONTENT outputing to Excel issue

Sep 14, 2012 5:22 PM

          Hi folks, 


I have a simple process that does a query and uses CFCONTENT to output a CFTABLE to a content type of "application/msexcel".  Now this works fine 90% of the time.  But I can reproduce a problem where it doesn't quite work as expected, when querying certain records. When this works,  the user gets the file downloaded to them and they open it and they see their data.  But with certain query results, the excel file opens and appears to be blank, but if you scroll down to like line 2000 you will see the embeddded HTML output for the CFTABLE with all the data.  Like this:

 

<table border><tr><th>ID</th>.....

 

 

That's in the Excel file in place of just the outputed data.

 

Because I can reproduce it with the same query over and over, I know it's likely something in the data like an extra quote or something like that, which is throwing off CFCONTENT.   Now, I can't control the data so I'm looking for suggestions for how I can wrap the data inside a CF Tag to preserve the output.


Any ideas?  I don't have access to CF9/10 or I would explore CFSPREADSHEET.  I have CF8.  Thanks

 

 

My code is quite simple, basically like this:

 

<cfcontent type="application/msexcel">
<cfheader name="Content-Disposition" value="filename=Export.xls">

 

    <cfquery datasource="#DS#" name="Getdata">
    select * from tablewhere practiceid = #practiceID#
    order byName
    </cfquery>
   
   
   
   

 

 

  
  
    <cftable query="getdata" htmltable="yes" colheaders="Yes" border="yes">
    <cfcol header="ID" text="#ID#">
    <cfcol header="First Name" text="#firstname#">
644 Views   4 Replies   Latest reply: Dan Bracuk, Sep 15, 2012 3:04 PM
 
Replies
  • srushing
    Currently Being Moderated
    1. srushing,
    Sep 14, 2012 5:49 PM   in reply to srushing
    Report

    Just some follow up info..I know it's something in the data.  If I limit the query to certain rows and certain colums, it will function normally.  But it's not always the same columns for the same rows that make it screw up.  So now I'm trying to think of a way to strip down the text to just text.  I tried quoting them, and tried trimming them, neither worked.  Even tried URLEncodedFormat around them and while that worked with one column, it didn't with all while producing an undesired result in the data anyway.  

     

    So thanks for any input.
    S

     
    |
    Mark as:
  • srushing
    Currently Being Moderated
    2. srushing,
    Sep 14, 2012 6:10 PM   in reply to srushing
    Report

    OK, last update.  If I remove the CFTABLE all together and just use my own <CFoutput> and basic HTML Table tags, all works fine.  Not sure what the issue is with this and CFTABLE, but it's now working without it.

     
    |
    Mark as:
  • Adam Cameron.
    Currently Being Moderated
    3. Adam Cameron.,
    Sep 15, 2012 7:41 AM   in reply to srushing
    Report

    Next time you encounter this, bear in mind that all <cfcontent> does in this case is to set the HTTP response MIME type header, which gives the browser guidance as to how to handle the data it receives.  It does not transform the data in any way.  So if you're using <cftable> (why?  Oh why?) then your're sending HTML and telling the browser it's XLS data.  Leaving it up to Excel to make sense of it (which it will kinda do, if the HTML it tidy).

     

    If you are getting unexpected results, just do what you'd - hopefully - normally do: look at the data you're sending back to the browser, and try to work out where it's going wrong.  I presume in this case there was a tonne of white space generated by <cftable> for some reason.

     

    In general if you want to set the MIME type for the response, get the data you want to send back ready (ie: put it in a variable), then call <cfcontent> with both the TYPE and the RESET attribute, then output the data.  This will mitigate some (although not all) of whitespace CFML churns out as part of "doing business", leaving only the data you actually want in the output buffer.

     

    --

    Adam

     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    4. Dan Bracuk,
    Sep 15, 2012 3:04 PM   in reply to srushing
    Report

    Sending html tables to excel started working poorly when Office 2007 came out.  If you are on CF 9 or higher, use cfspreadsheet.  Otherwise, find Ben Nadel's POI and use it.

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points