[XSL-LIST Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

Handling missing cells in Excel tables (2.0 solution)

Subject: Handling missing cells in Excel tables (2.0 solution)
From: "Jay Bryant" <jay@xxxxxxxxxxxx>
Date: Thu, 13 Jul 2006 12:01:55 -0500
excel max cells
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

Current Thread

PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Buy Stylus Studio Now

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Don't miss another message! Subscribe to this list today.
Email
First Name
Last Name
Company
Subscribe in XML format
RSS 2.0
Atom 0.3
Site Map | Privacy Policy | Terms of Use | Trademarks
Free Stylus Studio XML Training:
W3C Member
Stylus Studio® and DataDirect XQuery ™are products from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2013 All Rights Reserved.