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

Subject: RE: [xsl] Handling missing cells in Excel tables (2.0 solution)
From: "Michael Kay" <mike@xxxxxxxxxxxx>
Date: Fri, 14 Jul 2006 04:00:22 +0100
I don't think your solution handles the ss:mergeAcross attribute, which can
appear on a cell that spans more than one column.

Instead of creating Cell elements to represent missing cells, I've tended to
compute the column number of the cells that do exist and add it as an
explicit attribute of the cell. Both techniques achieve the objective of
making it easy to address a cell given its column number. This can be done
using this kind of logic:

<xsl:template match="Row">
  <Row nr="{f:getRowNumber(.)}">
    <xsl:for-each-group select="Cell" group-starting-with="Cell[@ss:Index]">
      <xsl:variable name="start" select="(@ss:Index, 1)[1]"
as="xs:integer"/>
      <xsl:for-each select="current-group()">
        <xsl:variable name="mergeTotal"
select="sum(current-group()[current() >> .]/@ss:MergeAcross)"/>
        <xsl:if test="Data">
          <Cell col="{$start + $mergeTotal + position() - 1}">
            <xsl:value-of select="Data"/>
          </Cell>
        </xsl:if>
      </xsl:for-each>
    </xsl:for-each-group>
  </Row>
</xsl:template>

The key to this is that it treats the problem as a positional grouping
problem in which the group is a sequence of cells starting with one that has
an ss:Index attribute. I think you could adapt the technique to creating
empty cells by putting the logic to construct the empty cells at the start
of the for-each-group body.

Michael Kay
http://www.saxonica.com/

 

> -----Original Message-----
> From: Jay Bryant [mailto:jay@xxxxxxxxxxxx] 
> Sent: 13 July 2006 18:02
> To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx
> Subject: [xsl] Handling missing cells in Excel tables (2.0 solution)
> 
> 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