Dynamic Excel file from ColdFusion

posted September 3rd 2004 at 1122 EDT in All, ColdFusion

It comes up every now and then about generating a Microsoft Excel File from a ColdFusion Page. Read on for a simple solution to this task that doesn’t involve com objects, csv files, or saving files to the server.

As it turns out, you can open a simple HTML page in excel and it will interpret basic tables as an excel sheet. We can build on this knowledge and force Excel to automagically open up the html page from the web. (It’s a whole different ball game if you do not have excel installed).

First we set the content type so IE or Firefox pass the request on to Excel

<cfcontent type="application/vnd.ms-excel">

Next we put together a simple HTML page

<h2>Page Title</h2>
<table border="1" cellpadding="1" cellspacing="2">
<tr>
    <th>Column 1</th>
    <th>Column 2</th>
</tr>
<tr>
    <td>Data1</td>
    <td>Data 2</td>
</tr> 
</table>

That’s all there is to it. The browser will open it up in excel and the user can save it as an .xls file if they care to.

14 Responses

  1. #1 webmilhouse
    5 years, 10 months ago

    If you are in test and have debugging turned on, this won’t work. You have to set the <cfsetting showdebugoutput="no"> parameter in your page.

  2. #2 John Rico
    5 years, 9 months ago

    Jehiah,

    you can actually use the cfheader tag to output a dynamic file to Excel format. Utilizing this method will allow you to use the cfoutput tags to specify what you want in the excel file. It also allows you to output query results within the created excel file.

    See Code Below:

    <cfcontent type="application/msexcel">
<cfquery datasource="datasource" name="count1">
SELECT * FROM TABLENAME ORDER BY COLUMN
</cfquery>
<cfheader name="Content-Disposition" value="filename=datafile.xls">
<table>
<cfoutput query="count1">
  <TR>
  <TD>Title</TD>
  <TD>#Name#</TD>
  <TD>#Division#</TD>
  <TD>#Location#</TD>
  </TR>
</cfoutput>
</table>
  3. #3 Phil Molaro
    5 years, 6 months ago

    I have been using this method for a while. I saw a tutorial somewhere a while back (and didn’t book mark it) wher ethe guy added in special character codes into his HTML that Excel would read. Specficlaly they were for defining the print region and page breaks. Does anybody know what these are called and where I can see a list of them. I need to insert page breaks and defining the print region would be awesome as well. Please copy me on Email. Thx!

  4. #4 Matthew
    4 years, 8 months ago

    You can use the Apache POI to generate “true” excel files. Using the above examples will work, but it is sort of a way to trick excel to open your files up.

    In conclusion, I personally have used this method as well as generated excel documents with POI. The new cf7 report builder can generate excel documents, and I think it uses POI to do so.

  5. #5 Samson
    4 years, 5 months ago

    Also, one thing that can’t be done any way except for using POI (that I know of) is to create Excel workbooks that include many different sheets (tabs) in them. Our clients need reports with several different recordsets in the Excel file. I’d love to know of an easier way to do this than using POI.

  6. #6 arkarma
    4 years, 2 months ago

    The easiest way to do this is using office xp or above, save an existing spreadsheet in XML format. Then edit in dreamweaver say.

    Insert the two lines at the top of the file :

    Then insert any queries you want to make, in the main table area of the document output your query and save as a .cfm file.

    Wala when the user goes to the url they should have an excel document in native format.

  7. #7 Neil
    4 years ago

    Anyone tried to dump the excel file on the server and then cflocation to it? Like:

  8. #8 Denny
    3 years, 11 months ago

    There is also the option of JExcelAPI. It’s a easy to use API (compared with POI), and can generate XLS files on the fly, right to an outputStream, and then right to the browser. You can have multiple sheets, do formulas, etc., etc.. Only problem is it’s a library that doesn’t ship with CF, so you have to add it to the classpath or use a JavaLoader.cfc type of deal.

    Another good alternative though.

  9. #9 RonZ
    3 years, 10 months ago

    Does anybody know how to open and edit an existing excel file. with coldfuision.

  10. #10 David Levin
    3 years, 1 month ago

    Good article. I think this method beats out the ‘export to excel’ cfc that is floating around the net because it is only compatable with the later versions of excel.

    by the way, why does every comment get smaller and smaller on this site?

  11. #11 tonya
    1 year, 12 months ago

    how would i send that excel file as an attachment in an email?

  12. #12 Leah
    1 year, 5 months ago

    Has anyone been successful with password protecting the excel file using this method of file creation? If so, how? Thanks

  13. #13 Hannes Kiessling
    1 year ago

    Hi,

    i want to create a EXCEL-File, but it must look nice! I must use width for each cell, but it dont works… what i must do?

    my code:

    #WSStatistik[i][2]#
    #WSStatistik[i][3]#
    #WSStatistik[i][4]#
    #WSStatistik[i][5]#
    #WSStatistik[i][9]#
    #WSStatistik[i][8]#
    #WSStatistik[i][7]#
    #WSStatistik[i][10]#
    #WSStatistik[i][6]#

    i hope you can help me…

    cu

  14. #14 Albert Siminone
    11 months, 1 week ago

    I use this
    http://www.emerle.net/programming/display.cfm/t/cfx_query2excel/

    As well as excel2query – both great