Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at Scotch On The Rock (SOTR) 2010 (London) with: Mark Drew

Creating And Streaming Simple Microsoft Excel Files With ColdFusion

By Ben Nadel on
Tags: 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.




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.

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

Daniel,

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

Happy 2007!

Reply to this Comment

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?

Reply to this Comment

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?

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

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?

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

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)

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

Reply to this Comment

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

Reply to this Comment

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.

Reply to this Comment

@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

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.