Excel supports SpreadsheetML for both import and export, providing a complete pathway for information. You can open a SpreadsheetML document, make a few changes, and re-open it in Excel if you want. (This is the only way to edit the schemas for XML Maps, for instance.) Perhaps more importantly, though, you can generate SpreadsheetML documents from whatever data you happen to have, providing a relatively easy and automatable path for transforming raw information into Excel spreadsheets.
To demonstrate, the code in the example will transform the XML shown in Save to SpreadsheetML and Extracting Data of the previous tutorial back into Excel again using XSLT. The stylesheet in the example uses the original spreadsheet as a template, and will produce XML very much like the SpreadsheetML you saved from Excel originally. The example leaves out some formatting so that there's a visible difference.
This tutorial uses Excel features that are available only in Excel XP and Excel 2003 on Windows. Earlier versions of Excel do not support this, and neither do current or announced Macintosh versions of Excel.
Example. XSLT for converting the custom XML vocabulary back to SpreadsheetML
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:d="http://simonstl.com/ns/dinosaurs/"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
>
<xsl:output method="xml" omit-xml-declaration="no" indent="yes" encoding="US-
ASCII"/>
<xsl:template match="/">
<xsl:apply-templates select="d:dinosaurs" />
</xsl:template>
<xsl:template match="d:dinosaurs">
<xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:
processing-instruction>
<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">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>XYZ</Author>
<LastAuthor>XYZ</LastAuthor>
<Created>2003-12-03T15:48:38Z</Created>
<LastSaved>2003-12-03T15:57:46Z</LastSaved>
<Company>ABC & Inc</Company>
<Version>11.5606</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>6150</WindowHeight>
<WindowWidth>8475</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>30</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<NumberFormat ss:Format="mmm\-yy"/>
</Style>
<Style ss:ID="s22">
<NumberFormat ss:Format=""$"#,##0.00"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="{count(d:sale)+4}" x:
FullColumns="1"
x:FullRows="1">
<Column ss:AutoFitWidth="0" ss:Width="73.5"/>
<Column ss:AutoFitWidth="0" ss:Width="96.75"/>
<Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="56.25"/>
<Row>
<Cell><Data ss:Type="String">Sales for:</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="DateTime"><xsl:value-of select="d:
date"/></Data></Cell>
</Row>
<Row ss:Index="3">
<Cell><Data ss:Type="String">ID Number</Data></Cell>
<Cell><Data ss:Type="String">Critter</Data></Cell>
<Cell><Data ss:Type="String">Price</Data></Cell>
<Cell><Data ss:Type="String">Quantity</Data></Cell>
<Cell><Data ss:Type="String">Total</Data></Cell>
</Row>
<xsl:apply-templates select="d:sale" />
<Row>
<Cell ss:Index="4"><Data ss:Type="String">Total:</Data></Cell>
<Cell ss:StyleID="s22" ss:Formula="=SUM(R[-{count(d:sale)}]C:R[-1]C)">
<Data ss:Type="Number"></Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>12</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
</xsl:template>
<xsl:template match="d:sale">
<Row>
<Cell><Data ss:Type="Number"><xsl:value-of select="d:IDnum" /></Data>
<NamedCell ss:Name="ID"/></Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="d:critter" /></Data>
<NamedCell ss:Name="Critters"/></Cell>
<Cell ss:StyleID="s22"><Data ss:Type="Number"><xsl:value-of select="d:
price" /></Data><NamedCell
ss:Name="Price"/></Cell>
<Cell><Data ss:Type="Number"><xsl:value-of select="d:quantity" /></Data>
<NamedCell ss:Name="Quantity"/></Cell>
<Cell ss:StyleID="s22" ss:Formula="=RC[-2]*RC[-1]"><Data ss:Type="Number">
<xsl:value-of select="d:total" /></Data></Cell>
</Row>
</xsl:template>
<xsl:template match="d:date" />
<xsl:template match="d:total" />
</xsl:stylesheet>
A few pieces of this example are worth special attention. First, note that the SpreadsheetML is wrapped in XSLT; the SpreadsheetML becomes part of the stylesheet. There's one piece of the SpreadsheetML you can't re-create with this method: the processing instruction noted earlier that tells Windows this is an Excel spreadsheet. For that, you have to use the following:
<xsl:processing-instruction name="mso-application">progid=[RETURN] "Excel.Sheet"</xsl:processing-instruction>
Because XSLT won't allow you to use the default namespace (no prefix) to refer to content that has a namespace, all the references to content in the source document now have the prefix d:, such as d:sale, d:date, etc.
Also, because the named ranges will vary depending on the number of sale elements in the original, this stylesheet won't generate the Names element and its contents. Excel will re-create the named ranges from the NamedCell elements in any case. The heart of this stylesheet is again the part that generates the Row and Cell elements, as shown in the following:
<xsl:template match="d:sale">
<Row>
<Cell><Data ss:Type="Number"><xsl:value-of select="d:IDnum" /></Data>
<NamedCell ss:Name="ID"/></Cell>
The xsl:template element will collect every sale element in the original and produce a Row element that contains Cell elements matching its contents. If you open in Excel the SpreadsheetML that this stylesheet produces (which looks much like that in the example, minus named ranges and some formatting), you get the result shown in the figure.
Figure. The test spreadsheet after its data has gone from SpreadsheetML to another vocabulary and back again
SpreadsheetML might not look very beautiful, but there are lots of reasons you might want to use it. For one, saving as SpreadsheetML gives you better access to the XML map information described in the previous tutorial than Excel's GUI offers at present. More importantly in the long run, SpreadsheetML is portable, and you can process it and generate it on virtually any computer that has basic XML facilities.