Excel XML Cell index problem

[Jesse Wu]

Hi Ken/Michael,

Thank you so much. These 2 programs are brilliant. I now realized that
not only XQuery I also need to polish up my XPATH and XSLT skills.

With kind regards,

-Jes

On Thu, Aug 20, 2009 at 5:50 PM, Michael Kay<http://x-query.com/mailman/listinfo/talk> wrote:
>
> It's a tough format to work with, this one. Generally it's best to
> preprocess it either
>
> (a) into a format where every Cell is explicitly present, or
>
> (b) into a format where every Cell is explicitly labelled with row and
> column attributes.
>
> The advantage is that this preprocessing is completely generic and reusable
> and can be done whatever processing you intend to do next.
>
> I found a neat way to add column numbers to all the cells using XSLT 2.0
> grouping capabilities. It's something like this:
>
> <xsl:for-each-group select="Cell" group-starting-with="*[@Index]">
>  <xsl:variable name="StartIndex" select="(@Index, 1)[1]"/>
>  <xsl:for-each select="current-group()">
>    <xsl:copy>
>       <xsl:attribute name="Index" select="$StartIndex + position() - 1"/>
>       <xsl:copy-of select="@* | node()"/>
>    </xsl:copy>
>  </xsl:for-each>
> </xsl:for-each-group>
>
>
> With XQuery it will need a recursive function. If you're comfortable with
> recursion it's probably not very difficult, except at this time of night.
>
> Regards,
>
> Michael Kay
> http://www.saxonica.com/
> http://twitter.com/michaelhkay
>
>> -----Original Message-----
>> From: http://x-query.com/mailman/listinfo/talk
>> [mailto:http://x-query.com/mailman/listinfo/talk] On Behalf Of Jesse Wu
>> Sent: 20 August 2009 20:16
>> To: http://x-query.com/mailman/listinfo/talk
>> Subject:  Excel XML Cell index problem
>>
>> Trying to evaluate if XQuery is the right tool to use to
>> process large Excel workbook, XLS when save as XML:
>> <Workbook><Worksheet><Table>
>> <Row>
>>  <Cell>AAA</Cell>
>>  <Cell Index="5" HRef= "http:??...">YYY</Cell>  .
>>  .
>>  <Cell Index="23">XXX</Cell>
>>  .
>> </Row>
>> <Row>
>> ...
>> </Row>
>>
>> My XQuery code is looping through each row then the cells in
>> each row, but unfortunately Microsoft Excel when save as XML
>> skips empty cells, as the example above in the 1st row, the
>> cells(columns) from 2 to 4 were missing before the 2nd cell
>> with Index="5" which means it's actually column 5 (I was
>> expecting to have 3 <Cell /> before it). Is there a way to
>> code this in Xquery?
>>
>> My code:
>>
>> let $pads : = 0
>> for $i in 1 to count($Row/Cell)
>> let $f := $i + $pads
>> return
>> <dummy>
>> {  xu:padcells($Row/Cell[$i], $f) }
>>
>> {$tr/Cell[$i]}
>>
>> </dummy>
>>
>> };
>>
>> declare function xu:padcells($cl, $f)
>> {
>> let $t := xs:integer($cl/@Index) -1
>> (: let $pads := $pads + ($f - $t )+ 1 is there a way to get
>> the total number of empty cells padded back to the calling
>> code? :) for $x in $f to $t return
>>       <Cell Index="{$x}"></Cell>
>> };
>>
>> Thanks in advance for all your help.
>>
>> -Jes
>> _______________________________________________
>> http://x-query.com/mailman/listinfo/talk
>> http://x-query.com/mailman/listinfo/talk
>
>