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[(_at_)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(_at_)bryantcs(_dot_)com]
Sent: 13 July 2006 18:02
To: xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
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 "group"</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
--~------------------------------------------------------------------
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail:
<mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--
--~------------------------------------------------------------------
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: <mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--