Creating And Streaming Simple Microsoft Excel Files With ColdFusion : Kinky Solutions : A Student's Perspective by Ben Nadel

Creating And Streaming Simple Microsoft Excel Files With ColdFusion

Posted December 15, 2006 at 8:24 AM

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.

Launch code in new window » Download code as text file »

<!---
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" )>
<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"
/><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.
--->
<cfset strFilePath = GetTempFile(
GetTempDirectory(),
"excel_"
) />
<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.

Download Code Snippet ZIP File

Related Blog Entries

Maintaining Line Breaks In An HTML-Excel File

Breaking Enormous CSV Files Into Smaller CSV Files

Problems With The Organic Model Of Distributed Configuration

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.

Posted by Bruce on Dec 15, 2006 at 10:32 AM

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.

Posted by Marco Antonio C. Santos on Dec 15, 2006 at 1:04 PM

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.

Posted by Ben Nadel on Dec 15, 2006 at 1:05 PM

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.

Posted by Ben Nadel on Dec 15, 2006 at 1:10 PM

Without path class? Mark Mandel Java Loader (http://www.compoundtheory.com/?action=displayPost&ID=114) can help you ya? I'll try here too! ;-)

Posted by Marco Antonio C. Santos on Dec 15, 2006 at 1:54 PM

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.

Posted by Ben Nadel on Dec 15, 2006 at 2:11 PM

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 :)

Posted by Daniel Elmore on Dec 31, 2006 at 6:53 PM

Daniel,

Thanks for the comments. I am glad you like the solution. And thanks for posting the tip for others to see.

Happy 2007!

Posted by Ben Nadel on Jan 1, 2007 at 10:22 AM

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?

Posted by Doug on Jan 23, 2007 at 10:24 AM

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?

Posted by Ben Nadel on Jan 23, 2007 at 10:45 AM

Everything is basically the same one is a desktop computer the other that is having the problem is a laptop.

Posted by Doug on Jan 24, 2007 at 8:59 AM

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.

Posted by Ben Nadel on Jan 24, 2007 at 4:01 PM

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

Posted by Freddy on Jul 1, 2007 at 9:39 PM

@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.

Posted by Ben Nadel on Jul 1, 2007 at 9:43 PM

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...

Posted by Duncan on Sep 19, 2007 at 12:55 PM

Excellent article! Thanks for posting this!

Posted by Cathy on Oct 7, 2007 at 8:36 PM

very helpful post. made my day!

Posted by Amit on Jul 2, 2008 at 8:33 AM

Post Comment | Ask Ben

Creating And Streaming Simple Microsoft Excel Files With ColdFusion : Kinky Solutions : A Student's Perspective by Ben Nadel