Bug #113303 for OpenOffice-OODoc: weird behaviour with updateCell on libreoffice spreadsheet

Thu Mar 24 08:08:17 2016 andrew.billyard [...] gmail.com - Ticket created

Subject:	weird behaviour with updateCell on libreoffice spreadsheet
Date:	Thu, 24 Mar 2016 08:08:06 -0400
To:	bug-OpenOffice-OODoc [...] rt.cpan.org
From:	Andrew Billyard <andrew.billyard [...] gmail.com>

I'm trying to update a libreoffice spreadsheet (test.ods) with perl's module OpenOffice::OODoc, and I am coming across spurious behaviour which I'm not sure if it's from my poor understanding, a bug in OpenOffice::OODoc or simply an artifact with libreoffice. Here's the test code: use OpenOffice::OODoc; my $document=odfDocument(file=>'test.ods'); foreach my $rr (0..10){ $document->updateCell("Sheet1",$rr,0,$rr."0"); } $document->save; The code is simply meant to place into column A for the first 10 rows, the row number followed by a '0' (i.e., '00', '10', '20'...) *Before* this code is ran, test.ods was blank except for the number 999 placed in cells "C1", "B2 and "F3". The cell "A5" was selected. So roughly, it looked like: 999 999 999 *After* the perl code was ran, the spreadsheet looked like: 00 00 999 10 999 20 20 20 20 20 999 30 30 30 30 30 30 40 40 40 40 40 40 It seems like the line $document->updateCell(...); does not update just one cell, but rather all cells on that line, starting at the specified column and ending at either the first non-black cell or end of spreadsheet's extent in columns. Also, it only does this to up to row 5, where the cell is selected, presumably to the spreadsheet's extent in rows. What I am doing wrong? -perl version: 5.16.3 -OpenOffice::OODoc version: 2.125 -libreoffice version: 4.1.6.2

Thu Mar 24 10:09:01 2016 jmgdoc [...] cpan.org - Correspondence added

With OpenOffice::OODoc, any direct update in the content of a table can corrupt the data structure.
So, as soon as you get access to a spreadsheet in order to insert, delete, or update something in a table you must pre-process the table structure using normalizeSheet().

Here the appropriate doc entry:
http://search.cpan.org/dist/OpenOffice-OODoc/OODoc/Text.pod#normalizeSheet(sheet,_%27full%27)

If you need a more friendly and efficient ODF table processing API, you should consider switching from OpenOffice::OODoc to ODF::lpOD.

Le 2016-03-24 08:08:17, andrew.billyard@gmail.com a écrit :
Show quoted text

> I'm trying to update a libreoffice spreadsheet (test.ods) with perl's
> module OpenOffice::OODoc, and I am coming across spurious behaviour which
> I'm not sure if it's from my poor understanding, a bug in OpenOffice::OODoc
> or simply an artifact with libreoffice. Here's the test code:
>
> use OpenOffice::OODoc;
> my $document=odfDocument(file=>'test.ods');
> foreach my $rr (0..10){
> $document->updateCell("Sheet1",$rr,0,$rr."0");
> }
> $document->save;
>
> The code is simply meant to place into column A for the first 10 rows, the
> row number followed by a '0' (i.e., '00', '10', '20'...)
>
> *Before* this code is ran, test.ods was blank except for the number 999
> placed in cells "C1", "B2 and "F3". The cell "A5" was selected. So roughly,
> it looked like:
>
> 999
> 999
> 999
>
>
> *After* the perl code was ran, the spreadsheet looked like:
> 00 00 999
> 10 999
> 20 20 20 20 20 999
> 30 30 30 30 30 30
> 40 40 40 40 40 40
>
> It seems like the line $document->updateCell(...); does not update just one
> cell, but rather all cells on that line, starting at the specified column
> and ending at either the first non-black cell or end of spreadsheet's
> extent in columns. Also, it only does this to up to row 5, where the cell
> is selected, presumably to the spreadsheet's extent in rows.
>
> What I am doing wrong?
> -perl version: 5.16.3
> -OpenOffice::OODoc version: 2.125
> -libreoffice version: 4.1.6.2

Thu Mar 24 10:09:01 2016 The RT System itself - Status changed from 'new' to 'open'

Thu Mar 24 10:14:07 2016 jmgdoc [...] cpan.org - Status changed from 'open' to 'resolved'

Thu Mar 24 16:01:48 2016 andrew.billyard [...] gmail.com - Correspondence added

Subject:	Re: [rt.cpan.org #113303] weird behaviour with updateCell on libreoffice spreadsheet
Date:	Thu, 24 Mar 2016 16:01:33 -0400
To:	bug-OpenOffice-OODoc [...] rt.cpan.org
From:	Andrew Billyard <andrew.billyard [...] gmail.com>

Thank you, that worked! Cheers, Andrew Show quoted text

_________________ Andrew Billyard, so you don't have to be. On Thu, Mar 24, 2016 at 10:09 AM, Jean-Marie Gouarné via RT < bug-OpenOffice-OODoc@rt.cpan.org> wrote:

> <URL: https://rt.cpan.org/Ticket/Display.html?id=113303 > > > With OpenOffice::OODoc, any direct update in the content of a table can > corrupt > the data structure. > So, as soon as you get access to a spreadsheet in order to insert, delete, > or > update something in a table you must pre-process the table structure using > normalizeSheet(). > > Here the appropriate doc entry: > > http://search.cpan.org/dist/OpenOffice-OODoc/OODoc/Text.pod#normalizeSheet(sheet,_%27full%27) > > If you need a more friendly and efficient ODF table processing API, you > should > consider switching from OpenOffice::OODoc to ODF::lpOD. > > Le 2016-03-24 08:08:17, andrew.billyard@gmail.com a écrit :

> > I'm trying to update a libreoffice spreadsheet (test.ods) with perl's > > module OpenOffice::OODoc, and I am coming across spurious behaviour which > > I'm not sure if it's from my poor understanding, a bug in

> OpenOffice::OODoc

> > or simply an artifact with libreoffice. Here's the test code: > > > > use OpenOffice::OODoc; > > my $document=odfDocument(file=>'test.ods'); > > foreach my $rr (0..10){ > > $document->updateCell("Sheet1",$rr,0,$rr."0"); > > } > > $document->save; > > > > The code is simply meant to place into column A for the first 10 rows,

> the

> > row number followed by a '0' (i.e., '00', '10', '20'...) > > > > *Before* this code is ran, test.ods was blank except for the number 999 > > placed in cells "C1", "B2 and "F3". The cell "A5" was selected. So

> roughly,

> > it looked like: > > > > 999 > > 999 > > 999 > > > > > > *After* the perl code was ran, the spreadsheet looked like: > > 00 00 999 > > 10 999 > > 20 20 20 20 20 999 > > 30 30 30 30 30 30 > > 40 40 40 40 40 40 > > > > It seems like the line $document->updateCell(...); does not update just

> one

> > cell, but rather all cells on that line, starting at the specified column > > and ending at either the first non-black cell or end of spreadsheet's > > extent in columns. Also, it only does this to up to row 5, where the cell > > is selected, presumably to the spreadsheet's extent in rows. > > > > What I am doing wrong? > > -perl version: 5.16.3 > > -OpenOffice::OODoc version: 2.125 > > -libreoffice version: 4.1.6.2

> >