[xsl] Handling missing cells in Excel tables (2.0 solution)

["Jay Bryant" <jay@xxxxxxxxxxxx>]

Hi, gang,

Yesterday, a client gave me the problem of converting an Excel file into a
DocBook file. After saving as XML, the rest of the process boiled down to a
straightforward transform, with one sticky bit. As many of you know (because
it's been on the list a number of times), Excel doesn't add Cell elements
for empty table cells. Instead, it adds an Index attribute to the next Cell
that has content.

So, the resulting data looks something like this:

   <Row ss:AutoFitHeight="0">
    <Cell><Data ss:Type="String">text</Data><NamedCell
ss:Name="area_range"/></Cell>
    <Cell><Data ss:Type="String">Formating information in following row
&quot;group&quot;</Data><NamedCell
      ss:Name="area_range"/></Cell>
    <Cell ss:Index="11" ss:StyleID="s21">And so on</Cell>
   </Row>

I searched Google and the FAQ for ideas. Mike Kay and Joris Gillis had
solutions, and thanks to both for that. However, their solutions didn't
quite suit me. Also I always feel that I don't truly understand a problem
until I develop my own solution to it. So, here's my take on how to convert
Excel's odd XML output to something easier for XSL to process:

<xsl:template match="ss:Row">
  <row>
    <!-- You can determine the value of max-cells programmatically.
         I happened to be dealing with a fixed-width table -->
    <xsl:call-template name="make-cell">
      <xsl:with-param name="cell" select="ss:Cell[1]"/>
      <xsl:with-param name="max-cells" select="10"/>
    </xsl:call-template>
  </row>
</xsl:template> <!-- match="ss:Row" -->

<!-- The algorithm:
     * If the cell has an Index attribute, create
       the proper number of empty cells and
       process the current cell. Then, if another
       cell exists, call the template again. If
       no other cell exists, add the proper
       number of empty cells to the end
       of the row.
     * If the cell does not have an Index
       attribute, process the current cell. Then,
       if another cell exists,  call the template
       again. If no other cell exists, add the
       proper number of empty cells to the end
       of the row. -->
<xsl:template name="make-cell">
  <xsl:param name="cell"/>
  <xsl:param name="count" select="1"/>
  <xsl:param name="max-cells"/>
  <xsl:for-each select="$cell">
    <xsl:choose>
      <xsl:when test="@ss:Index">
        <xsl:for-each select="xs:integer($count) to xs:integer(@ss:Index -
1)">
          <entry/>
        </xsl:for-each>
        <entry><xsl:value-of select="ss:Data"/></entry>
        <xsl:choose>
          <xsl:when test="following-sibling::ss:Cell">
            <xsl:call-template name="make-cell">
              <xsl:with-param name="cell"
select="following-sibling::ss:Cell[1]"/>
              <xsl:with-param name="count" select="@ss:Index + 1"/>
              <xsl:with-param name="max-cells" select="$max-cells"/>
            </xsl:call-template>
          </xsl:when>
          <xsl:otherwise>
            <!-- I didn't put an if statement here because
                 the first test of the for-each condition
                 catches the case where ss:@Index = $max-cells.
                 So, an if statement would be redundant. -->
            <xsl:for-each select="xs:integer(@ss:Index + 1) to
xs:integer($max-cells)">
              <entry/>
            </xsl:for-each>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:when>
      <xsl:otherwise>
        <entry><xsl:value-of select="ss:Data"/></entry>
        <xsl:choose>
          <xsl:when test="following-sibling::ss:Cell">
            <xsl:call-template name="make-cell">
              <xsl:with-param name="cell"
select="following-sibling::ss:Cell[1]"/>
              <xsl:with-param name="count" select="$count + 1"/>
              <xsl:with-param name="max-cells" select="$max-cells"/>
            </xsl:call-template>
          </xsl:when>
          <xsl:otherwise>
            <!-- I didn't put an if statement here because
                 the first test of the for-each condition
                 catches the case where $count = $max-cells.
                 So, an if statement would be redundant. -->
            <xsl:for-each select="xs:integer($count + 1) to
xs:integer($max-cells)">
              <entry/>
            </xsl:for-each>
          </xsl:otherwise>
        </xsl:choose>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:for-each>
</xsl:template>

Note that a cell in a DocBook table is called an entry.

Also, you'll need xmlns:xs="http://www.w3.org/2001/XMLSchema"; in the
stylesheet element.

FWIW

Jay Bryant
Bryant Communication Services