Skip navigation
lwfg
lwfg
Currently Being Moderated

query results to Excel problems

Jun 18, 2012 4:33 AM

I'm trying to get results from a ColdFusion query called q to go into an Excel file.

There are 2 things going wrong.

1. An Excel message comes up that says "The file you are trying to open 'stc.xls', is in a different format than specified by the file extension..."

2. character values in col1 01,02,03,... are coming over to Excel as numbers 1,2,3,...

 

How do I fix those 2 things ?

 

This is the way I'm trying to do it.

<cfsetting enablecfoutputonly="yes">

<cfcontent type="application/msexcel">

<cfheader name="Content-Disposition" value="filename=test.xls">

<cfoutput>

   <table>

      <cfloop index="ii" from="1" to="#q.recordcount#">

         <tr>

            <td>

              #q.col1[ii]#

            </td>

            <td>

              #q.col2[ii]#

            </td>

         </tr>

      </cfloop>

   </table>

</cfoutput>

782 Views   4 Replies   Latest reply: -==cfSearching==-, Jun 18, 2012 2:14 PM
 
Replies
  • Dan Bracuk
    Currently Being Moderated
    1. Dan Bracuk,
    Jun 18, 2012 4:59 AM   in reply to lwfg
    Report

    Using html table tags stopped working when MS introduced Office 2007.  Use cfspreadsheet instead.

     
    |
    Mark as:
  • lwfg
    Currently Being Moderated
    2. lwfg,
    Jun 18, 2012 5:54 AM   in reply to Dan Bracuk
    Report

    Thank you. That's just for version 9, right ?

     
    |
    Mark as:
  • Dan Bracuk
    Currently Being Moderated
    3. Dan Bracuk,
    Jun 18, 2012 9:25 AM   in reply to lwfg
    Report

    cfspreadsheet was added for version 9.  If you are on an earlier version, check out this link.  http://www.bennadel.com/projects/poi-utility.htm

     
    |
    Mark as:
  • -==cfSearching==-
    Currently Being Moderated
    4. -==cfSearching==-,
    Jun 18, 2012 2:14 PM   in reply to Dan Bracuk
    Report

    Dan Bracuk wrote:

     

    Using html table tags stopped working when MS introduced Office 2007.  Use cfspreadsheet instead.

     

    It is not that it stopped working. Excel 2007 introduced extension hardening for increased security. So Excel warns you when the file content does not match the file extension, such as your code where cfheader claims it is an *.xls file but the content is actually html.

     

    Users can still open the file, they will just get a warning first. The only way to prevent the warning is to a) disable the registry setting which controls the behavior OR b) ensure the content matches the file extension. For example, generating a true Excel file with cfspreadsheet or the POIUtility.  Another option is to generate an Excel xml file. IIRC, Ben Nadels blog also has some good articles on that as well.

     
    |
    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