Skip to content

Instantly share code, notes, and snippets.

@pdaengeli

pdaengeli/RenamaUsingFirstRow.xsl

Created July 10, 2017 23:07
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save pdaengeli/463ba4544a89e173bb548507d68e72ac to your computer and use it in GitHub Desktop.
Save pdaengeli/463ba4544a89e173bb548507d68e72ac to your computer and use it in GitHub Desktop.
Download ZIP
Restructure Spreadsheet ML
Raw
RenamaUsingFirstRow.xsl
<?xml version="1.0" encoding="UTF-8"?>
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xd="http://www.oxygenxml.com/ns/doc/xsl"
exclude-result-prefixes="xs xd"
version="2.0">
<xsl:output indent="yes"/>
<xd:doc>
<xd:desc>
<xd:p>This transformation replaces the incoming element names by the value given in the first row.</xd:p>
</xd:desc>
</xd:doc>
<xsl:template match="*:dataroot">
<xsl:copy>
<xsl:apply-templates select="*[position() > 1]"/>
</xsl:copy>
</xsl:template>
<!-- applies to third and following rows -->
<xsl:template match="*[position() > 1][*/text()]">
<object>
<xsl:call-template name="renameChildren"/>
</object>
</xsl:template>
<xsl:template name="renameChildren">
<xsl:for-each select="*:Cell">
<!-- renaming the element using the value in the first row -->
<xsl:variable name="position" select="@*:Index"/>
<!-- workaround to fix ill-named column headings/element names -->
<xsl:variable name="heading" select="translate(/*/*[1]/*[position()=$position],': (),','')"/>
<xsl:element name="{$heading}"><xsl:value-of select="."/></xsl:element>
</xsl:for-each>
</xsl:template>
<xsl:template match="text()"/>
</xsl:transform>
Raw
RestructureSpreadsheetML.xsl
<?xml version="1.0" encoding="UTF-8"?>
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xd="http://www.oxygenxml.com/ns/doc/xsl"
exclude-result-prefixes="xs xd"
version="3.0">
<xd:doc>
<xd:desc>
<xd:p>SpreadsheetML as exported by Excel indicates empty cells using an index attribute where needed. In order to reduce complexity,
this stylesheet assigns an index attribute to each cell. This way each cell can directly be addressed.</xd:p>
<!--<xd:p>Furthermore, this stylesheet creates placeholder elements for empty cells. This is needed for subsequent position based actions.</xd:p>-->
</xd:desc>
</xd:doc>
<!-- prevent unnecessary whitespace nodes to reduce memory usage -->
<xsl:strip-space elements="*"/>
<xsl:output indent="yes"/>
<!--<xsl:variable name="columnTotal" select="count(*:Workbook/*:Worksheet/*:Table/*:Row[1]/*:Cell)"/>-->
<xsl:template match="*:Workbook">
<dataroot>
<xsl:apply-templates/>
</dataroot>
</xsl:template>
<xsl:template match="*:Worksheet[1]/*:Table/*:Row">
<object>
<xsl:for-each-group select="*:Cell" group-starting-with=".[@ss:Index]">
<!--[START]-->
<xsl:for-each select="current-group()">
<xsl:variable name="setIndex">
<xsl:choose>
<xsl:when test="self::*:Cell[@ss:Index]">
<xsl:value-of select="self::*:Cell/@ss:Index"/>
</xsl:when>
<xsl:when test="preceding-sibling::*:Cell[@ss:Index][1]">
<xsl:value-of select="preceding-sibling::*:Cell[@ss:Index][1]/@ss:Index + position() -1"/>
</xsl:when>
<xsl:when test="not(preceding-sibling::*:Cell[@ss:Index][1])">
<xsl:value-of select="position()"/>
</xsl:when>
</xsl:choose>
</xsl:variable>
<xsl:copy copy-namespaces="no">
<xsl:attribute name="ss:Index" select="$setIndex"/>
<!--<xsl:copy-of select="@* | node()" copy-namespaces="no"/>-->
<xsl:apply-templates/>
</xsl:copy>
<!-- Create placeholder elements for empty cells based on calculated number (deactivated for memory reasons) -->
<!-- <xsl:if test="position() = last()">
<xsl:variable name="fill">
<xsl:choose>
<xsl:when test="following-sibling::*:Cell[@ss:Index][1][@ss:Index]">
<xsl:value-of select="following-sibling::*:Cell[@ss:Index][1]/@ss:Index - $setIndex -1"/>
</xsl:when>
<xsl:when test="not(following-sibling::*:Cell[1])">
<xsl:value-of select="$columnTotal - $setIndex -1"/>
</xsl:when>
</xsl:choose>
</xsl:variable>
<xsl:for-each select="1 to $fill">
<Cell ss:Index="{$setIndex + current()}"/>
</xsl:for-each>
</xsl:if>-->
</xsl:for-each>
<!--[END]-->
</xsl:for-each-group>
</object>
</xsl:template>
<xsl:template match="*:Data">
<xsl:value-of select="."/>
</xsl:template>
<xsl:template match="text()"/>
</xsl:transform>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment