Example A stylesheet for generating SpreadsheetML Microsoft Office and XML

Example A stylesheet for generating SpreadsheetML

Mon, 29 Aug 2011 14:02:50 | Microsoft Office and XML

How To Make Money Writing Easy Web Articles

<xsl:stylesheet version="1.0"

xmlns:xsl="http://www.w3.org/19 9 9/XSL/Transform" xmlns:d="http://simonstl.com/ns/dinosaurs/"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:html="http://www.w3.org/TR/REC-html4 0"

<xsl:output method="xml" omit-xml-declaration="no" indent="yes" encoding="US-ASCII"/>

<xsl:template match="d:dinosaurs">

<xsl:processing-instruction name="mso-application">progid= "Excel.Sheet"</xsl

<!--Namespace declarations moved from Workbook to xsl:stylesheet--	>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
<aStyleM
<Style ss:ID="s21">
<NumberFormat s s:Format="mmm\-yy"/>
</Style>
<Style ss:ID="s22">
<NumberFormat ss:Format=""$"#, ##0.00"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheetl">
<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="{count(d:	sale)+4}"
x:FullRows="1">
<Column ss:AutoFitWidth="0" ss:Width="7 3.5"/>
<Column ss:AutoFitWidth="0" ss:Width="96.75"/>
<Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="5 6.25"/>
<Row>
<Cell><Data ss:Type="String">Sales for:</Data></Cell>
<Cell ss:StyleID="s21"XData ss:Type="DateTime"Xxsl: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</Datax/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 /e:Index="4"><Data ss:Type="String">Total:</Data></Cell>

<ProtectObjects>False</ProtectObj ects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet>

<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"	>
<ProtectObjects>False</ProtectObj ects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"	>
<ProtecOObj e ctv>False</ProtectObj ects>
<ProtecAScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
</xsl:template>
<xsl:template match="d:sale">
<Row>
<CellXData ss :Type="Number"><xsl:value-of
select="d:IDnum" /></Data><NamedCell ss:Name="ID"/x/Cell>
<Cell><Data ss:Type="String"><xsl:value-of select="d:critter"	/></
DataXNamedCell ss : Name = "Critters"/></Cell>
<Cell ss:StyleID="s2 2"><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>

There are a few pieces of this worth special attention. First, note that the SpreadsheetML is wra part of the stylesheet. There's one extra namespace declaration:

xmlns:d="http://simonstl.com/ns/dinosaurs/"

XSLT requires that references to parts of XML documents that have namespace URIs also have i in the stylesheet to elements in the original document will look like d:sale instead of just sale.

There's also one piece of the SpreadsheetML we need to recreate explicitly, and not just by incli instruction noted earlier that tells Windows this is an Excel spreadsheet. For that, we have to us

<xsl:processing-instruction name="mso-application">progid= "Excel.Sheet"</xsl:processing-instruction>

Because the named ranges will vary depending on the number of sale elements in the original, element an d its contents. Excel will recreate the named ranges from the NamedCell elements in

This stylesheet creates a Table element complete with (accurate) ss:ExpandedColumnCount an

<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="{count(d:sale)+4}"

x:FullColumns="1" x:FullRows="1">

If ca lculat ing the numb er of oows o r columns in y our spread sheet i s going no be di fficult, it will b produces an error- if wsong but li ttle benefin if right.

Tue fi rst row of the sprea dsheet con tains the date i

<Cell><Data ss : Type="String">Sales f or: </Datax/Cell>

<Cell ss:StyleID="s21"XData ss :Type="DateTime"><xsl:value-of select="d:dat<

Thexsl:value-of element pulls the information from the date element of the XML document an we'll see at the end of the spreadsheet, regular processing of the date element (and the total to be suppressed.

The heart of this stylesheet is again the part that generates the Row and Cell elements, like:

<xsl:template match="d:sale">

<Cell><Data ss:Type="Number"><xsl:value-of select="d:IDnum" /></Data><Named(

Thexsl:template element will collect every sale element in the original and produce a Row ele matching its contents. Each Row contains the contents of one sale element. To keep XSLT from total elements, which would drop their values into the SpreadsheetML as (unexpected) text, th processing for them with empty xsl:template elements.

<xsl:template match="d:date" />

<xsl:template match="d:total" />

The SpreadsheetML created by this stylesheet from the XML data in Example 7-8 looks like Exai Example 7-11. A SpreadsheetML document created with XSLT

<?mso-application progid="Excel.Sheet"?>

<Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="12" x:	FullColumns="1"
x:FullRows="1">
<Column ss:AutoFitWidth="0" ss:Width="7 3.5"/>
<Column ss:AutoFitWidth="0" ss:Width="96.75"/>
<Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="5 6.25"/>
<Row>
<Cell><Data ss:Type="String">Sales for:</Data></Cell>
<Cell se : Sir yleID="s21"><Data ss: Type = "DateTime">2 00 4-01-01T00 :00 :00. 000</Dai
</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>
<Row>
<Cell><Data ss:Type="Number">4 62 7</Data><NamedCell ss:Name=	"ID"/></Cell>
<Cell><Data ss:Type="String">Diplodocus</DataXNamedCell ss	:Name = "Critters",
</Cell>
<Cell ss:StyleID="s22"><Data ss:Type="Number">22.5</Data><NamedCell
ss:Name="Price"/></Cell>
<Cell><Data ss:Type="Number">127</Data><NamedCell ss:Name="	Quantity"/></Cell
<Cell ss:StyleID="s22" ss:Formula="=RC[-2]*RC[-1]"><Data ss	:Type="Number">
2 857.5</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">3912</Data><NamedCell ss:Name=	"ID"/></Cell>
<Cell><Data ss:Type="String">Brontosaurus</Data>
<NamedCell ss:Name="Critters"/></Cell>

<Cell><Data ss:Type="Number">98 4 5</Data><NamedCell ss:Name="ID"/></Cell> <Cell><Data ss:Type="String">Triceratops</Data>

cNamedCell ss:Name="Critters"/>c/Cell> <Cell ss:StyleID="s22"><Data ss:Type="Number">12</Data> <NamedCell ss:Name="Price"/>c/Cell> <Cell><Data ss:Type="Number">91</Data><NamedCell ss:Name="Quantity"/></Cell> <Cell ss:StyleID="s22" ss:Formula="=RC[-2]*RC[-1]"><Data ss:Type="Number"> 1G92</Data></Cell>

<CellxData ss : Type="Number">962 5</DataXNamedCell s s : Name = " ID" /></Cell> cCell>cData ss:Type="String">Vulcanodon</Data>

cNamedCell ss:Name="Critters"/>c/Cell> <Cell ss:StyleID="s22"><Data ss:Type="Number">19</Data><NamedCell ss:Name="Price"/>c/Cell> <Cell><Data ss:Type="Number">1G8</Data><NamedCell ss:Name="Quantity"/></Cell <Cell ss:StyleID="s22" ss:Formula="=RC[-2]*RC[-1]"><Data ss:Type="Number"> 2G52</Data></Cell>

<Cell><Data ss:Type="Number">5 9G3</Data><NamedCell ss:Name="ID"/></Cell> <Cell><Data ss:Type="String">Stegosaurus</Data>

<Cell><Data ss:Type="Number">182 4</Data><NamedCell ss:Name="ID"/></Cell> <Cell><Data ss:Type="String">Monoclonius</Data>

cNamedCell ss:Name="Critters"/></Cell> <Cell ss:StyleID="s22"><Data ss:Type="Number">16.5</Data><NamedCell ss:Name="Price"/>c/Cell> <Cell><Data ss:Type="Number">133</Data><NamedCell ss:Name="Quantity"/></Cell <Cell ss:StyleID="s22" ss:Formula="=RC[-2]*RC[-1]"><Data ss:Type="Number"> 2194.5</Data></Cell> </Row> <Row>

<Cell><Data ss:Type="Number">9728</Data><NamedCell ss :Name = "ID"/></Cell> <Cell><Data ss:Type="String">Megalosaurus</Data>

cNamedCell ss:Name="Critters"/>c/Cell> <Cell ss:StyleID="s22"><Data ss:Type="Number">23</Data><NamedCell ss:Name="Price"/>c/Cell> <Cell><Data ss:Type="Number">12 8</Data><NamedCell ss:Name="Quantity"/></Cell <Cell ss:StyleID="s22" ss:Formula="=RC[-2]*RC[-1]"><Data ss:Type="Number"> 2944</Data></Cell> </Row> <Row>

<Cell><Data ss:Type="Number">8 64 9</Data><NamedCell ss:Name="ID"/></Cell> cCell>cData ss:Type="String">Barosaurusc/Data>

<NamedCell ss:Name="Critters"/></Cell>
<Cell ss:StyleID="s22"><Data ss:Type="Number">17</Data><NamedCell
ss:Name="Price"/></Cell>
<Cell><Data ss:Type="Number">91</Data><NamedCell	ss:Name="Quantity"/></Cell:
<Cell ss:StyleID="s22" ss:Formula="=RC[-2]*RC[-1]	"><Data ss:Type="Number">
15 4 7</Data></Cell>
</Row>
<Row>
<Cell ss:Index="4"><Data ss:Type="String">Total:</Data></Cell>
<Cell ss:StyleID="s22" ss:Formula="=SUM(R[-8]C:R[	-1]C)">
<Data ss:Type="Number">15147.5</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:	office:excel">
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>60 0</VerticalResolution>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>12</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObj ects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>	<Worksheet ss:Name="Sheet2"> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <ProtectObjects>False</ProtectObj ects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> <WorksheeR ss:Name="Sheet3"> <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel"> <ProtectObjects>False</ProtectObj ects> <ProtectScenarios>False</ProtectScenarios> </WorksheetOptions> </Worksheet> </Workbook> If you open the SpreadsheetML this stylesheet produces (which looks much like that in Example and formatting) in Excel, we get the result shown inFigure 7-8.

HAWOK XML Faq

» Microsoft Office and XML