xmlxsltspreadsheetml

XSLT 1.0 - Build an XML spreadsheet with two tables based on separate XML elements

I need to build an XML Spreadsheet (SpreadsheetML) file in a certain format from an XML document which contains the data structured in separate elements (using XSLT 1.0). A simplified sample is below:

<Root>
<DetailsTable>
    <row data="1"/>
    <row data="2"/>
    <row data="3"/>
    <row data="4"/>
    <row data="5"/>
    <row data="6"/>
    <row data="7"/>
    <row data="8"/>
    <row data="9"/>
    <row data="10"/>
    <row data="11"/>
    <row data="12"/>
    <row data="13"/>
    <row data="14"/>
</DetailsTable>

<SummaryTable>
    <row data="A"/>
    <row data="B"/>
    <row data="C"/>
</SummaryTable>    
</Root>

The desired output is supposed to be layed out as two separate 'tables' on an Excel sheet, where one is sort of in the middle of the other - as in the screenshot:

enter image description here

The problem is that with XSLT transformation I need to be printing the rows one by one, and all cells have to be printed as the row is printed - therefore, for some rows (e.g. indexes 4,5,6,7) I need to add some special cells for which values I will get from the SummaryTable XML element.

In other words, the output XML format is something like that:

    <Table>
   <Row>
    <Cell><Data >DetailsTable</Data></Cell>
   </Row>
   <Row>
    <Cell><Data >1</Data></Cell>
   </Row>
   <Row>
    <Cell><Data >2</Data></Cell>
   </Row>
   <Row>
    <Cell><Data >3</Data></Cell>
    <Cell><Data >SummaryTable</Data></Cell>
   </Row>
   <Row>
    <Cell><Data >4</Data></Cell>
    <Cell><Data >A</Data></Cell>
   </Row>
   <Row>
    <Cell><Data >5</Data></Cell>
    <Cell><Data >B</Data></Cell>
   </Row>
   <Row>
    <Cell><Data >6</Data></Cell>
    <Cell><Data >C</Data></Cell>
   </Row>
   <Row>
    <Cell><Data >7</Data></Cell>
   </Row>
   <Row>
    <Cell><Data >8</Data></Cell>
   </Row>
   <Row>
</Table>

The problem is that I don't control how many rows will there be in the details table - it might be plenty to just do 'for-each' on them and add the extra cell elements at specific indexes by using the position().

However it might be that there are too few (e.g. just one row element) in which case the for-each would never iterate to indexes 4,5,6,7, so my summary table would not be printed - and the expected output for such case should be:

enter image description here

I was thinking about performing the for-each loop for a specified number of times (e.g. 7), so that I print out first seven rows along with the summary table and then performing the for-each loop again on the DetailsTable/row elements, but skipping first 7 index positions, but it seems like a nasty workaround.

Any other ideas?

Solution

  • One solution, although it doesn't look very elegant, is to have a recursive template, that increments a row number until it reaches the end of either the detail or summary rows, depending on which is greater.

    Try this XSLT:

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

  <xsl:param name="firstSummaryRow" select="4" />

  <xsl:variable name="lastDetailRow" select="1 + count(/*/DetailsTable/row)" />
  <xsl:variable name="lastSummaryRow" select="$firstSummaryRow + count(/*/SummaryTable/row)" />

  <xsl:template match="/*">
    <xsl:call-template name="Rows" />
  </xsl:template>

  <xsl:template name="Rows">
    <xsl:param name="rowNumber" select="1" />
    <Row>
      <Cell>
        <Data>
          <xsl:choose>
            <xsl:when test="$rowNumber = 1">Details Table</xsl:when>
            <xsl:otherwise>
              <xsl:value-of select="DetailsTable/row[position() = $rowNumber - 1]/@data" />
            </xsl:otherwise>
          </xsl:choose>
        </Data>
      </Cell>
      <xsl:if test="$rowNumber >= $firstSummaryRow and $rowNumber &lt;= $lastSummaryRow">
        <Cell Index="3">
          <Data>
            <xsl:choose>
              <xsl:when test="$rowNumber = $firstSummaryRow">Summary Table</xsl:when>
              <xsl:otherwise>
                <xsl:value-of select="SummaryTable/row[position() = $rowNumber - $firstSummaryRow]/@data" /> 
              </xsl:otherwise>
            </xsl:choose>
          </Data>
        </Cell>
      </xsl:if>
    </Row>
    <xsl:if test="$rowNumber &lt; $lastDetailRow or $rowNumber &lt; $lastSummaryRow">
      <xsl:call-template name="Rows">
        <xsl:with-param name="rowNumber" select="$rowNumber + 1" />
      </xsl:call-template>
    </xsl:if>
  </xsl:template>
</xsl:stylesheet>

    See it in action at http://xsltfiddle.liberty-development.net/pPzifpd