0

I'm working on getting an html table to export to excel using coldfusion. I've got the table to export successfully but I have run into 2 problems.

First, the submit button I use to initiate the export is being sent to the excel file with the table data. Is there a way to prevent the export button that's being posted from showing up in my excel spreadsheet?

Secondly, when opening the excel file I get an error message stating "The file format and extension of '.xls' don't match..."

If anyone knows a solution to either problems please share.

Thanks

<cfsetting enablecfoutputonly="Yes">

<!---Navigation Logic--->
<cfif isdefined("form.nav")>
<cfswitch expression="#Form.nav#">

<cfcase value="export">
<cfcontent type="application/vnd.ms-excel">
<cfheader name="Content-Disposition" value="attachment; filename=Radio.xls">
</cfcase>

</cfswitch>
</cfif>

<cfoutput>
<table name ="radTable" id="radTable" class="radTable">
<tr><th>Sort</th><th>Date</th><th>Name</th><th>Volume Knob</th>
<th>Channel Knob</th><th>Antenna</th><th>Microphone</th>
<th>Microphone Clip</th><th>Battery</th><th>Battery Clip</th>
</tr>
<cfloop query="sqlRadio" startrow="#rowStart#" endRow="#rowEnd#">
<tr>
<td>#Sort#</td><td>#DateFormat(Date, "mm/dd/yyyy")#</td><td>#eName#</td>
<td>#vKnob#</td><td>#cKnob#</td><td>#Antenna#</td><td>#Microphone#</td>
<td>#mClip#</td><td>#Battery#</td><td>#bClip#</td>
</tr>
<tr><td colspan="10" style="text-align:left">Comments: #Comments#</td></tr>
</cfloop>
</table>
<form name="pageNav" action="results.cfm" method="post">
<input type="hidden" name="rowStart" value="#rowStart#">
<input type="hidden" name="rowEnd" value="#rowEnd#">
<input type="hidden" name="selRad" value="#radName#">
<input type="submit" name="nav" form="pageNav" value="<<">
<input type="submit" name="nav" form="pageNav" value="<">
<input type="submit" name="nav" form="pageNav" value=">">
<input type="submit" name="nav" form="pageNav" value=">>">
<input type="submit" name="nav" form="pageNav" value="export">
</form>
</cfoutput>
Jimmy Goodson
  • 65
  • 1
  • 10
  • 4
    Using html to generate excel content became a really bad idea when Office 2007 came out. Use ColdFusion's spreadsheet functions instead. – Dan Bracuk Apr 23 '15 at 22:03
  • 1
    You're setting the MIME type to be `application/vnd.ms-excel` and giving it an `.xls` extension, but you're sending HTML. If you're saying it's `application/vnd.ms-excel`, and as an `.xls`, it actually needs to *be* an Excel file. As for how not to include stuff you you don't want to be in there... *don't put it in there in the first place*!! – Adam Cameron Apr 24 '15 at 05:22
  • Did you do a search for the exact error message first? It is a very common question. A quick search on "ColdFusion" and the phrase "The file format and extension of '.xls' don't match" turns up the explanation that it is [due to Extension hardening](http://stackoverflow.com/questions/940045/how-to-suppress-the-file-corrupt-warning-at-excel-download) as explained above. – Leigh Apr 26 '15 at 00:13
  • Just wanted to say TY for those that contributed and tried to help. I came to a solution on my own but I did basically what Pankaj stated. Ty – Jimmy Goodson May 05 '15 at 23:33

1 Answers1

0

You need to just put your form in an if statement like this:

<cfif NOT (structKeyExists(form, "nav") AND form.nav EQ "export")>
    <form>
       .................
       .................
    </form>
</cfif>

The above code will ensure that the form (and hence any form elements) is not available when user chooses to export the table to excel.

Pankaj
  • 1,731
  • 1
  • 13
  • 15
  • Ty this was what I ended up doing, I came to the conclusion on my own but thank you again for taking the time to try to help me. – Jimmy Goodson May 05 '15 at 23:29