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 BFusion / BFLEX 2010 (Bloomington, Indiana) with: John Farrar

Creating Microsoft Excel Documents With ColdFusion And XML

By Ben Nadel on
Tags: ColdFusion

A little while back I posted about how to create simple Microsoft Excel documents using HTML. That's good and all and provides some basic formatting, but it is a definite hack. Someone suggested to me that I look into using XML to create Excel documents. As it turns out, using XML to create an Excel file is not only easy, it provides much more control over formatting, not to mention that I can rock cell formulas and a bunch of other things that make Excel great. But, perhaps the coolest thing is that, unlike the HTML Table method, the XML document opens up in Microsoft Excel document as if it were a true Excel document; the entire grid is laid out nicely, tabs are there, it's beautiful.

To start off with, I am going to show you how to create a simple data grid based on a query. As always, let's build a query to test with. For this one, it will be a query of movies, their name, rating, and some additional information:

  • <!--- Create query to ouptut to excel. --->
  • <cfset qMovie = QueryNew(
  • "id, name, date_watched, rating, has_fighting, has_boobies",
  • "CF_SQL_INTEGER, CF_SQL_VARCHAR, CF_SQL_DATE, CF_SQL_DECIMAL, CF_SQL_BIT, CF_SQL_BIT"
  • ) />
  •  
  • <!--- Add rows to query. --->
  • <cfset QueryAddRow( qMovie, 5 ) />
  •  
  • <!--- Set row data. --->
  • <cfset qMovie[ "id" ][ 1 ] = JavaCast( "int", 1 ) />
  • <cfset qMovie[ "name" ][ 1 ] = JavaCast( "string", "Terminator 2" ) />
  • <cfset qMovie[ "date_watched" ][ 1 ] = JavaCast( "float", "2006/05/25" ) />
  • <cfset qMovie[ "rating" ][ 1 ] = JavaCast( "float", 10.0 ) />
  • <cfset qMovie[ "has_fighting" ][ 1 ] = JavaCast( "boolean", true ) />
  • <cfset qMovie[ "has_boobies" ][ 1 ] = JavaCast( "boolean", false ) />
  •  
  • <cfset qMovie[ "id" ][ 2 ] = JavaCast( "int", 2 ) />
  • <cfset qMovie[ "name" ][ 2 ] = JavaCast( "string", "American Pie" ) />
  • <cfset qMovie[ "date_watched" ][ 2 ] = JavaCast( "float", "2005/08/02" ) />
  • <cfset qMovie[ "rating" ][ 2 ] = JavaCast( "float", 9.0 ) />
  • <cfset qMovie[ "has_fighting" ][ 2 ] = JavaCast( "boolean", false ) />
  • <cfset qMovie[ "has_boobies" ][ 2 ] = JavaCast( "boolean", true ) />
  •  
  • <cfset qMovie[ "id" ][ 3 ] = JavaCast( "int", 3 ) />
  • <cfset qMovie[ "name" ][ 3 ] = JavaCast( "string", "Friends With Money" ) />
  • <cfset qMovie[ "date_watched" ][ 3 ] = JavaCast( "float", "2006/06/21" ) />
  • <cfset qMovie[ "rating" ][ 3 ] = JavaCast( "float", 8.0 ) />
  • <cfset qMovie[ "has_fighting" ][ 3 ] = JavaCast( "boolean", false ) />
  • <cfset qMovie[ "has_boobies" ][ 3 ] = JavaCast( "boolean", false ) />
  •  
  • <cfset qMovie[ "id" ][ 4 ] = JavaCast( "int", 4 ) />
  • <cfset qMovie[ "name" ][ 4 ] = JavaCast( "string", "Better Than Chocolate" ) />
  • <cfset qMovie[ "date_watched" ][ 4 ] = JavaCast( "float", "2006/10/07" ) />
  • <cfset qMovie[ "rating" ][ 4 ] = JavaCast( "float", 8.5 ) />
  • <cfset qMovie[ "has_fighting" ][ 4 ] = JavaCast( "boolean", true ) />
  • <cfset qMovie[ "has_boobies" ][ 4 ] = JavaCast( "boolean", true ) />
  •  
  • <cfset qMovie[ "id" ][ 5 ] = JavaCast( "int", 5 ) />
  • <cfset qMovie[ "name" ][ 5 ] = JavaCast( "string", "Real Genius" ) />
  • <cfset qMovie[ "date_watched" ][ 5 ] = JavaCast( "float", "2006/12/12" ) />
  • <cfset qMovie[ "rating" ][ 5 ] = JavaCast( "float", 9.0 ) />
  • <cfset qMovie[ "has_fighting" ][ 5 ] = JavaCast( "boolean", false ) />
  • <cfset qMovie[ "has_boobies" ][ 5 ] = JavaCast( "boolean", false ) />

Now that we have a our query, let's create a Microsoft XML Excel document with a header row followed by data rows:

  • <!---
  • Store the XML Excel data. We are storing it first so that
  • we can clean up the data afterwards and then stream it as
  • a binary object to the browser.
  • --->
  • <cfsavecontent variable="strXmlData">
  • <cfoutput>
  •  
  • <!---
  • Define this document as both an XML doucment and a
  • Microsoft Excel document.
  • --->
  • <?xml version="1.0"?>
  • <?mso-application progid="Excel.Sheet"?>
  •  
  • <!---
  • This is the Workbook root element. This element
  • stores characteristics and properties of the
  • workbook, such as the namespaces used in
  • SpreadsheetML.
  • --->
  • <Workbook
  • xmlns="urn:schemas-microsoft-com:office:spreadsheet"
  • xmlns:o="urn:schemas-microsoft-com:office:office"
  • xmlns:x="urn:schemas-microsoft-com:office:excel"
  • xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
  • xmlns:html="http://www.w3.org/TR/REC-html40">
  •  
  • <!---
  • The first child element of the WorkBook element
  • is DocumentProperties. Office documents store
  • metadata related to the document—for example,
  • the author name, company, creation date, and
  • more in the DocumentProperties element.
  • --->
  • <DocumentProperties
  • xmlns="urn:schemas-microsoft-com:office:office">
  • <Author>Ben Nadel</Author>
  • <Company>Kinky Solutions</Company>
  • </DocumentProperties>
  •  
  • <!---
  • The Styles node represents information related
  • to individual styles that can be used to format
  • components of the workbook.
  • --->
  • <Styles>
  •  
  • <!--- Basic format used by all cells. --->
  • <Style ss:ID="Default" ss:Name="Normal">
  • <Alignment ss:Vertical="Top"/>
  • <Borders/>
  • <Font/>
  • <Interior/>
  • <NumberFormat/>
  • <Protection/>
  • </Style>
  •  
  • <!---
  • This is the movie rating style. We are going to
  • format the number so that it goes to one
  • decimal place.
  • --->
  • <Style ss:ID="Rating">
  • <NumberFormat ss:Format="0.0" />
  • </Style>
  •  
  • <!---
  • This is the date of the movie viewing. It is
  • going to be a short date in the format of
  • d-mmm-yyyy (ex. 15-Mar-2007).
  • --->
  • <Style ss:ID="ShortDate">
  • <NumberFormat ss:Format="[ENG][$-409]d\-mmm\-yyyy;@" />
  • </Style>
  •  
  • <!---
  • This is the boolean format of the has
  • fighting and has boobies columns. We are
  • going to display these values in Yes /
  • No format.
  • --->
  • <Style ss:ID="YesNo">
  • <NumberFormat ss:Format="Yes/No" />
  • </Style>
  •  
  • </Styles>
  •  
  •  
  • <!---
  • This defines the first worksheeet and it's name.
  • We are only using one worksheet in this example,
  • but you could add more Worksheet nodes after
  • this one for multiple tabs. The "Name" attribute
  • here is the name that shows up in the tab.
  • --->
  • <Worksheet ss:Name="Movie Data">
  •  
  • <Table
  • <!---
  • We need a column for each column of the
  • query. This attribute is required to be
  • correct. If the value here does NOT
  • match the data in Excel file, the
  • document will not render properly.
  • --->
  • ss:ExpandedColumnCount="#ListLen( qMovie.ColumnList )#"
  •  
  • <!---
  • We need a row for every query record
  • plus one for the header row. Again, if
  • this value does not match what is in the
  • document, the excel file will not
  • render properly.
  • --->
  • ss:ExpandedRowCount="#(qMovie.RecordCount + 1)#"
  •  
  • x:FullColumns="1"
  • x:FullRows="1">
  •  
  • <!---
  • Here, we can define general properties
  • regarding each column in the data output.
  • --->
  • <Column ss:Index="1" ss:Width="30" />
  • <Column ss:Index="2" ss:Width="100" />
  • <Column ss:Index="3" ss:Width="42" />
  • <Column ss:Index="4" ss:Width="84" />
  • <Column ss:Index="5" ss:Width="66" />
  • <Column ss:Index="6" ss:Width="70" />
  •  
  • <!---
  • This is our header row. All cells in the
  • header row will be of type string.
  • --->
  • <Row>
  • <Cell>
  • <Data ss:Type="String">ID</Data>
  • </Cell>
  • <Cell>
  • <Data ss:Type="String">Name</Data>
  • </Cell>
  • <Cell>
  • <Data ss:Type="String">Rating</Data>
  • </Cell>
  • <Cell>
  • <Data ss:Type="String">Date Watched</Data>
  • </Cell>
  • <Cell>
  • <Data ss:Type="String">Has Fighting</Data>
  • </Cell>
  • <Cell>
  • <Data ss:Type="String">Has Boobies</Data>
  • </Cell>
  • </Row>
  •  
  • <!--- Loop over the query. --->
  • <cfloop query="qMovie">
  •  
  • <!---
  • When we output the excel XML row / cell
  • data, we can put the format the tabbing
  • / returning of the Cell and data cells
  • in relation to each other; however, we
  • cannot freely move around the values
  • within the Data cells as it may change
  • the ability to convert the data type.
  • For instance, we cannot put any white
  • space in front of a numeric value or it
  • will not be parsed as a number and will
  • error out.
  • --->
  • <Row>
  • <Cell>
  • <Data ss:Type="Number">#qMovie.id#</Data>
  • </Cell>
  • <Cell>
  • <Data ss:Type="String">#qMovie.name#</Data>
  • </Cell>
  • <Cell ss:StyleID="Rating">
  • <Data ss:Type="Number">#qMovie.rating#</Data>
  • </Cell>
  • <Cell ss:StyleID="ShortDate">
  • <Data ss:Type="DateTime">#DateFormat( qMovie.date_watched, "yyyy-mm-dd" )#T#TimeFormat( qMovie.date_watched, "HH:mm:ss.l" )#</Data>
  • </Cell>
  • <Cell ss:StyleID="YesNo">
  • <Data ss:Type="Number">#qMovie.has_fighting#</Data>
  • </Cell>
  • <Cell ss:StyleID="YesNo">
  • <Data ss:Type="Number">#qMovie.has_boobies#</Data>
  • </Cell>
  • </Row>
  •  
  • </cfloop>
  •  
  • </Table>
  •  
  • </Worksheet>
  •  
  • </Workbook>
  •  
  • </cfoutput>
  • </cfsavecontent>
  •  
  •  
  • <!---
  • Define the way in which the browser should interpret
  • the content that we are about to stream.
  • --->
  • <cfheader
  • name="content-disposition"
  • value="attachment; filename=basic.xml"
  • />
  •  
  • <!---
  • When streaming the Excel XML data, trim the data and
  • replace all the inter-tag white space. No need to stream
  • any more content than we have to.
  • --->
  • <cfcontent
  • type="application/msexcel"
  • variable="#ToBinary( ToBase64( strXmlData.Trim().ReplaceAll( '>\s+', '>' ).ReplaceAll( '\s+<', '<' ) ) )#"
  • />

As you can see, the Excel data is defined in a TABLE XML node. The nodes within the Table element are fairly self-explanatory. The Data element defines what data type Excel will use to interpret the data. Each cell can take a formatting option (a CSS Style defined in the Styles nodes) to help format the data. Notice that we are taking our Rating cell to a single decimal place and that our boolean columns are being output in Yes / No format. These formatting options are all defined by the Styles.

Then, once I have the data stored up, I trim it and remove the white space between tags. Then, I convert it to binary and stream via the CFContent's Variable attribute (the variable attribute requires binary, hence the conversion). The use of the variable attribute automatically resets the content buffer so don't worry about any previous white space that was created. Notice also that I am telling the browser to use the downloaded document as if it were a Microsoft Excel document by setting the CFContent type to "application/msexcel".

Now, granted, this is a bit more involved than the HTML version, but as you can see, once you understand the layout, it's not very complicated. What IS complicated is debugging. If you do get something wrong, the document just doesn't render. It says that it dumps information into a Log, but I cannot find the Log at the location which it reports to have used. Oh well.

In the next demo, I will try to add some more cell formatting and make use of some cell formulas.



Reader Comments

Ben,

that's nice. Thanks! I've done a similar thing with C#. The one thing to note when using XML to generate Excel sheets is that the user has to have at least Office/Excel 2003 to be able to read the Excel XML files. Apparently previous versions of office did not support XML Excel sheets.

Reply to this Comment

Boyan,

That is true, although I want to say there is a version of Excel from 2002 that will support this (but I might just be making that up). I figure though, that it has been 4 years... time to upgrade if you haven't already. And honestly, if you are doing this for a client, most clients are either willing to upgrade to a new Office (if that is required for the project) or already have it due to the corporate environment. But then again, I have pretty flexible clients most of the time, so that might not be a good sample of the population.

Also, I am not sure how this works in Open Office.

What I do know is that ColdFusion can read in this Microsoft Excel data using XmlParse() and convert it to an internal XML document object. That is way cool and is perhaps a huge selling point over the HTML table version.

Reply to this Comment

OpenOffice uses the Open Document Format as it's native document format. OASIS has recently adopted it as an XML standard for representing office-style documents, and rumor has it that the next version of Microsoft Office will include support for Open Document, there is already a Microsoft-sponsored effort to build a ODF->OfficeXML translator.

I haven't tried it yet, but there's a bunch of XSL files at their sourceforge site that might be possible to use with ColdFusion to translate ODF -> OfficeXML and back again.

Open Doc Format: http://www.oasis-open.org/committees/tc_home.php?wg_abbrev=office#technical

ODF Translator: http://sourceforge.net/projects/odf-converter/

Reply to this Comment

In a previous gig, we had very picky requirements for some financial reports for a government agency in the UK (alas, I was never sent over there like so many of my coworkers). The formatting, tabs, and formulas were a pain in the butt to create dynamically, so I created the files in Excel, saved them as XML files, and used a ColdFusion script to parse and replaced placeholders in the files with transaction data from the database. The results were saved in another directory, and voila! A perfectly formatted report with all worksheets, formatting, and formulas intact.

While minor tweaks to the excel template could be made without getting IT involved, the big drawback was scalability. As the reports became larger and larger (certain sections repeated and/or grouped, depending on how many financial transactions were involved), it became a very unwieldy process. In hindsight, it was not a good solution to that particular problem, but it might be useful elsewhere. Live and learn.

Reply to this Comment

I am hoping you will help me out. I am trying to get this to work in 6.1 and I can not seem to figure it out. since I don't have the variable attribute of the cfcontent tag; I am trying to find a work around. I have been playing with variations of something like this:

<cfset the_file = ToBinary(ToBase64(strXmlData.Trim().ReplaceAll( '>\s+', '>' ).ReplaceAll( '\s+<', '<' ) ) ) />

<cffile action="write" file="#expandPath('.')#/basic.xml" output="#the_file#" />

<cfheader name="content-disposition" value="attachment; filename=basic.xml" />

<cfcontent type="application/vnd.ms-excel" file="#expandPath('basic.xml')#" />

for the output, but I am only getting text in IE and and an Excel error in FF on 'Table"

Could you steer me in the right direction?

Thank you

Reply to this Comment

Ron,

You are very close. Since you are writing to a text file (XML file) you don't need to convert to binary. I only had to do that because the Variable attribute only accepts binary. For your thing, you can put the line:

strXmlData.Trim().ReplaceAll( '>\s+', '>' ).ReplaceAll( '\s+<', '<' )

directly into the output of the CFFile tag:

<cffile action="WRITE"
output="#strXmlData.Trim().ReplaceAll( '>\s+', '>' ).ReplaceAll( '\s+<', '<' )#" ...... />

Give that a go and it should work fine. Let me know if you have any other problems.

Reply to this Comment

Jeremy,

It sounds like you were creating very complicated documents. I am not sure, though, how things would not scale well. Granted I just found out about this XML stuff, but it seems that as long as you have set formats for the documents, scaling (ie. number of records) wouldn't be an issue?

Reply to this Comment

Yes, they were fairly complicated documents. The scaling issues came into play with the repeatable sections of the spreadsheets. There were placeholder markers for the beginning and end of the repeatable sections. Within these sections were placeholders for the data from the database. How many times these sections were repeated depended on the number and types of transactions. A few thousand transactions a day was fine, but once we got into a few hundred thousand per day, things got a little slow. I later went back and optimized the code significantly, which helped a bit. Granted, with the sheer volume of data being processed daily, I have a feeling that generating those spreadsheets would have been a bear any way you cut it.

Reply to this Comment

Most definitely a bear... and when the Row Count and the Column Count have to match up exactly, I am sure it was a nightmare dealing with dynamic / repeatable content areas. Sounds horrible to me :)

For simple documents, though, I am gonna give it a shot and see where it leads me.

Thanks for all of your feedback!

Reply to this Comment

Ben-
My coworker Matt has been using this XML method to create some pretty sick looking Excel workbooks. I mentioned that you couldn't find the error log file and he said "oh, it's here:"

C:\Documents and Settings\{username}\Local Settings\Temporary Internet Files\Content.MSO

Just sub in your username and that'll do

Reply to this Comment

Steve,

I appreciate that, but that's the same thing my Excel tells me ... and I still can't locate it :(

OH CRAP! It's a hidden folder. I just found it. Thanks! Stupid windows trying to protect me from myself :) Thanks!

Hey, the debugging information in this ain't half bad. Sweet.

Reply to this Comment

Any idea on how to add an image to a cell w/ schemaML? Attempted to add an image using the following html namespace: xmlns:html="http://www.w3.org/TR/REC-html40
(image source pointed to an absolute path).

The xml generated by my program looks something like this:

<Cell>
<Data ss:Type="String">
this is a test
<html:Img Src="https://dev-server/test/icon.gif"/>
</Data>
</Cell>

The excel file doesn't produce any errors and opens up successfully. However that line referring to the image is completely ignored by the excel file (renders a blank cell or in this case only the test string). Just to test other html tags in html namespace, applied the html font tag to change the font style in a cell and it rendered that properly to the excel file.

(Also tried converting the image to a binary object but not sure how to write it so it would be recognized by the excel file ...)

Any ideas?

Reply to this Comment

Mo,

I have not tried this myself. You should create an XLS and put in am image. Then save that file (via Excel) as an XML document and open it up. My guess is that the graphic is stored as some sort of Base64 encoding (looks like about a 100 lines of random letters).

Reply to this Comment

It should be noted that this will not work unless you are using Excel 2002 or later. That is when Microsoft added XML support to Excel. While on the face it sounds irrelevant, consider that most Federal Government agencies are still using Windows 2000 and Office 2000 on a wide-scale basis. That's the struggle I had to overcome after being initially thrilled about the prospects.

All that being said, this has a ton of potential. One of the pitfalls of using HTML tables to create an Excel file is that the resulting Excel file usually looks plain, if not trashy. There also is no support for multiple tabs and Excel formulas. This XML-based approach changes all that. It allows us now to have the client design a spreadsheet exactly how they want it, look and feel, formulas, etc. and with a quick Save As, we have the template to build our dynamic Excel file from. A couple of hours of XML cleanup and proper placement of variable placeholders and we can give them exactly what they want, with no compromises.

Thanks for this tip!

Reply to this Comment

I don't know if Mo has resolved his issue with embedding images in the XML file. I have the same problem. I tried inserting an image to an Excel file, saved it as .xml and checked to see what the resulting XML code is, but there is no indication about the image.

I like the XML schema for Excel coz it allows you to have much of the functionality of Excel, however, I've been stumped by my inability to embed images to the generated Excel file.

Reply to this Comment

@AC,

The image might be stored in base64, which would look like a few thousand characters in a row (usually broken onto a new line every X number of characters)... see anything like that in the XML?

Reply to this Comment

Ben,
Great Snipplets man. But I've converted the QueryNew( line to an actualy cfquery, wrapped the <cfset for the row data in a cfoutput, cfloop to set all the parameters, and esentially that is all that has changed,
and I get the following Error when I try to open the xls in excel:

Table.

I opened the error log and it displays this:
XML ERROR in Table
REASON: Bad Value
FILE: C:\DOCUME~1\jharvey\LOCALS~1\Temp\twc_basic.xml-1.XLS
GROUP: Cell
TAG: Data
VALUE: TXML PARSE ERROR: Missing end-tag
Error occurs at or below this element stack:
<ss:Workbook>
<ss:Worksheet>
<ss:Table>
<ss:Row>
<ss:Cell>
<ss:Data>
<ss:kkg>

Did I do something wrong?

Reply to this Comment

Just thought that before you asked here's my code:

<cfquery name="quiz_results" datasource="#datasrc#">
select id, user_name, timestamp, q1, q2, q3 from webos_bmi_quiz
</cfquery>

<cfset add_row ='#quiz_results.recordcount#'>
<!--- Add rows to query. --->
<cfset QueryAddRow( quiz_results, #quiz_results.recordcount# ) />

<!--- Set row data. --->
<cfoutput>
<cfloop query="quiz_results">
<cfset quiz_results[ "id" ][ 1 ] = JavaCast( "string", "#id#" ) />
<cfset quiz_results[ "user_name" ][ 1 ] = JavaCast( "string", "#user_name#" ) />
<cfset quiz_results[ "timestamp" ][ 1 ] = JavaCast( "string", "#timestamp#" ) />
<cfset quiz_results[ "q1" ][ 1 ] = JavaCast( "string", "#q1#" ) />
<cfset quiz_results[ "q2" ][ 1 ] = JavaCast( "string", "#q2#" ) />
<cfset quiz_results[ "q3" ][ 1 ] = JavaCast( "string", "#q3#" ) />
</cfloop>
</cfoutput>

<Worksheet ss:Name="Quiz Results">
<Table ss:ExpandedColumnCount="#ListLen( quiz_results.ColumnList )#" ss:ExpandedRowCount="#(quiz_results.RecordCount + 1)#" x:FullColumns="1" x:FullRows="1">
<Column ss:Index="1" ss:Width="30" />
<Column ss:Index="2" ss:Width="100" />
<Column ss:Index="3" ss:Width="42" />
<Column ss:Index="4" ss:Width="84" />
<Column ss:Index="5" ss:Width="66" />
<Column ss:Index="6" ss:Width="70" />
<Row>
<Cell>
<Data ss:Type="String">ID</Data>
</Cell>
<Cell>
<Data ss:Type="String">User Name</Data>
</Cell>
<Cell>
<Data ss:Type="String">Date Taken</Data>
</Cell>
<Cell>
<Data ss:Type="String">Q-1 Results</Data>
</Cell>
<Cell>
<Data ss:Type="String">Q-2 Results</Data>
</Cell>
<Cell>
<Data ss:Type="String">Q-3 Results</Data>
</Cell>
</Row>
<cfloop query="quiz_results">
<Row>
<Cell>
<Data ss:Type="Number">#quiz_results.id#</Data>
</Cell>
<Cell>
<Data ss:Type="String">#quiz_results.user_name#</Data>
</Cell>
<Cell ss:StyleID="ShortDate">
<Data ss:Type="DateTime">#DateFormat( quiz_results.timestamp, "yyyy-mm-dd" )#T#TimeFormat( quiz_results.timestamp, "HH:mm:ss.l" )#</Data>
</Cell>
<Cell>
<Data ss:Type="String">#quiz_results.q1#</Data>
</Cell>
<Cell>
<Data ss:Type="String">#quiz_results.q2#</Data>
</Cell>
<Cell>
<Data ss:Type="String">#quiz_results.q3#</Data>
</Cell>

</Row>

</cfloop>

</Table>
</Worksheet>

</Workbook>

</cfoutput>
</cfsavecontent>

Reply to this Comment

Nevermind,
One of the Usernames inputted in the database had < & > in it (apparently it was inputted before I added my stripHTML command on the entry form.

Sorry for any issues, or hours of scratching one's head.

-James

Reply to this Comment

I have tried to implement this but instead of a query I am using a stored procedure. But everytime I try to ope the xml.xls files I am getting an error worksheet seetings. I am not sure what is wrong with the setting. Can any one please help.

Thanks!

Reply to this Comment

I commented out your qMovie query and data set up and added:
<cfquery name="qMovie" datasource="admi-prod">
select dealer_id id, dealership_name name, termination_date date_watched, primary_manufacturer_id rating, 0 has_fighting, 1 has_boobies
from dealer_master
where dealership_name like '%st%'
and rownum <= 5
</cfquery>
and now when I run the page I just get the query data dumped. Just one long string of text. Why the difference?

Reply to this Comment

@Kate,

Is it asking your to save the XML document? Or is it just display it? There might be an error in the XML that is being generated? You might want to wrap your individual field output in XmlFormat() method calls?

Reply to this Comment

Hi, thanks for the article. I'm using Excel 2007 and when I try giving the file an xml extension, it automatically appends .xls (so it becomes basic.xml.XLS) This causes Excel to display the message "the file you are trying to open is in a different format than specified by the file extension." It still works but the message is annoying. Is there any way to prevent this?

Reply to this Comment

@Candice,

Are you talking about generating the XML document programmatically using ColdFusion? Or are you talking about saving a document from within Excel?

Reply to this Comment

"It says that it dumps information into a Log, but I cannot find the Log at the location which it reports to have used. Oh well."

From my experience using FireFox, you can paste the path given to you by the error message into the address bar of your browser and it will display the log file as text.

Common issues are improperly formatted cells and not accounting for the expanded row count correctly.

Keep up the great work Ben, I really appreciate your site and your insight.

Reply to this Comment

I'm talking about generating the XML document programmatically using ColdFusion. It happens even when using the same code in the example.

Reply to this Comment

When I create a test.cfm and use the sample code I just get the basic.xml file. I am using excel 2007 as well.

Excel will always give you that notification if the file being opened is not a true xls. The only way that I have found around this was using VB.NET, and even then the solution was not perfect.

Reply to this Comment

Hey all,
this may be what I need to use, but I am not totally sure. So here is my issue:
My supervisor (not CF smart at all), does not like the html based report I am generating. In HTML if the page is more than one page, if I try to print it, the first page is blank and data is missing. So, I try to use cf to export to excel. Here is his issue, the exported file does not work like excel. He actually has to hit print and set up the print features to print to landscape and fit to page. Will this concept above help me solve this issue so I can get him off my back...wait that will still cause him to be a pain, but at least I can solve this problem, and learn something new along the way?

thanks
Dan

Reply to this Comment

@Candice,

Did you ever find a work around to the "different format specified by the file extension" warning?

Reply to this Comment

Nice easy to follow tutorial Ben - cheers.

I have a question though - I am trying to out put data for specific members using tabs so the above example works well for me. My problem is that I actually want to include 2 tables of data in the one worksheet for each member.

Basically the first table will all be member details and the second table would specific of that members account shown. I tried the 2 table approach but quickly learned that didn't work and I am going to plug away and trial and error some kinda solution if possible.

If anyone has any input on this it would be much appreciated.

Cheers.

Reply to this Comment

@Neil,

Try creating your desired document in Excel and then exporting as XML to see what it creates - that's basically what I did.

Reply to this Comment

Cheers for the pointer Ben - I just did some creating of the doc in Excel and then viewed the source with notepad and was able to extract what I needed.

Cheers.

Reply to this Comment

@Mike,

No I never did find a workaround.

Most users have figured out that they can just click 'Yes' but it's still annoying.

Reply to this Comment

'Real Genius' doesn't have boobies? I could have sworn it did! Oh well... time to take it off my Netflix list.

Reply to this Comment

First of all, excellent blog post and comments...

i use C# to make XML Excel files when comes to reports with dynamic columns... got through the same issues you are commenting, Where's the log?, How many rows and cols are? and finally making the design usefull to the user: If there were too many columns break to another page or if it doesn't fit on a A4 paper then add some new page in the same worksheet, etc.

But, the one thing i need, actually, is the image... got ideas?

if i can't walk through this last problem... i will have to change to another format... and to be honest, xml for Excel has been a real challenge yes, but i "luv" the results :)

Regards

Reply to this Comment

@Jorge,

You'd have to create an XLS file and the export as XML and see what it does. Not sure if it would embed it via Base64 or if it would actually save the image externally and then link to it. If its embedded base64, then you are in a good place. If it's externally linked, then you're gonna have packaging problems :(

Reply to this Comment

Thanks Ben, actually, i thought that before, but i don't see eny of it inside the xml file.

i have tried to insert and link the image (which is an option of the image insert dialog) but without luck...

i have been saving the xls as xml and then when i open the xml file it doesn't show the image no matter what image format i use... anyway, i'll report back to you if i find something :)

Reply to this Comment

Wow!, looks like MS-Word has some pretty complex options for design... i will merge this with an excel table to get both features...

for now, the problem of the image is resolved with word saved as xml.

Best wishes

Reply to this Comment

@Jorge,

Yeah, MS has some CRAZY document formatting internally when you export. Show's you how nuts it is behind the scene. Good luck!

Reply to this Comment

Ben ,

This is indeed helpful.
But I am looking for including images in Excel.
I opened the XML file in Excel and embed a image in it.Then tried to save it as XML Spreadsheet , It gives me warning that Autoshapes, charts etc will not be saved in this format.

Can you suggest some workaround to achieve this.

Reply to this Comment

@Aradhana,

If saving an Excel document as XML discards images / shapes, I am not sure that you can go about it in this approach.

Reply to this Comment

Hi Ben,

I need to implement the export to excel functionality.

I have multiple rows in a screen....Select any one row and then export to excel, then a certain file is exported to excel.This is simple as I did it.

But now I need to impelemnt for multiple files.........I mean the user can select multiple rows and then hit the excel button...........Then he should be able to downlaod multiple excel reprots in one go...........

Please Ben help.............

Rgds,

Abhi

Reply to this Comment

You can select the option of repeating row headers in PageSetup and save the file as xml.

Open the xml in any editor and see what you need to write in xml to achieve this.

Reply to this Comment

I want to repeat rows at top on each page through code and not manually. On creating the excel file the option should be enabled

Reply to this Comment

@Adith,

@Aradhana is correct - you need to implement it in a standard XLS file and then export it to see how it is done in XML. I don't know this off hand.

@Vicky,

I would suggest using CFZip (if you are CF8+) and zip the files together before streaming them to the client. Are you on CF8 or CF9?

Reply to this Comment

@Ben, I skimmed through the comments, not sure if anyone has mentioned this or not, but I took your example and made a change on line 297 which was to change the basic.xml to basic.xsl to see if it would open it and it worked fine. So if you were creating an excel file and wanted it available as whatever.xls, it's totally doable. I'm using Office 2K3, so it worked with, not sure about other versions. This could fix the issue with Offic 2K7 mentioned above about the appending of the .xls to the file like basic.xml.xls mentioned above.

Reply to this Comment

I see that the XML is being displayed in the browser directly rather than getting the Open/Saev Dialog...

Can it be because of any of the following reasons viz.,

#1. File Size is huge
#2. Connection speed

-Naveen

Reply to this Comment

@Hatem,

Good idea! Yeah, as long as the data in the file is "good" for the given application, I think tweaking the file extension might just work.

@Naveen,

I have not seen that happen; but it's been a while since I've played around with this.

Reply to this Comment

Can we append/add sheet in Excel document?

I have a big data to export in excel and CFsaveContent is limited by its memory.

Reply to this Comment

Meenakshi -
look in to using the the java stringbuffer classes to generate the file much more efficiently. i ran into memory limits with cfsavecontent too.

either that or upgrade to ColdFusion 9 and enjoy a much, much simpler way to manipulate spreadsheets!

Reply to this Comment

@Meenakshi,

@Steve is right - you can look into creating a string buffer; also, you would write the file to disk a bit at a time, appending to the file after X records are written.

Reply to this Comment

@Ben, @Steve,

Could this "String Buffer" (sounds like something from Star Trek) be used when reading an extra large excel file from the disc say using the POI utility? Or can you only do it when writing something to the disc?

The reason I ask, is that I've been trying to use the POI utility to read some exceptionally large excel files but the server crashes every time i try and read a file greater than 15-20mb.

Reply to this Comment

@steve,
thanks... I'll see if I can find some introductory information on BufferedReader. Do you happen to have any recommended resources I should check out... possibly places that have good sample code?

Reply to this Comment

Your example about creating Excel Documents with Coldfusion and XML addressed almost exactly what I need. However, I have two questions: how do I enter multi-line content in the Excel cells, without Excel making multiple rows. Secondly, can a spreadsheet be created with automatically-determined column widths? (Your example specified each one.)

Reply to this Comment

When Excel cant open the XML file, it does write the error out to a log. I ran ProcessMonitor and they are written out to: C:\Documents and Settings\<MyName>\Local Settings\Temporary Internet Files\Content.MSO\<DifferentNumberEachTime>.log

Reply to this Comment

@Jeremy,

Good call! Does it give information as to what went wrong, such as the line number or anything?

Reply to this Comment

I am trying to save the excel file instead of streaming it to the browser. The file gets created fine but when I open it, it says that it is locked for editing by 'another user.' Then it gives me the options to open it Read-Only or be Notified when the document is no longer in use. Does anyone know why this is happening and how to fix it?

Reply to this Comment

^ Nevermind that question. I figured out it has nothing to do with the way the file is created. It's caused by the program I'm using to get the files.

Reply to this Comment

@Ben,
Thanks for the tip regarding your XML article and using BufferedInputStream instead of FileInputStream. I wish excel files were written in plain text just like your XML example. Unfortunately I think they need some type of decoding in order to view the XML(ish) structure.

I decided to try and modify your POI utility to see if I could get it to use BufferedInputStream instead of FileInputStream. I modified line 871 of POIUtility.cfc to read:
LOCAL.FileInputStream = CreateObject( "java", "java.io.BufferedInputStream").Init(CreateObject("java","java.io.FileInputStream").Init(JavaCast("string",ARGUMENTS.FilePath)));

However, I think that the ExcelFileSystem object cannot handle a buffered input stream. Does the apache.poi have some way of buffering the excel input stream?

Reply to this Comment

@Candice,

No problem - glad you got it figured out.

@David,

To view the XLS document as an XML file, if I remember correctly, I actually opened up the document in Excel and then went "File > Save As > XML". So, there was not an encoding issue, but rather a completely separate file format.

As far as the buffered input stream with POI, I am not sure that it would make a difference; I believe POI reads in the entire file before it does anything with it. As such, I am not sure that the buffering nature would have a performance difference.

Reply to this Comment

@Ben,
Thanks for the tip. It sounds like because xlsx files aren't in plain text, the buffered reader wouldn't be able to look for any patterns anyway. XML could be the answer to my problem or possibly even a CSV file. I'll let you know how it goes. :)

Reply to this Comment

@David,

Gotcha. I never activated my 2007 Office suite; I tend to use to use 2003 (I'm old-school that way), so when it comes to ...X documents, I'm just shooting in the dark :)

Reply to this Comment

@Ben,

Thanks for providing these techniques. I'm creating some pretty cool spreadsheets from CF now thanks to you.

As to the discussion of graphic objects not being honored by XML, I've had the same experience. Excel 2007's Help says "Chart and other graphic objects" are not retained.

Does anyone have any ideas on how to create Excel files that contain bar and pie charts and the like from CF?

Reply to this Comment

Thanks, Ben, this really helps!

I know it's all old stuff to you now, but it's still useful.

Reply to this Comment

@Mike,

Yeah, the image / graphic stuff is more complex. I believe that the latest version of POI JAR file supports embedded graphics. After I finish all my presentation stuff, I'll try and update my POI project to play around with some of these updated ideas.

@Ethan,

Heck yeah - always glad to help. I'm actually interested in re-visiting some of this XML-based document stuff in CF9's DOC-to-PDF translation. I wonder if Open Office (the service that does this conversion) will support XML style data formatting.

Reply to this Comment

Ben,

Thanks a lot for this fantastic writeup! I had already looked at XML output of a report I want to automate, and it (fortunately!) made a ton of sense. This helps make even better sense of it.

This is WAY easier than using the MSExcel .NET COM Interop or, worse, COM+ itself. It's also much better than exporting as a CSV and letting the users sort it out. :)

Reply to this Comment

@Carlos,

I agree - much easier than involving outside resources. The XML can get pretty intense sometimes; and when it breaks, it can be a super pain to track it down. But when it works, it's relatively straightforwrd.

Reply to this Comment

@Candice,

I'm guessing your issue is related to Excel 2010 (or 2007) in your case. I'm having the same issue and upon further research, it turns out that Microsoft put in this new feature that has to do with security. MS assumes that since this is not opened from within excel that it is a security violation and intentionally corrupts the file which is why you keep getting that message. Now you can go into your excel and change the settings to allow for content to be opened but that doesn't help your users (or mine). It's one extra click of nuisance.

I wonder if there is a workaround for this via a java lib or something....

Reply to this Comment

@roger v - Yes it's Excel 2010. Thanks for the explanation.

Does anyone know if you can apply more than one style to a cell? For example, I have a style called "header" that makes the text bold. Then I have a style called "border" which adds a border. Can I apply both to one cell without having to make a new style?

Reply to this Comment

@roger, @candice, (& Ben)

Regarding the Office 2007 message: The file you are trying to open .xlsx is in a different format than specified by the file extension. verify the file is not corrupted and is from trusted source before opening the file. Do you want to open the file now?
---

I found the following webpage (below) and tried the solution about the MIME types. Still popping up the message. Even more annoying, it seems to be pop-UNDERing the message now and shows my CFM as "spinning wheel of death" on IE7 leaving users, incl me, wondering, "What's taking so long?"

Thus, the Registry fix would probably work, but I don't have that control over the machines thus I'm going to try to open as a CSV or TXT to avoid the security warning.

---

http://www.itexperience.net/2008/03/17/excel-2007-error-different-format-than-specified-by-the-file-extension/

[Quote]
In most cases, the problem can be solved by defining MIME types and associating them with extensions.
When you're using a Document Management System (like Sharepoint or Livelink), you can try adding these:
.xls = application/msexcel
.xlsx = application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

However, if you don't want to look for a solution, but just want to solve the problem, insert this key in your registry to suppress the notification:

[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security]
"ExtensionHardening"=dword:00000000

[/QUOTE]

Reply to this Comment

Okay, so if you comment out your <cfcontent type="application/msexcel"> tag, you'll avoid the warning. However, the data displays in your web browser and not Excel requiring a user to cut/paste.

Changing the filename to .CSV does not suppress the prompt. In fact, it attaches XLS to the ending ("filename.csv.xls"). Dumb.

Reply to this Comment

I really wanted to use Ben's file, because he had done all the work, but had the same problem with the prompt and know this will confuse my users. The Apache POI api cured all the issues I had with the prompt. I little involved, but it works well.

http://www.mainebusinessworks.org/assessment
Click on Cash Flow and generate the spreadsheet. Still in development, but the spreadsheets works with no prompt or error.

More info here about Apache POI.
http://poi.apache.org/spreadsheet/quick-guide.html

Reply to this Comment

@Ben,
I actually found POI through your site and looked at your application. I hope to do more with POI and will take another look.

thanks.

great site!

Reply to this Comment

Hey Folks,

I actually got rid of the annoying message:

HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

string fileName = XXX + ".xml";

Reply to this Comment

Ben,

Love the site very helpful indeed. I was wondering if you could use the example in this post with MS Excel's import data from the web via a webservice that returns XML? So far I have been unsuccessful as it does not appear to be reading the schema information. This causes excel create it own schema which messes the entire format of the data. I able to do it with using a row element with attributes for the column name and there values for the cells. This has an issue with column ordering as XML orders the attributes A-Z.

Reply to this Comment

Hey Ben,
I am in a bit of a spot here and i hope you can bail me out.
I have this very simple document having a query tag and i want to export the results onto an excel file. Here is the code for it:

<cfsetting enablecfoutputonly="Yes">
<cfquery name="GetEmps" datasource="rupam">
SELECT * FROM employee
</cfquery>

<cfcontent type="application/vnd.ms-excel">
<cfheader name="Content-Disposition" value="attachment; filename=Employees.xls">

<cfoutput>
<table cols="4" border="2">
<cfloop query="GetEmps">
<tr>
<td>#Eid#</td>
<td>#Ename#</td>
<td>#Address#</td>
</tr>
</cfloop>
</table>
</cfoutput>

Everything is working fine. The excel file is being made properly. But, when i save the file onto my system and try to open it, it gives me a warning: The file you are trying to open,"Employees.xls", is in a different format than specified by the file extension.Verify that the file is from a trusted source before opening. And with that 3 choices of 'yes','no'and 'help'. On clicking 'yes' it opens and displays the result that i want. But i dont want that warning to appear. I have tried every method possible, even using the POI utility of yours. But still with the same result. I tried doing it via xml as you have shown here, but its not in the requirement.

Can you help me out here please.

Reply to this Comment

I'd like to thank you once again for this post. It helped a lot on a project I was on. I couldn't really use POI, I don't think, because from what I read about POI, a tabular direct display of the data was required...what I mean is what was displayed had to be columns straight down from the database, and there wasn't really much room for customization of what was displayed. What I was working with had that in that the excel cells were displaying data that wasn't necessarily following straight down from the database. Therefore, this particular method of exporting to excel was necessary. Thanks a heap!!!

Reply to this Comment

Ben,

Having issues trying to add a Title Row in my table. I wanted to use the ss:Span tag so it knows how many columns to span but no matter where I use this <table>, <column>, <row>, <data>. I doesn't work. Can you inform me of what/where the correct syntx would be to merge cells in XML format?

This is the last code I was using:
<Column ss:Span="14">
<Row ss:StyleID="title" ss:Height="20">
<Cell>
<Data ss:Type="String">Year To Date Detail Sheet (<cfoutput>#fy_begin# - #DateFormat(Now(), 'MM/DD/YYYY')#</cfoutput>)</Data>
</Cell>
</Row>
</Column>

Thanks in advance for any help you can provide!

Reply to this Comment

Hi Ben,

I have designed a screen from where user can download spreadsheet which is generated using xml code in .cfm file. User click on generate file. Instead of save that file, user open it. After that user trying to 'Save As' that file and pop-up window appears to save the file. In that window the File name box is empty and File type is xml. Which is not happening when user save the file directly after generating it. My requirement is to get the file name in the File Name box and file type should be .xls.
Thanks in Advance.

Regards,
Ravi

Reply to this Comment

@Ravi,

Did you give it a file name like below?
<cfheader name="Content-Disposition" value="attachment; filename=whateverfilenameyouwant.xls">

Reply to this Comment

@S,

Thanks for your quick response.
I did the same, as you mentioned.
Actually when user directly save that file then there is no problem with file name and extension. But when user click on open and then save as that, the file name left blank and file type default set to .xml.
It seems to me that generating excel using xml code is creating problem.
Please suggest.

Reply to this Comment

@Randall,

Yes, I'm setting the mime type. But i'm using vnd.ms-excel instead of application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.
The mime type, you mentioned, is for xlsx but i want to generate xls file.
<CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=FILE_NAME.xls">
<CFCONTENT TYPE="application/vnd.ms-excel">

I want to mention it again that User is facing problem only when user generate the file and instead of saving it, user open that file and after that trying to save it using 'Save As'.

Reply to this Comment

Ben,

For starters... I LOVE this technique, and I have been using it successfully for years. I have produced many beautiful multi-sheet workbooks with rich formatting.

I know this is an old thread, but I was curious to know if you (or anyone) had encountered this new twist (I say "new" because this wouldn't have been a consideration back when this thread was first written).

Recently, I had a project that involved creating just such an Excel file and attaching it to an email.

No problem! It worked for everyone, UNTIL users began wanting to open these "Excel Files" on a mobile device like an iPhone or iPad. While iOS devices CAN open Excel files, they cannot (it seems) open XML files designed for Excel.

First I tried just renaming the file from Somefile.xml to Somefile.xls (both of these files open natively in Excel on both my Mac and PC. However neither will open on my iPhone.

I'm somewhat desperate for a tip on how to solve this.

Reply to this Comment

@scott,

Thanks for the tip Scott. I tested it out, and it worked fine.

Except in this case, I was hoping to avoid re-writing my whole app. I was hoping to leverage my previous success using XML. This alternate approach (which may be my only option) relies on building an excel file not too different (in concept) from using the Spreadsheet functions introduced in CF9.

I appreciate your tip, and I may end up going that route if need be. Still I'm hoping someone has had some experience with getting XML (created for Excel) to open on an iOS device.

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.