Skip to main content
S
srushing
Known Participant
September 15, 2012
Question

CFCONTENT outputing to Excel issue

  • September 15, 2012
  • 2 replies
  • 1612 views

          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#">
    This topic has been closed for replies.

    2 replies

    S
    srushingAuthor
    Known Participant
    September 15, 2012

    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.

    A
    Adam Cameron.
    Inspiring
    September 15, 2012

    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

    S
    srushingAuthor
    Known Participant
    September 15, 2012

    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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices