Search code examples
excelxsltformulatranslatexsl-choose

Run a xsl:choose using the result of a formula

Creating an XSL to translate XML into Excel. Part of that is a formula which performs a lookup on another worksheet. However, I want to transform/translate the return, so I figured an xsl:choose would be best.

But I don't know how to reference my ss:Formula inside the xsl:choose?

    <Cell ss:StyleID="RARs17"
        ss:Formula="=INDEX('{$vhostname}'!R2C6:R{$vuln_count+7}C6,MATCH(RC3,'{$vhostname}'!R2C14:R{$vuln_count+7}C14,0))>
        <Data ss:Type="String">
        <xsl:choose>
            <xsl:when test="ss:Formula = 'NF'">Completed</xsl:when>
            <xsl:when test="ss:Formula = 'NR'">NR</xsl:when>
            <xsl:when test="ss:Formula = 'O'">Ongoing</xsl:when>
            <xsl:when test="ss:Formula = 'NA'">NA</xsl:when>
            <xsl:otherwise><xsl:value-of select="ss:Formula" /></xsl:otherwise>
        </xsl:choose></Data>
    </Cell> <!-- Status, looks up Result -->
Solution

  • You cannot refer to nodes of the output tree. Define the formula as a variable and use that in the attribute as well as in the expression.

    Edit

    I probably misunderstood what you meant by "reference my ss:Formula". If you want to consider the result of the actual lookup as performed by Excel, that won't work - for reasons explained by Tim C in his comment.

    You could, however, perform the lookup yourself using XSLT methods such as key.