Creating And Streaming Simple Microsoft Excel Files With ColdFusion
I was demonstrating to a co-worker the other day how you can easily create Microsoft Excel data files and stream them to the browser as an attachment that the end user can then open (in Excel) or save to the file system. This blew his mind, so I thought I would share it here as well.
You don't need Java objects (ex. JExcel) or COM objects if you want to create simple Excel files. This technique uses basic HTML and just requests that it be interpreted by Microsoft Excel, not the user's browser. This does NOT create a full-fledged Excel document. When you open this document in Excel you will see that it does not have a proper grid. However, if you copy and paste the Excel data into a new Excel sheet, it works flawlessly. For basic file creation, I consider this is to be completely acceptable (and so do all of my clients).
To create the Excel file all you have to do is create a basic HTML page that caters to the Excel application way of doing things (such as using PT instead of PX for font sized, border widths, padding, etc) and then stream it to the browser with the proper CFHeader and CFContent tags. The CFHeader tag is always the same, but there are several ways to handle the content. You can stream it as text, as a file attachment, or as a binary data stream. I suspect that the binary data stream and the file stream do pretty much the same thing, but as the "Variable" attribute only came about in ColdFusion MX 7, I will demonstrate both methodologies.
In the code below, also notice that I put in the ability to preview the code (?preview in URL). This has nothing to do with creating Excel files, this was just for debugging purposes and to demonstrate flexability.
<!---
Create and store the simple HTML data that you want
to treat as an Excel file.
--->
<cfsavecontent variable="strExcelData">
<style type="text/css">
td {
font-family: "times new roman", verdana ;
font-size: 11pt ;
}
td.header {
background-color: yellow ;
border-bottom: 0.5pt solid black ;
font-weight: bold ;
}
</style>
<table>
<tr>
<td class="header">Conversational Phrase</td>
<td class="header">Daily Count</td>
</tr>
<tr>
<td>Sweet ass sweet!</td>
<td>3</td>
</tr>
<tr>
<td>Freakin' Sweet!</td>
<td>15</td>
</tr>
<tr>
<td>Heck yeah!</td>
<td>5</td>
</tr>
<tr>
<td>Booya Grandma!</td>
<td>0</td>
</tr>
</table>
</cfsavecontent>
<!---
Check to see if we are previewing the excel data. This
will output the HTML/XLS to the web browser without
invoking the MS Excel applicaiton.
--->
<cfif StructKeyExists( URL, "preview" )>
<!--- Output the excel data for preview. --->
<html>
<head>
<title>Excel Data Preview</title>
</head>
<body>
<cfset WriteOutput( strExcelData ) />
</body>
</html>
<!---
Exit out of template so that the attachment
does not process.
--->
<cfexit />
</cfif>
<!---
ASSERT: At this point, we are definately not previewing the
data. We are planning on streaming it to the browser as
an attached file.
--->
<!---
Set the header so that the browser request the user
to open/save the document. Give it an attachment behavior
will do this. We are also suggesting that the browser
use the name "phrases.xls" when prompting for save.
--->
<cfheader
name="Content-Disposition"
value="attachment; filename=phrases.xls"
/>
<!---
There are several ways in which we can stream the file
to the browser:
- Binary variable stream
- Binary file stream
- Text stream
Check the URL to see which of these we are going to end
up using.
--->
<cfif StructKeyExists( URL, "text" )>
<!---
We are going to stream the excel data to the browser
through the standard text output stream. The browser
will then collect this data and execute it as if it
were an attachment.
Be careful to reset the content when streaming the
text as you don't want white-space to be part of the
streamed data.
--->
<cfcontent
type="application/msexcel"
reset="true"
<!--- Write the output. --->
/><cfset WriteOutput( strExcelData.Trim() )
<!---
Exit out of template to prevent unexpected data
streaming to the browser (on request end??).
--->
/><cfexit />
<cfelseif StructKeyExists( URL, "file" )>
<!---
We are going to stream the excel data to the browser
using a file stream from the server. To do this, we
will have to save a temp file to the server.
--->
<!--- Get the temp file for streaming. --->
<cfset strFilePath = GetTempFile(
GetTempDirectory(),
"excel_"
) />
<!--- Write the excel data to the file. --->
<cffile
action="WRITE"
file="#strFilePath#"
output="#strExcelData.Trim()#"
/>
<!---
Stream the file to the browser. By doing this, the
content buffer is automatically cleared and the file
is streamed. We don't have to worry about anything
after the file as no page content is taken into
account any more.
Additionally, we are requesting that the file be
deleted after it is done streaming (deletefile). Now,
we don't have to worry about cluttering up the server.
--->
<cfcontent
type="application/msexcel"
file="#strFilePath#"
deletefile="true"
/>
<cfelse>
<!---
Bey default, we are going to stream the text as a
binary variable. By using the Variable attribute, the
content of the page is automatically reset; we don't
have to worry about clearing the buffer. In order to
use this method, we have to convert the excel text
data to base64 and then to binary.
This method is available in ColdFusion MX 7 and later.
--->
<cfcontent
type="application/msexcel"
variable="#ToBinary( ToBase64( strExcelData.Trim() ) )#"
/>
</cfif>
We are creating basic HTML documents here for use within Microsoft Excel, but that doesn't mean that we can go ahead an use just any old HTML. A lot of the HTML that you might use, especially when it comes to CSS/Styles are not used by Excel. To get a better idea of what Excel will accept create an actual Excel data file and then save it as a web page and view the source. You will see that the Excel "web preview" document has some crazy HTML most of the time, but you will also see exactly what it can work with.
When it comes to streaming Excel document data to the browser, I would always go with either the file stream or the Variable stream. The streaming of text in the standard buffer has caused problems for me in the past, especially for very large files. Some characters don't seem to play well with the stream or get changed / corrupted during the process. Not sure how it handles large, extended characters; probably not well.
I am sure this is review to a lot of you, but hopefully this was a Eureka moment for a lot more of you.
Want to use code from this post? Check out the license.
Reader Comments
Ben - nice example. I've been using the creating Excel file code provided by Nate Weiss in chapter 33 of CF MX7 Web Application Construction Kit. His example code shows how to use a query's results to create tab-delimited output and then return the output to the user in such a way that the browser lets the user open the returned output in Excel. The data is lined up nicely in the grids in Excel.
His code is quite short and has worked well for what we need. We often put "Export to Excel" links on our pages to allow users to easily save the data to an Excel file on their computer.
So your readers may also want to check out chapter 33. In that chapter are some other suggestions about creating output that can be read by Excel.
Ben, only one tip: this technique doesn't work with OpenOffice documents. OO will try to open with Writer(MS Word similar) application loosing file style. Using JExcel to create an "real" Excel file allow user to open that with OO Calc application.
Bruce,
Excellent suggestion. Yes, creating Tab-delimited files is a great way to create data files in Excel. The only real difference between that way and the example above is that by using HTML, you can create formatted excel file. The tab-delimited file doesn't do anything in the way of formatting.
Both great ideas though.
Antonio,
That is good to know. I have zero experience with Open Office. I am in the middle of trying to create a JExcel file without using the java class paths, but have not been able to do it yet. There is some sort of conflict somewhere.
I will post that when I get it to work.
Without path class? Mark Mandel Java Loader (http://www.compoundtheory.com/?action=displayPost&ID=114) can help you ya? I'll try here too! ;-)
I am actually using the JavaProxy as he has demoed. However, we ALSO have the JXL package installed in ColdFusion. I am not sure if it is getting confused between the one I am calling and the one that is in the Java class paths.
Ben, this is probably the best article on CF-Excel creation on the web. I was trying to figure out how to generate comments on field column headers (those little yellow notepads that pop up). Using your suggestion of exporting an excel sheet for the web revealed the proper code. A simple idea that hadn't crossed my mind. Thanks!
For anyone else wanting to add comments:
Export your example excel file and look for the <v:shape> XML data, that's all you need. You don't need all the JS code that it generates unless you want the excel sheet to open as a HTML file.
Daniel Elmore :)
Daniel,
Thanks for the comments. I am glad you like the solution. And thanks for posting the tip for others to see.
Happy 2007!
Have you ever heard of someone being able to open the resulting file up in Excel on one computer but not on another. I believe we are using the Nate Weiss tab-delimited output method and it opens fine on one compyter but then when the information is opened on a laptop running the same version of Excel it all goes into just the first column, any ideas?
Doug,
That is strange indeed. I have not had a ton of experience with this, so I cannot say for sure. Are the operating systems different for the two dif computers? Or same OS/ same excel edition?
Everything is basically the same one is a desktop computer the other that is having the problem is a laptop.
Doug, that is too strange. The only think I can suggest is to create an actual Excel document (using Excel). Then output it in the proper format (export / save as in excel) on one computer, then on the other, and the compare the two different outputs. Other than that, I am out of ideas.
Sorry.
hi,
just want to know how to deal table that have img ...
I follow your method ... but it doesn't work when the table data got image obj ...
any idea ?
thanks
@Freddy,
I have not tried to do this with an IMG. You might want to try looking into a Java-based solution like POI to deal with embedded image data. Sorry I could not be of more help.
I've had a similar problem to Doug - i can open the file on one computer and not on another - same excel version, same files, etc. Just an FYI, but very much interested in solutions...
Excellent article! Thanks for posting this!
very helpful post. made my day!
Thanks for the example (2.5 years later). I am wondering if you have been able to achieve the same results exporting data either from a cfgrid or directly from a database.
Also, the tab-delimited option posted by Bruce seems interesting but I do not have that book handy and have not been able to find any sample code. Have you given that method a try?
@Dan,
When I need to create a large XLS file, I usually end up creating just a CSV (either tab or comma delimited) and just stream it back using CFContent / CFHeader. Works quite nicely. For smaller files, I will use my POI Utilities.
Ok. I should preface all of my posts with the fact that I am an utter newbie when it comes to CFML or programming for the matter.
That being said what the heck are you talking about ;)
Hi,
This was amazingly handy. Like you mentioned in your post above I often need to give people the option to download datasets as .xls documents.
Using this and your post here (Query to Array)
www.bennadel.com/blog/1387-Ask-Ben-Exporting-A-Query-To-CSV-Without-Using-Column-Names.htm
it was incredibly easy to write a generic xls generator, without even knowing the column names etc.
I've plugged that into a fusebox framework, and you've saved me days of dev time :-)
You providded some great code in your cmf_excel POI Utility ColdFusion Component. :)
I just wish you had used a more professional set of data in you samples.
I had to delete the sample data from my computer once I saw what it contained. :(
Unfortunately with the latest IE browsers, I'm now getting a message that the 'file is in a different format to the extension, and that it may be corrupted. Do you still want to open it etc....
It still opens fine if you click the 'yes' prompt but the message is a bit alarmist.
Has anyone found a work around for that? I'm going to have to go back to the csv output at present.
@James,
I know this is outdated, but the search engines seem to keep picking it up so hence the update:
Since this aricle, coldfusion now has a built in spreadsheets tag called cfspreadsheet.
James, is getting the error because MS Office is now looking for actual excel documents. While the method here will work, its not the most elegant, if time permits (and especially if someone is paying you for an export to excel feature), you should build and serve an actual spreadsheet.
http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec17cba-7f87.html