Using XSLT to convert XML into Excel File

Question

Thanks in advance, I'm new to XSLT, if there's anything that i'm doing completly wrong tell me please! I'm trying to convert this piece of XML to an Excel Spreadsheet using XSLT

  <MODULO>
    <NAME>ObtemNovoSubsidiarias</NAME>
    <VALUES>
      <ROW>
        <Funcao>Sócio</Funcao>
        <Nome>WURTH INTERNATIONAL AG</Nome>
        <NumeroSine>100088</NumeroSine>
        <pais>PORTUGAL</pais>
      </ROW>
      <ROW>
        <FormacaoAcademica />
        <Funcao>Sócio</Funcao>
        <Nome>WURTH SCHWEIZ AG</Nome>
        <NumeroSine>1394204</NumeroSine>
        <pais />
      </ROW>
    </VALUES>
  </MODULO>

This is the XSLT i have For this only, i have other tags but this is the one i'm having problems with.

 <xsl:when test="NAME='ObtemNovoSubsidiarias'">
            <Worksheet ss:Name="Empresas_Soc">
              <Table>
                <ss:Column ss:Width="100"/>
                <ss:Column ss:Width="350"/>
                <xsl:for-each select="VALUES/ROW">
                  <Row ss:Index="1">
                    <Cell ss:Index="1" ss:StyleID="header">
                      <Data ss:Type="String">
                        Nome
                      </Data>
                    </Cell>
                    <Cell ss:Index="2" ss:StyleID="header">
                      <Data ss:Type="String">
                        <xsl:value-of select="Nome"/>
                      </Data>
                    </Cell>
                  </Row>
                  <Row ss:Index="{position() + 1}">
                    <Cell ss:Index="1" ss:StyleID="header">
                      <Data ss:Type="String">
                        TRUST Nº
                      </Data>
                    </Cell>
                    <Cell ss:Index="2" ss:StyleID="header">
                      <Data ss:Type="String">
                        <xsl:value-of select="NumeroSine"/>
                      </Data>
                    </Cell>
                  </Row>
                 <Row ss:Index="{position() + 2}">
                    <Cell ss:Index="1" ss:StyleID="header">
                      <Data ss:Type="String">
                        FUNÇÃO
                      </Data>
                    </Cell>
                    <Cell ss:Index="2" ss:StyleID="header">
                      <Data ss:Type="String">
                        <xsl:value-of select="Funcao"/>
                      </Data>
                    </Cell>
                  </Row>
                  <xsl:choose>
                    <xsl:when test="pais = ''">
                      <Row ss:Index="{position() + 3}">
                      </Row>
                    </xsl:when>
                    <xsl:otherwise>
                      <Row ss:Index="{position() + 3}">
                        <Cell ss:Index="1" ss:StyleID="header">
                          <Data ss:Type="String">
                            PAÍS
                          </Data>
                        </Cell>
                        <Cell ss:Index="2" ss:StyleID="header">
                          <Data ss:Type="String">
                            <xsl:value-of select="pais"/>
                          </Data>
                        </Cell>
                      </Row>
                    </xsl:otherwise>
                  </xsl:choose>               
                </xsl:for-each>
              </Table>
            </Worksheet>
          </xsl:when>

(Note: The tag is called <ROW> but it's not an actual row, the rest of the XML are actualy Rows but some <MODULO> like this one are organized differently , since its automaticaly generated it ended up like that, in this case each <ROW> it's a different company i have to display)

The problem is, I'm suposed to show each <ROW>(company) separatly, like

Nome          WURTH
Funcao        Sócio
NumeroSine    100088
pais          PORTUGAL

Then a blank row in Excel and after the blank row the other <ROW> shows up. I'm able to show the first one but the 2nd one doesn't show up. I've tried a couple different approaches like making another <table> for each <ROW> but that doesn't work aswell.

If someone can help me i would be very thankfull!

Tim C · Accepted Answer · 2018-02-22 17:46:59Z

I think the problem you are having is with the setting of the ss:index for each ROW. Using position() gives the position of each ROW, so will be either 1 or 2 in your case. But where you do {position() + 1} to get the index for the TRUST Nº row, then for the first ROW the index will be 2 (which is correct), but for the second ROW the index is set to 3, when it should be 7 (as you have 4 excel rows per ROW plus a blank row).

So, the expression should be {(position() - 1) * 5 + 2} which returns 2 and 7 respectively. Thus, your xsl:for-each could look like this (I've removed the code that populates the second cell just to keep it shorter).

<xsl:for-each select="VALUES/ROW">
  <Row ss:Index="{(position() - 1) * 5 + 1}">
    <Cell ss:Index="1" ss:StyleID="header">
      <Data ss:Type="String">
        Nome
      </Data>
    </Cell>
  </Row>
  <Row ss:Index="{(position() - 1) * 5 + 2}">
    <Cell ss:Index="1" ss:StyleID="header">
      <Data ss:Type="String">
        TRUST Nº
      </Data>
    </Cell>
  </Row>
 <Row ss:Index="{(position() - 1) * 5 + 3}">
    <Cell ss:Index="1" ss:StyleID="header">
      <Data ss:Type="String">
        FUNÇÃO
      </Data>
    </Cell>
  </Row>
  <xsl:choose>
    <xsl:when test="pais = ''">
      <Row ss:Index="{(position() - 1) * 5 + 4}">
      </Row>
    </xsl:when>
    <xsl:otherwise>
      <Row ss:Index="{(position() - 1) * 5 + 4}">
        <Cell ss:Index="1" ss:StyleID="header">
          <Data ss:Type="String">
            PAÍS
          </Data>
        </Cell>
      </Row>
    </xsl:otherwise>
  </xsl:choose>           
  <Row ss:Index="{(position() - 1) * 5 + 5}">
  </Row>
</xsl:for-each>

However, this has the problem of adding two blank lines where there is not a pais element, when you probably only want one. If so, try this instead...

<xsl:for-each select="VALUES/ROW">
  <xsl:variable name="base" select="count(preceding-sibling::ROW) * 4 + count(preceding-sibling::ROW[pais != ''])" />
  <Row ss:Index="{$base + 1}">
    <Cell ss:Index="1" ss:StyleID="header">
      <Data ss:Type="String">
        Nome
      </Data>
    </Cell>
  </Row>
  <Row ss:Index="{$base + 2}">
    <Cell ss:Index="1" ss:StyleID="header">
      <Data ss:Type="String">
        TRUST Nº
      </Data>
    </Cell>
  </Row>
 <Row ss:Index="{$base + 3}">
    <Cell ss:Index="1" ss:StyleID="header">
      <Data ss:Type="String">
        FUNÇÃO
      </Data>
    </Cell>
  </Row>
  <xsl:choose>
    <xsl:when test="pais = ''">
      <Row ss:Index="{$base + 4}">
      </Row>
    </xsl:when>
    <xsl:otherwise>
      <Row ss:Index="{$base + 4}">
        <Cell ss:Index="1" ss:StyleID="header">
          <Data ss:Type="String">
            PAÍS
          </Data>
        </Cell>
      </Row>
      <Row ss:Index="{$base + 5}">
      </Row>
    </xsl:otherwise>
  </xsl:choose>           
</xsl:for-each>

Another technique that can sometimes be useful with this kind of problem is to add the ss:Index numbers in a second pass over the data.

Collectives™ on Stack Overflow

Using XSLT to convert XML into Excel File

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related