Re: [Exist-open] EXCEL XML cell index problem (possible bug in eXist)

[jessewu <jes...@gm...>]

Hi,

Ken Holman helped me put a program together to solve this problem, tested
working fine using Saxon. The following is the screen shot:

C:\Program Files\eXist\saxon>java net.sf.saxon.Query -s:idx.xml excel.xq
<?xml version="1.0" encoding="UTF-8"?>
<dummy>
<outCell outIndex="1">AAA</outCell>
<outCell outIndex="2"/>
<outCell outIndex="3"/>
<outCell outIndex="4"/>
<outCell outIndex="5">YYY</outCell>
<outCell outIndex="6">BBB</outCell>
<outCell outIndex="7">CCC</outCell>
<outCell outIndex="8"/>
<outCell outIndex="9"/>
<outCell outIndex="10"/>
<outCell outIndex="11"/>
<outCell outIndex="12">ZZZ</outCell>
<outCell outIndex="13">DDD</outCell>
<outCell outIndex="14"/>
<outCell outIndex="15"/>
<outCell outIndex="16"/>
<outCell outIndex="17"/>
<outCell outIndex="18"/>
<outCell outIndex="19"/>
<outCell outIndex="20"/>
<outCell outIndex="21"/>
<outCell outIndex="22"/>
<outCell outIndex="23">XXX</outCell>
</dummy>

C:\Program Files\eXist\saxon>type excel.xq
xquery version "1.0";

<dummy>{

(:Walk all cells in the row:)
for $c in Table/Row/Cell

(:determine count of immediately preceding cells without an index
attribute:)
let $preceding-non-indexed := count( $c/preceding-sibling::Cell[not(@Index)]
)
                             - count( $c/preceding-sibling::Cell[@Index][1]/
                                       preceding-sibling::Cell[not(@Index)]
)

(:which was the previously-specified index?:)
let $preceding-index := ( $c/preceding-sibling::Cell[@Index][1]/@Index,
                         0 )[1]

(:which was the last-specified index?:)
let $preceding-or-this-index := ( $c/@Index,
                                
$c/preceding-sibling::Cell[@Index][1]/@Index,
                                 0 )[1]
 return
   (
     (:first fill any absent cells:)
     for $fill-index in xs:integer( $preceding-index +
                                    $preceding-non-indexed +
                                    1 )
                        to
                        xs:integer( $preceding-or-this-index - 1 )
       return <outCell outIndex="{$fill-index}"/>,

     (:then put out this given cell with an appropriate index:)
     <outCell outIndex="{if( $c/@Index )
                           then (:index given:) $c/@Index
                           else (:index calculated :)
                                ( $preceding-index +
                                  $preceding-non-indexed +
                                  1)
                        }">{$c/node()}</outCell>
   )
}</dummy>

C:\Program Files\eXist\saxon>type idx.xml
<?xml version="1.0"?>
<Table>
<Row>
 <Cell>AAA</Cell>
 <Cell Index="5" HRef= "http:??...">YYY</Cell>
 <Cell>BBB</Cell>
 <Cell>CCC</Cell>
 <Cell Index="12" HRef= "http:??...">ZZZ</Cell>
 <Cell>DDD</Cell>
 <Cell Index="23">XXX</Cell>
</Row>
</Table>
C:\Program Files\eXist\saxon>
-----------------------------------------------------
When using eXist, the code:

xquery version "1.0";

declare option exist:serialize "media-type=text/xml indent=yes
omit-xml-declaration=no";
let $data := doc("idx.xml")
return
<dummy>{

(:Walk all cells in the row:)
for $c in $data/Table/Row/Cell
- Hide quoted text -

(:determine count of immediately preceding cells without an index
attribute:)
let $preceding-non-indexed := count( $c/preceding-sibling::Cell[not(@Index)]
)
                            - count( $c/preceding-sibling::Cell[@Index][1]/
                                      preceding-sibling::Cell[not(@Index)] )

(:which was the previously-specified index?:)
let $preceding-index := ( $c/preceding-sibling::Cell[@Index][1]/@Index,
                        0 )[1]

(:which was the last-specified index?:)
let $preceding-or-this-index := ( $c/@Index,
                               
$c/preceding-sibling::Cell[@Index][1]/@Index,
                                0 )[1]
 return
  (
    (:first fill any absent cells:)
    for $fill-index in xs:integer( $preceding-index +
                                   $preceding-non-indexed +
                                   1 )
                       to
                       xs:integer( $preceding-or-this-index - 1 )
      return <outCell outIndex="{$fill-index}"/>,

    (:then put out this given cell with an appropriate index:)
    <outCell outIndex="{if( $c/@Index )
                          then (:index given:) $c/@Index
                          else (:index calculated :)
                               ( $preceding-index +
                                 $preceding-non-indexed +
                                 1)
                       }">{$c/node()}</outCell>
  )
}</dummy>

output:
<?xml version="1.0" encoding="UTF-8"?>
<dummy>
   <outCell outIndex="1">AAA</outCell>
   <outCell outIndex="2"/>
   <outCell outIndex="3"/>
   <outCell outIndex="4"/>
   <outCell outIndex="5">YYY</outCell>
   <outCell outIndex="6">BBB</outCell>
   <outCell outIndex="7">CCC</outCell>
   <outCell outIndex="8"/>
   <outCell outIndex="9"/>
   <outCell outIndex="10"/>
   <outCell outIndex="11"/>
   <outCell outIndex="12">ZZZ</outCell>
   <outCell outIndex="8">DDD</outCell>
   <outCell outIndex="9"/>
   <outCell outIndex="10"/>
   <outCell outIndex="11"/>
   <outCell outIndex="12"/>
   <outCell outIndex="13"/>
   <outCell outIndex="14"/>
   <outCell outIndex="15"/>
   <outCell outIndex="16"/>
   <outCell outIndex="17"/>
   <outCell outIndex="18"/>
   <outCell outIndex="19"/>
   <outCell outIndex="20"/>
   <outCell outIndex="21"/>
   <outCell outIndex="22"/>
   <outCell outIndex="23">XXX</outCell>
</dummy>

May be it's a bug in eXist?

Thanks so much for your time.

Jesse


Wolfgang Meier-2 wrote:
> 
>> Thank you all for your reply. I think I need to call a function to pad
>> the
>> empty cells and keep track of how many empty cells has been padded.
>> Unless
>> XQuery allows global variable or function call supports call by
>> reference, I
>> am not sure how this can be done. Is there a known limitation of the
>> XQuery
>> language i.e. can this be done using XQuery or I have to use a 3rd party
>> tool as suggested by Dan.
> 
> XQuery is a powerful functional language. As Michael already pointed
> out, you need to become familiar with the functional programming
> style, which can be a bit confusing compared to a procedural approach.
> 
> Dan suggested using a 3rd party tool, not because XQuery is too
> limited for the job, but because the Excel format uses non-standard,
> wrong XML (as I understood it), which has to be preprocessed.
> 
> Wolfgang
> 
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008
> 30-Day 
> trial. Simplify your report design, integration and deployment - and focus
> on 
> what you do best, core application coding. Discover what's new with 
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Exist-open mailing list
> Exi...@li...
> https://lists.sourceforge.net/lists/listinfo/exist-open
> 
> 

-- 
View this message in context: http://www.nabble.com/EXCEL-XML-cell-index-problem-tp25028115p25135678.html
Sent from the exist-open mailing list archive at Nabble.com.