[basex-talk] Open Document spreadsheets and column position

[Graydon Saunders]

Hi all --

So in Open Document Spreadsheets, columns with empty cells may be collapsed:

<table:table-row table:style-name="ro2">
     <table:table-cell table:number-columns-repeated="3"
table:style-name="ce454" />
     <table:table-cell table:style-name="ce255" />
     <table:table-cell table:style-name="ce255" office:value-type="string">
      <text:p>label</text:p>
     </table:table-cell>
     <table:table-cell table:style-name="ce255" office:value-type="string">
      <text:p>description</text:p>
     </table:table-cell>
     <table:table-cell table:number-columns-repeated="4"
table:style-name="ce455" />
    </table:table-row>

Not all of the rows have have these collapsed empty cells; in some rows,
all the cells are present because they've got values in them.  And while I
wouldn't care in the example row, sometimes more than one table:table-cell
represents a collapsed column in a position I care about.

All the labels are in column D and all the descriptions in column E (in
spreadsheet terms), whether all the table:cell elements are present in the
XML representation of the sheet or not, and I need to reliably find them so
as to be able to convince myself that the XSLT transform is getting correct
answers.  (The spreadsheet is about 40 MiB; not so much by recent list
standards, but still more than I can hope to read. :)

declare function local:realPos($current as node())
as xs:integer
{
 (: the horror, the horror :)
 xs:integer(path($current) ! tokenize(.,'/')[last()] !
replace(.,'.*\[(\p{Nd}+)\]','$1') ! xs:integer(
.)
  +
sum($current/preceding-sibling::table:table-cell/@table:number-columns-repeated)
  -
count($current/preceding-sibling::table:table-cell/@table:number-columns-repeated)
)
};

works, in the sense of "will get the cell corresponding to the intended
spreadsheet column even when some of the columns have been collapsed with
@table:number-columns-repeated".

It's horribly slow and it hurts just to look at, though, so -- is there a
better way?

(The XSLT transform pre-processes all the collapsed table:table-cell
elements back into place, among other tidying.  I'm not at all sure that's
a better way with XQuery.)

Thanks!
Graydon