Outputting a Table to Excel in Coldfusion

[maverik59]

Hi, I have been searching for this on the net but have only found some really wierd examples. I have this table

<table width=&quot;100%&quot; border=&quot;1&quot;>
<tr>
<td>test</td>
<td>test1</td>
<td>honda2</td>
<td> </td>
<td> </td>
<td> </td>
</tr>
</table>

and i've been messing with the cffile and cfcontent taqs but have had no success.

Any ideas are much appreciated.

Cheers
Si

 

[cfvogue]

Ho 12345simon,

Write you output to a .csv (comma seperated value) file. Excel will be able to handle this. For exaple:

<cfquery name=&quot;example_csv&quot;>
select column1,column2,column3 from MyTable
</cfquery>

Now do the following:

<cfset ColumnHeadList = &quot;column1,column2,column3&quot;>
<cffile action=&quot;write&quot; file=&quot;example.csv&quot; output=&quot;#ColumnHeadList#&quot; AddNewLine=&quot;yes&quot;>

<cfoutput query=&quot;example_csv&quot;>
<cfset row=&quot;#column1#,#colum2#,#column3#&quot;>
<cffile action=&quot;append&quot; file=&quot;example.csv&quot; output=&quot;#row#&quot; AddNewLine=&quot;yes&quot;>
</cfoutput>

No open the file you just created in Excel and there u are!

chau

 

[csteinhilber]

Simon,
Are you trying to save the table as an Excel file? Or have it open from the web into Excel?
If the former, Chau's example is the simplest way to achieve this. In fact, without writing some sort of DLL, it's most likely the only way.

If the latter, you're on course with CFCONTENT. You also need to use CFHEADER in conjuction with that tag.

Copy the following into a blank document named &quot;open_as_excel.cfm&quot;:

<cfcontent type=&quot;application/vnd.ms-excel&quot;>
<cfheader name=&quot;Content-Disposition&quot; value=&quot;filename=MyExcelFile.xls&quot;>

<table width=&quot;100%&quot; border=&quot;1&quot;>
  <tr>
    <td>test</td>
    <td>test1</td>
    <td>honda2</td>
    <td> </td>
    <td> </td>
    <td> </td>
  </tr>
</table>

It's important that this is the only code in the page. If you have a !DOCTYPE tag or HTML or BODY tag, it won't work (the browser will catch on that it's actually an HTML page and render it instead of open it as Excel).

Of course, you can have whatever CFML you need to produce the table... from a CFQUERY to reading another file to grabbing a WDDX or webservice. Just don't include HTML or BODY tags.

Hope it helps.


-Carl

 

[maverik59]

Many Thanks for your help guys you have opended my mind to this and i tried it both ways and it works great.