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

Question

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:

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:

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?

Isn't there some way to compute the (first) row you want to insert the summary table into based on the number of rows in the input tables? Or what determines the row(s) you want to insert the summary table into? What happens in the case you mention that there is just one row in the details table? How would the output look then? — Martin Honnen, Mar 05 '19 at 21:48
@MartinHonnen - Hi - the rows to be inserted to SummaryTable in Excel come from the SummaryTable in XML (which comes from DB query). The format and location of the SummaryTable is fixed. I have also updated the question with expected output for when there is just one details row. The summary should still be in the same place. — Bartosz, Mar 05 '19 at 21:58
You haven't shown your XSLT nor the XML output of all possible results but in general, in particular with XSLT 1, if you need to create result elements for which there are or for which there can be no input elements to be mapped/processed, you need to write some code as you outlined, that ensures you create your seven first rows and then processes any remaining rows.The usual way is to write a recursive template with a parameter to reduce and check the number of calls. — Martin Honnen, Mar 06 '19 at 09:55

score 2 · Accepted Answer · answered Mar 06 '19 at 09:56

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

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

1 Answers1