Author:
TwistedInteractive
Version:
0.1
Release Date:
22 Oct 2010
Category:
Output

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

View Raw
<?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>

Discuss this XSLT Utility

Symphony • Open Source XSLT CMS

Server Requirements

  • PHP 5.3-5.6 or 7.0-7.3
  • PHP's LibXML module, with the XSLT extension enabled (--with-xsl)
  • MySQL 5.5 or above
  • An Apache or Litespeed webserver
  • Apache's mod_rewrite module or equivalent

Compatible Hosts

Sign in

Login details