XSLT: Need to calculate Left and right using Excel cell width in XSLT

Question

I need to convert XML to XML using XSLT My input XML is:

 <sheets>
    <Cells>
      <rows rowNumber="0">
        <columns No="0">
          <cell id="A1" cellWidth="152" cellHeight="20" >test</cell>
        </columns>
        <columns No="1">
          <cell id="A2" cellWidth="52" cellHeight="20" >test1</cell>
        </columns>
        <columns No="2">
          <cell id="A3" cellWidth="50" cellHeight="40" >test1</cell>
        </columns>
        </rows>
        <rows rowNumber="1">
        <columns No="0">
          <cell id="B1" cellWidth="50" cellHeight="20" >test</cell>
        </columns>
        <columns No="1">
          <cell id="B2" cellWidth="50" cellHeight="40" >test1</cell>
        </columns>
        <columns No="2">
          <cell id="B3" cellWidth="51" cellHeight="20" >test2</cell>
        </columns>
        </rows>
        </Cells>
        </Sheets>

My Expected output is:

<page>
<line l="0" r="152" t="0" b="20">test</line>
<line l="153" r="204" t="0" b="20">test1</line>
<line l="205" r="254" t="0" b="40">test1</line>
<line l="0" r="50" t="20" b="40">test</line>
<line l="51" r="100" t="20" b="60">test1</line>
<line l="101" r="151" t="40" b="60">test1</line>
</page> 

Now, the problem is:
For calculating left and right value using width, I need to maintain 2 things:
1. till the cell total row width
2. cell width which I am getting from cell attribute cellWidth
Using above two parameter, I can calculate left and right e.g.
In above Example for Cell A3
Total row width: 254
Cell Width :50
So left=254-50
and right=254
Similarly, calculating top and bottom value using cell Height
But in that case, I need to calculate column wise data and i can not understand that how can i do that

I have tried using :
XSLT sum preceding siblings on attributes
How to sum the values of several nodes in XSLT
XSLT: Sum of all previous attributes

My XSLT code is:

                        <xsl:for-each select="columns">
                            <xsl:variable name="totalWidth" select="sum(preceding-sibling::cell/@cellWidth)"/>
                            <xsl:variable name="totalHeight" select="sum(preceding-sibling::cell/@cellWidth)"/>
                            <line>
                                            <xsl:attribute name="l">
                                    <xsl:value-of select="$totalWidth" />
                                </xsl:attribute>
                                <xsl:attribute name="r">
                                    <xsl:value-of select="(round(cell/@cellWidth)+$totalWidth)" />
                                </xsl:attribute>

                                <xsl:attribute name="t">
                                    <xsl:value-of select="$totalHeight" />
                                </xsl:attribute>

                                <xsl:attribute name="b">
                                    <xsl:value-of select="round(cell/@cellHeight)+$totalHeight" />
                                </xsl:attribute>
                                            <xsl:value-of select="cell" />

                                        </line>

So please share the XSLT code

Answer 1

One problem you have is with the totalWidth variable

<xsl:variable name="totalWidth" select="sum(preceding-sibling::cell/@cellWidth)"/>

At this point you are positioned on a column element, which only has column elements as preceding siblings. It should really be this..

<xsl:variable name="left" select="sum(preceding-sibling::columns/cell/@cellWidth)"/>

For getting the cell top t you need values from previous rows, so you could first define a variable (before the xsl:for-each like so)

<xsl:variable name="previousRows" select="preceding-sibling::rows" />

Then it is a case of counting columns from the previous rows with the same No as the current one

<xsl:variable name="top" select="sum($previousRows/columns[@No = current()/@No]/cell/@cellHeight)"/>

Putting this together gives this...

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
    <xsl:output method="xml" indent="yes" />

    <xsl:template match="rows">
        <xsl:variable name="previousRows" select="preceding-sibling::rows" />
        <xsl:for-each select="columns">
            <xsl:variable name="left" select="sum(preceding-sibling::columns/cell/@cellWidth)"/>
            <xsl:variable name="top" select="sum($previousRows/columns[@No = current()/@No]/cell/@cellHeight)"/>
            <line l="{$left}" r="{$left + cell/@cellWidth - 1}" t="{$top}" h="{$top + cell/@cellHeight}">
                <xsl:value-of select="cell" />
            </line>
        </xsl:for-each>
    </xsl:template>

    <xsl:template match="/*">
        <sheet>
           <xsl:apply-templates select="Cells/rows" />
        </sheet>
    </xsl:template>
</xsl:stylesheet>

Note that this does not quite give your expected output, because you give the first line as being <line l="0" r="152" t="0" b="20">, but if your left position has a zero-based index, a width of "152" would give a right value of 151.

Note the use of Attribute Value Templates to simplify the creation of attributes.