query results to Excel problems

[Guest]

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>

[Dan_Bracuk]

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

[Guest]

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

[Dan_Bracuk]

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

[-__cfSearching__-]

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.