0

Given this Open XML snippet:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<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">

    <Worksheet ss:Name="Sheet5">
      <Table ss:ExpandedColumnCount="7" ss:ExpandedRowCount="4">
        <Column ss:Index="2" ss:AutoFitWidth="0" ss:Width="59.25"/>
        <Column ss:Index="5" ss:AutoFitWidth="0" ss:Width="75"/>
        <Column ss:AutoFitWidth="0" ss:Width="31.5"/>
      </Table>
    </Worksheet>

</Workbook>

...what XSLT 1.0 templating solution (avoiding loops if possible) can I use to produce this HTML output snippet:

<table>
  <colgroup>
    <col style="width:45pt;">
    <col style="width:59.25pt;">
    <col style="width:45pt;">
    <col style="width:45pt;">
    <col style="width:75pt;">
    <col style="width:31.5pt;">
    <col style="width:45pt;">
  </colgroup>
</table>

Note that the ss:ExpandedColumnCount attribute specifies the total number of columns. Also note that a Column tag in the source that does not include an ss:Index is a special case that specifies the next column index after the previous index; in this case, it evaluates to 6. All other columns assume the width of 45pt.

In my environment, there is no choice but to use the MSXML2 Engine.

This is just an arbitrary example of a range selection on an MS Excel worksheet encoded in Open XML, sometimes referred to as OOXML. I am looking for the XLST to perform this transformation in the general case.

Improve this question
5
  • Where did the value of 75pt come from? Commented Jan 1, 2017 at 13:03
  • We need a fuller XML snippet to see where ss namespace is declared. Commented Jan 1, 2017 at 13:44
  • @michael.hor257k That's a typo. Fixed. Commented Jan 1, 2017 at 16:03
  • @Parfait The question is tagged openxml, but I've added to the snippet. Commented Jan 1, 2017 at 16:10
  • Technically speaking there are no loops in XSLT. Being a functional, declarative language variables in XSLT are immutable. But then you may point out the xsl:for-each, but this is more a mapping expression than a loop known in procedural languages. Commented Jan 1, 2017 at 19:00

2 Answers 2

Reset to default
2

I am not sure what exactly you mean by "avoiding loops if possible". I certainly don't see how this can be accomplished without using recursion. In fact, I believe you need to use it in two places:

  • first, to generate a list of overriding column widths where every node has an index value;

  • then, to generate the required number of col elements.

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
exclude-result-prefixes="ss"
xmlns:exsl="http://exslt.org/common"
extension-element-prefixes="exsl">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:template match="ss:Table">
    <xsl:variable name="col-widths-rtf">
        <xsl:apply-templates select="ss:Column[1]" mode="col-widths"/>
    </xsl:variable>
    <table>
        <colgroup>
            <xsl:call-template name="colgroup">
                <xsl:with-param name="n" select="@ss:ExpandedColumnCount"/>
                <xsl:with-param name="col-widths" select="exsl:node-set($col-widths-rtf)/width"/>
            </xsl:call-template>
        </colgroup>
    </table>
</xsl:template>

<xsl:template match="ss:Column" mode="col-widths">
    <xsl:param name="last-index" select="0"/>
    <xsl:variable name="index">
        <xsl:choose>
            <xsl:when test="@ss:Index">
                <xsl:value-of select="@ss:Index"/>
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="$last-index + 1"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:variable>
    <width index="{$index}">
        <xsl:value-of select="@ss:Width"/>
    </width>
    <!-- sibling recursion -->
    <xsl:apply-templates select="following-sibling::ss:Column[1]" mode="col-widths">
        <xsl:with-param name="last-index" select="$index"/>
    </xsl:apply-templates>
</xsl:template>

<xsl:template name="colgroup">
    <xsl:param name="n"/>
    <xsl:param name="col-widths"/>
    <xsl:variable name="override" select="$col-widths[@index=$n]" />
    <xsl:variable name="width">
        <xsl:choose>
            <xsl:when test="$override">
                <xsl:value-of select="$override"/>
            </xsl:when>
            <xsl:otherwise>45</xsl:otherwise>
        </xsl:choose>       
    </xsl:variable>
    <xsl:if test="$n > 1">
        <!-- recursive call -->
        <xsl:call-template name="colgroup">
            <xsl:with-param name="n" select="$n - 1"/>
            <xsl:with-param name="col-widths" select="$col-widths"/>
        </xsl:call-template>
    </xsl:if>
    <col style="width:{$width}pt;"/>
</xsl:template>

</xsl:stylesheet>
Improve this answer
Sign up to request clarification or add additional context in comments.

12 Comments

Good answer! This recursive template call process looks similar to an answer you gave on my question in transposing the Google Doodles. No doubt the @michael.hor257k signature!
Great answer, but as it stands it won't work for me. I am forced to use MSXML2 to do the transform and apparently it does not support the extension. Can your answer be reworked without extensions?
@astudent See if it supports the same function in msxsl namespace: msdn.microsoft.com/en-us/library/hz88kef0%28v=vs.110%29.aspx
Well, using the msxsl namespace it at least did not error during the transform, but it did not work correctly. All of the column tags were output with the default value.
Lack of error is not a sufficient indication of support - use function-available('msxsl:node-set') to make sure. w3.org/TR/xslt/#function-function-available
|
1

I think it can be done without an extension function, and in a single pass, but I might be wrong, as I haven't fully tested the following XSLT in all possible circumstances, but give it a go anyway...

<xsl:stylesheet version="1.0" 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    exclude-result-prefixes="ss">
<xsl:output method="xml" indent="yes"/>

<xsl:template match="ss:Table">
    <table>
        <colgroup>
          <xsl:call-template name="Columns" />
        </colgroup>
    </table>
</xsl:template>

<xsl:template name="Columns">
  <xsl:param name="MaxColumns" select="@ss:ExpandedColumnCount" />
  <xsl:param name="Column" select="ss:Column[1]" />
  <xsl:param name="ColumnNumber" select="1" />

  <xsl:if test="$ColumnNumber &lt;= $MaxColumns">
    <xsl:variable name="IsMatch" select="$Column/@ss:Index = $ColumnNumber or $Column[not(@ss:Index)]" />
    <xsl:variable name="width">
      <xsl:choose>
        <xsl:when test="$IsMatch">
          <xsl:value-of select="$Column/@ss:Width" />
        </xsl:when>
        <xsl:otherwise>45</xsl:otherwise>
      </xsl:choose>
    </xsl:variable>  
    <col style="width:{$width}pt;"></col>
    <xsl:call-template name="Columns">
      <xsl:with-param name="MaxColumns" select="$MaxColumns" />
      <xsl:with-param name="Column" select="$Column[not($IsMatch)]|$Column[$IsMatch]/following-sibling::ss:Column[1]" />
      <xsl:with-param name="ColumnNumber" select="$ColumnNumber + 1" />
    </xsl:call-template>
  </xsl:if>
</xsl:template>
</xsl:stylesheet>
Improve this answer

1 Comment

This worked. And I like the fact that ti does not rely on an extension.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.