Export to Microsoft Excel 2003 XML
Create an Excel Sheet according to a datasource or one ore more groups from a datasource
Description
This utility takes a datasource with entries or a data-source with grouped entries and converts it to a Microsoft Excel 2003 XML datasheet.
Parameters
Required:
sheets
An XPath expression of a node to select the entries from.
or:
An XPath expression to select one ore more groups with (data-source with 'Group By'-output)
headers
A comma-seperated list of the headers (must be of equal length with entryNames)
entryNames
A comma-seperated list of the entry names (must be of equal length with headers)
Optional:
withSubs
A string to determine the action to perform when an entry has sub-elements (selectboxes for instance). Valid options are 'implode', 'first', 'last'.
Example calls
Create an Excel sheet from a section:
<xsl:call-template name="excel">
<xsl:with-param name="sheets" select="data/subscribers" />
<xsl:with-param name="headers" select="'Name, E-mail address'" />
<xsl:with-param name="entryNames" select="'name, e-mail-address'" />
</xsl:call-template>
Create an Excel sheet from multiple groups (data-source with 'Group By'-output):
<xsl:call-template name="excel">
<xsl:with-param name="sheetGroups" select="data/real-estate/area" />
<xsl:with-param name="withSubs" select="'last'" />
<xsl:with-param name="headers" select="'Street, Number, Area, Price, Status'" />
<xsl:with-param name="entryNames" select="'street, number, area, price, status'" />
</xsl:call-template>
XSLT
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="urn:schemas-microsoft-com:office:spreadsheet"> <!-- Export to Microsoft Excel 2003 XML ======================================================================== Author: Twisted Website: http://www.twisted.nl Description: Create a Microsoft Excel 2003 XML Sheet according to a datasource or one ore more groups from a datasource. ======================================================================== Parameters: Required: sheets An XPath expression of a node to select the entries from. or: An XPath expression to select one ore more groups with (data-source with 'Group By'-output) headers A comma-seperated list of the headers (must be of equal length with entryNames) entryNames A comma-seperated list of the entry names (must be of equal length with headers) Optional: withSubs A string to determine the action to perform when an entry has sub-elements (selectboxes for instance). Valid options are 'implode', 'first', 'last'. ======================================================================== Example calls: Create an Excel sheet from a section: <xsl:call-template name="excel"> <xsl:with-param name="sheets" select="data/subscribers" /> <xsl:with-param name="headers" select="'Name, E-mail address'" /> <xsl:with-param name="entryNames" select="'name, e-mail-address'" /> </xsl:call-template> Create an Excel sheet from multiple groups (data-source with 'Group By'-output): <xsl:call-template name="excel"> <xsl:with-param name="sheetGroups" select="data/real-estate/area" /> <xsl:with-param name="withSubs" select="'last'" /> <xsl:with-param name="headers" select="'Street, Number, Area, Price, Status'" /> <xsl:with-param name="entryNames" select="'street, number, area, price, status'" /> </xsl:call-template> --> <xsl:template name="excel"> <xsl:param name="sheets" /> <xsl:param name="headers" /> <xsl:param name="entryNames" /> <xsl:param name="withSubs" select="'implode'" /> <!-- Output the Excel 2003 XML: --> <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction> <Workbook xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel"> <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"> <Colors> <Color> <Index>3</Index> <RGB>#c0c0c0</RGB> </Color> <Color> <Index>4</Index> <RGB>#ff0000</RGB> </Color> </Colors> </OfficeDocumentSettings> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> <WindowHeight>9000</WindowHeight> <WindowWidth>13860</WindowWidth> <WindowTopX>240</WindowTopX> <WindowTopY>75</WindowTopY> <ProtectStructure>False</ProtectStructure> <ProtectWindows>False</ProtectWindows> </ExcelWorkbook> <Styles> <Style ss:ID="Default" ss:Name="Default"/> <Style ss:ID="Result" ss:Name="Result"> <Font ss:Bold="1" ss:Italic="1" ss:Underline="Single"/> </Style> <Style ss:ID="Result2" ss:Name="Result2"> <Font ss:Bold="1" ss:Italic="1" ss:Underline="Single"/> <NumberFormat ss:Format="Euro Currency"/> </Style> <Style ss:ID="Heading" ss:Name="Heading"> <Font ss:Bold="1" ss:Italic="1" ss:Size="16"/> </Style> <Style ss:ID="Heading1" ss:Name="Heading1"> <Font ss:Bold="1" ss:Italic="1" ss:Size="16"/> </Style> <Style ss:ID="co1"/> <Style ss:ID="ta1"/> <Style ss:ID="ta_extref"/> </Styles> <!-- Process data: --> <xsl:call-template name="excelBuildSheet"> <xsl:with-param name="sheets" select="$sheets" /> <xsl:with-param name="withItem" select="$withSubs" /> <xsl:with-param name="headers" select="$headers" /> <xsl:with-param name="entryNames" select="$entryNames" /> </xsl:call-template> </Workbook> </xsl:template> <!-- Build the sheet: --> <xsl:template name="excelBuildSheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel"> <xsl:param name="sheets" /> <xsl:param name="withItem" /> <xsl:param name="headers" /> <xsl:param name="entryNames" /> <xsl:if test="$sheets != ''"> <!-- Create a sheet for each group/section: --> <xsl:for-each select="$sheets"> <!-- Create a worksheet with the right name for this group: --> <xsl:variable name="sheetName"> <xsl:choose> <xsl:when test="$sheets/section"> <!-- In case of a section --> <xsl:value-of select="section" /> </xsl:when> <xsl:when test="$sheets/@value"> <!-- In case of a group --> <xsl:value-of select="@value" /> </xsl:when> <xsl:otherwise> <!-- Unknown --> <xsl:value-of select="'Untitled'" /> </xsl:otherwise> </xsl:choose> </xsl:variable> <ss:Worksheet ss:Name="{$sheetName}"> <Table ss:StyleID="ta1"> <Column ss:Span="13" ss:Width="64.2614"/> <Row ss:Height="13.5496"> <!-- Show the headers: --> <xsl:call-template name="excelOutputHeaders"> <xsl:with-param name="list" select="$headers" /> </xsl:call-template> </Row> <!-- Show the rows: --> <xsl:for-each select="entry"> <Row ss:Height="13.5496"> <xsl:call-template name="excelOutputData"> <xsl:with-param name="list" select="$entryNames" /> <xsl:with-param name="entry" select="." /> <xsl:with-param name="withItem" select="$withItem" /> </xsl:call-template> </Row> </xsl:for-each> </Table> <x:WorksheetOptions/> </ss:Worksheet> </xsl:for-each> </xsl:if> </xsl:template> <!-- Recursive template used for the data of the worksheet: --> <xsl:template name="excelOutputData" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> <xsl:param name="list" /> <xsl:param name="entry" /> <xsl:param name="withItem" /> <xsl:variable name="newlist" select="normalize-space($list)" /> <xsl:variable name="first" select="normalize-space(substring-before($newlist, ','))" /> <xsl:variable name="remaining" select="normalize-space(substring-after($newlist, ','))" /> <!-- Show the node: --> <Cell> <Data ss:Type="String"> <!-- Set the data: --> <xsl:choose> <xsl:when test="$first"> <xsl:call-template name="excelGetData"> <xsl:with-param name="field" select="$entry/*[name() = $first]" /> <xsl:with-param name="withItem" select="$withItem" /> </xsl:call-template> </xsl:when> <xsl:otherwise> <xsl:call-template name="excelGetData"> <xsl:with-param name="field" select="$entry/*[name() = $newlist]" /> <xsl:with-param name="withItem" select="$withItem" /> </xsl:call-template> </xsl:otherwise> </xsl:choose> </Data> </Cell> <!-- Recursive loop: --> <xsl:if test="$remaining"> <xsl:call-template name="excelOutputData"> <xsl:with-param name="list" select="$remaining" /> <xsl:with-param name="entry" select="$entry" /> <xsl:with-param name="withItem" select="$withItem" /> </xsl:call-template> </xsl:if> </xsl:template> <!-- Retreive the data value of a single field: --> <xsl:template name="excelGetData"> <xsl:param name="field" /> <xsl:param name="withItem" /> <!-- Show the value: --> <xsl:choose> <!-- Check if it has sub-items: --> <xsl:when test="count($field/*) = 0"> <!-- Show default value: --> <xsl:value-of select="$field" /> </xsl:when> <xsl:otherwise> <!-- Field doesn't have a value, treat as an item: --> <xsl:choose> <xsl:when test="$withItem = 'implode'"> <xsl:for-each select="$field/*"> <xsl:value-of select="." /> <xsl:if test="position() != last()">, </xsl:if> </xsl:for-each> </xsl:when> <xsl:when test="$withItem = 'last'"> <xsl:value-of select="$field/*[last()]" /> </xsl:when> <xsl:when test="$withItem = 'first'"> <xsl:value-of select="$field/*[1]" /> </xsl:when> </xsl:choose> </xsl:otherwise> </xsl:choose> </xsl:template> <!-- Recursive template used for the headers of the worksheet: --> <xsl:template name="excelOutputHeaders" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> <xsl:param name="list" /> <xsl:variable name="newlist" select="normalize-space($list)" /> <xsl:variable name="first" select="normalize-space(substring-before($newlist, ','))" /> <xsl:variable name="remaining" select="normalize-space(substring-after($newlist, ','))" /> <!-- Show the node: --> <Cell> <Data ss:Type="String"> <xsl:choose> <xsl:when test="$first"> <xsl:value-of select="$first" /> </xsl:when> <xsl:otherwise> <!-- The last item is reached: --> <xsl:value-of select="$newlist" /> </xsl:otherwise> </xsl:choose> </Data> </Cell> <!-- Recursive loop: --> <xsl:if test="$remaining"> <xsl:call-template name="excelOutputHeaders"> <xsl:with-param name="list" select="$remaining" /> </xsl:call-template> </xsl:if> </xsl:template> </xsl:stylesheet>