Building Excel Spreadsheets via XML 
By Kris Brixon

Creating Excel Spreadsheets from ColdFusion with advanced formatting possible and it does not take a lot of Java calls. Spreadsheets can be created via XML using the XML dialect called SpreadsheetML developed by Microsoft for Excel.

For an introduction to XML in ColdFusion please see:
    Easy XML creation in 2 minutes 
    by: Marcos Placoná

We are going to create the following spreadsheet with background coloring, font weight, borders and formulas. 




The easy way to create the XML is to build a sample Excel Spreadsheet in Excel and then save as XML. Then strip out the pieces that you do not need and leave one row for your query to loop. Then save the XML for Excel to read.


<!---
Query Sim: 
a custom tag for creating test queries.
http://www.halhelms.com/index.cfm?fuseaction=code.detail 
--->

<cfmodule template="QuerySim.cfm">
qrySales
SALESPERSON,QTR1,QTR2,QTR3,QTR4
Brixon, Kris|5.55|1.11|33.33|99.99
Dent, Arthur|4.44|2.22|44.44|88.88
Perfect, Ford|3.33|3.33|55.55|77.77
Trillian|2.23|4.44|66.66|66.66
Beeblebrox, Zaphod|1.11|5.55|77.77|55.55
</cfmodule>

<cfxml variable="xmlDataDump">
<?mso-application progid="Excel.Sheet"?>
<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">
    <Styles>
        <Style ss:ID="s25">
            <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font x:Family="Swiss" ss:Bold="1"/>
        </Style>
        <Style ss:ID="s27">
            <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
            <Borders>
                <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
            </Borders>
            <Font x:Family="Swiss" ss:Bold="1"/>
            <Interior ss:Color="#99CCFF" ss:Pattern="Solid"/>
        </Style>
        <Style ss:ID="s28">
            <Interior ss:Color="#99CCFF" ss:Pattern="Solid"/>
        </Style>
    </Styles>
    <Worksheet ss:Name="Sheet1">
        <Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="12" x:FullColumns="1" x:FullRows="1">
            <Column ss:Width="93"/>
            <Row>
                <Cell ss:StyleID="s25"><Data ss:Type="String">Salesperson</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">1Qtr</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">2Qtr</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">3Qtr</Data></Cell>
                <Cell ss:StyleID="s25"><Data ss:Type="String">4Qtr</Data></Cell>
                <Cell ss:StyleID="s27"><Data ss:Type="String">Year</Data></Cell>
           </Row>
           
<cfoutput>
            <cfloop query=
"qrySales">
            <Row>
                <Cell><Data ss:Type="String">#qrySales.SALESPERSON#</Data></Cell>
                <Cell><Data ss:Type="Number">#qrySales.QTR1#</Data></Cell>
                <Cell><Data ss:Type="Number">#qrySales.QTR2#</Data></Cell>
                <Cell><Data ss:Type="Number">#qrySales.QTR3#</Data></Cell>
                <Cell><Data ss:Type="Number">#qrySales.QTR4#</Data></Cell>
                <Cell ss:StyleID="s28" ss:Formula="=SUM(RC[-4]:RC[-1])"></Cell>
            </Row>
          
  </cfloop>
            </cfoutput>

        </Table>
    </Worksheet>
</Workbook>
</cfxml>

<cfset xml = ToString(xmlDataDump)>

<cffile action=
"write" nameconflict="overwrite" file="#getDirectoryFromPath(getTemplatePath())#ExcelDump.xls" output="#xml#">


Here is a play by play of what just happened:

<cfmodule template="QuerySim.cfm">
The first thing we need is data. I tend to use the QuerySim tag from Hal Helms for testing data. You can download the tag or create a table and query.
http://www.halhelms.com/index.cfm?fuseaction=code.detail 

<cfxml variable="xmlDataDump">
The cfxml tag is going to hold the XML and auto create the initial tag.

<?mso-application progid="Excel.Sheet"?>
The mso-application tag tells Windows which application should open this file.

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

SpreadsheetML can only have one Workbook tag and it sets all the name spaces for the XML document.

<Styles>?</Styles>
The Styles tag and sub-tags hold the formatting information for the spreadsheet. Think of them like CSS. 

<Worksheet ss:Name="Sheet1">
There can be multiple Worksheet tags and it holds the name of each sheet.

<Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="12" x:FullColumns="1" x:FullRows="1">
Here we are starting to get into the meat of the XML. The table tag starts the table.

<Column ss:Width="93"/>
The column tag sets some properties for each column. We only have one column tag since the first column is wider than default, but all other columns are default width so they are not listed.

<Row>
The row tag starts the row.

<Cell ss:StyleID="s25"><Data ss:Type="String">Salesperson</Data></Cell>
The Cell tag starts each cell.
The StyleID attribute associates a style with this field.
The Data tag holds the actual data for the cell.

<Cell ss:StyleID="s28" ss:Formula="=SUM(RC[-4]:RC[-1])"></Cell>
A Cell with a Formula does not need a Data tag since the data will be generated from the formula. Note, the XML formulas do not use the $A$1 notation, they use R1C1 notation.

<cfset xml = ToString(xmlDataDump)>
Dump the XML to a variable.

<cffile action="write" nameconflict="overwrite" file="#getDirectoryFromPath(getTemplatePath())#ExcelDump.xls" output="#xml#">
Write the XML out to a file for to use.

Tags (and associated sub-tags) stripped out of the Excel version:
<DocumentProperties>
<OfficeDocumentSettings>
<ExcelWorkbook>
<Style ss:ID="Default" ss:Name="Normal">
<WorksheetOptions>
<Worksheet ss:Name="Sheet2">
<Worksheet ss:Name="Sheet3">

There is no harm in leaving the above tags in the document and for some things the tags may have to stay, but for the example these were not needed and removing them made the XML easier to read.

Gotchas:
1. Referencing cells in formulas are not like Excel. 
2. Any reserved XML characters in strings must be escaped ?? ??<>&.
3. Referencing rows and cells can get very complicated when you start to span.
4. Make sure data types match the contents of the cell.

Conclusion:

Start simple and build the complexity to help find parsing errors since XML is very unforgiving and Excel will just error when it can not parse the file or hits something that it does not understand. Read the XML that Excel generates and stick close to their implementation. Download the XML Reference Schemas from Microsoft and find the help file ?Introduction to SpreadsheetML?, it is a great reference.

References:

Query Simulator: 
http://www.halhelms.com/index.cfm?fuseaction=code.detail 

Office 2003: XML Reference Schemas: http://www.microsoft.com/downloads/details.aspx?FamilyId=FE118952-3547-420A-A412-00A2662442D9&displaylang=en 

Easy XML creation in 2 minutes by: Marcos Placoná: 
http://tutorial97.easycfm.com/

Additional Documentation (XLS example, etc.)
http://www.easycfm.com/downloads/tutorial_429.zip

About This Tutorial
Author: Kris Brixon
Skill Level: Advanced 
 
 
 
Platforms Tested: CFMX7
Total Views: 159,032
Submission Date: January 24, 2006
Last Update Date: June 05, 2009
All Tutorials By This Autor: 4
Discuss This Tutorial
  • Using #XMLFormat(String)# it fixed a lot of random errors I was getting. Also make sure you keep the capitilization of Excel tags the same; else Excel cries

Advertisement

Sponsored By...
Mobile App Development (IOS, Android, Cordova, Phonegap, Objective-C, Java) - Austin, Texas Mobile Apps - Touch512, LLC.