How can ColdFusion generate an excel file?
Because recent Excel products support HTML table format, getting ColdFusion to generate and Excel file can be as simple as creating your HTML tables and then using the <cfcontent> tag to set the mime type of your newly generated Excel file.
The following code sample taken from the CF 7 documentation shows a sample of using <cfheader> and <cfcontent> to push a dynamic Excel file to the browser (prompt the user whether to save Excel the file or open it in a browser).
<cfcontent type="application/vnd.msexcel">
<table border="2">
<tr><td>Month</td><td>Quantity</td><td>$ Sales</td></tr>
<tr><td>January</td><td>80</td><td >$245</td></tr>
<tr><td>February</td><td>100</td><td>$699</td></tr>
<tr><td>March</td><td>230</td><td >$2036</td></tr>
<tr><td>Total</td><td>=Sum(B2..B4)</td><td>=Sum(C2..C4)</td></tr>
</table>
Another option to consider is the Jakarta POI project: http://jakarta.apache.org/poi/. As per the project home page: The POI project consists of APIs for manipulating various file formats based upon Microsoft's OLE 2 Compound Document format using pure Java. In short, you can read and write MS Excel files using Java.
This question was written by Jeremy Petersen.
It was last updated on July 28, 2006 at 4:17:11 PM EDT.
CFML Referenced
Categories
Comments
Comment made by Rich Rein on July 28, 2006 at 4:24 PM
I have written a custom tag pair which we use in production today for large exports (html is fine for small to medium size spreadsheets, but for larger sheets or multi-tab layout, the poi project really shines). Feel free to contact me for more information, I will try to get documentation written and find a place to host the project.
Comment made by Sami Hoda on July 28, 2006 at 5:38 PM
How can we contact you?
Comment made by Barney Boisvert on July 28, 2006 at 5:52 PM
The JExcelAPI project is another Java-based option for Excel workbook creation. I tried it and POI recently and found POI to be a bit more cumbersome and difficult to work with.
Comment made by Rob Brooks-Bilson on July 28, 2006 at 6:19 PM
I agree with Rich RE file size. The HTML/XML method of Excel generation result in bloated Excel files. Using POI to generate an actual Excel binary is much more efficient.
Take a look at this tutorial by Dave Ross:
www.d-ross.org/index.cfm?objectid=9C65ED5A-508B-E116-6F4F7F38C6AE167C
Comment made by Sean Corfield on July 29, 2006 at 12:37 AM
http://sourceforge.net/project/showfiles.php?group_id=100854&package_id=108545
Very simple Java CFX tags to read and write Excel files. Search my blog for documentation (I didn't write the tags but I documented them).
Comment made by Ryan Guill on July 29, 2006 at 9:35 PM
You can also save any workbook as xml. then its just a matter of editing your xml document and filling in values as you want with cf, and use cfheader and cfcontent like above to serve the xml document up as a xls. With the xml version, its a little easier to manage, and you can also do all the different styles and things as well.
Comment made by Rich Rein on July 31, 2006 at 10:34 AM
Sorry, thought my email address would be attached, as it asked for it in the comment form. richard.rein {AT} pearson {DOT} com
Ryan - xml does allow you the same control, unfortunately in comparing the poi-generated excel file to the xml one, the xml version of our sample file was over 100MB, while the POI version was under 2MB for the same data and styles...
Comment made by Eric Roberts on November 3, 2006 at 4:53 PM
If you are not familisr with using Java in CF, POI can be cumbersome...but it gets better. The d-ross.org site was invaluable to me in figuring this out. It was actually my intro into using Java in CF. Once I got the hang of it...it was a piece of cake.
The company I was doing a contract gig for was calling an Excel COM object via an ASP page. It was killing thier servers. It would max out the dual processer machine they had it on for about 30 seconds...and this was with just one user calling up the spreadsheet. I created the same spreadsheet using POI and it maxed out for about 2 seconds (on a single processer machine) and was done in about a quarter of the time. The only thing that POI could not do at the time (this may have changed as it was 1 on thier priority list for the next version) was that it could not do charts on the fly.
One of the big benefits of this is, besides the greater efficiency, is that you don't have to have Office installed on your server.
Comment made by shawn gibson on March 27, 2007 at 8:41 PM
Very useful. A (hopefully) slight twist to this...let's say you've got the Excel file, and you would like to use CF (preferably, but not necessarily, via a Flex 2 front end, to mass-populate a SQL table...is this sort of thing possible? Click a button navigate to the Excel file on your computer, import the Excel table, and it will paste-append all the data to after the last record of the specified SQL table?
Or is that but a dream?
Shawn
Comment made by Craig Kaminsky on April 8, 2008 at 12:28 PM
There is a CFC-based utility called POI (http://www.bennadel.com/projects/poi-utility.htm -- I think it's on RIA Forge as well but I cannot find it today) that does a great job of reading and writing Excel files to and from CF.
I've used this utility with CF8 and it was easy to "install" and deploy. If you want to read or write Excel files this is a great tool.
Of course, this is not to say there is anything wrong with the steps above! Just wanted to offer an alternative I only recently found.
Comment made by Leah on February 6, 2009 at 2:05 PM
This is very useful but has anyone been successful with password protecting the excel file using this method of file creation? Thanks