bugGNU Octave - Bugs: bug #60825, [octave forge] (io) io oct2xls...

 
 

bug #60825: [octave forge] (io) io oct2xls corrupts content.xml

Submitter:  Greg Holmberg <gholmberg>
Submitted:  Sat 26 Jun 2021 02:15:41 AM UTC
   
 
Category:  Octave Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Incorrect Result
Status:  Fixed Assigned to:  philipnienhuis
Originator Name:  Greg Holmberg Open/Closed:  * Closed
Release:  * other Operating System:  * Any
Fixed Release:  None Planned Release:  None
* Mandatory Fields

Add a New Comment Rich Markup
   

Jump to the original submission

Wed 28 Jul 2021 10:48:20 AM UTC, comment #10: 

Yeah my apologies, the OCT interface's oct2xls ignores (wipes) all formatting in processed worksheets for actually all supported outputs - .xlsx, .ods and .gnumeric. That is a known and practically unresolvable limitation, in the sense that it'll take an awful lot of work to get it properly working; it'd also severely affect performance. Given that there is a choice of ready-baked interfaces that behave better, IMO there's little use (in the sense of "re-inventing the wheel") in trying to also implement retaining formatting.

Anyway, good that you mention - I'll include a warning somewhere in the help texts about this. I should have done that long ago (shame on me).

IMO the OCT interface is very good for reading data (and for .xlsx much faster than the Java-based interfaces) and for just basic output.
For maintenance of and fiddling with formatted spreadsheets there are very good but much slower alternatives based on Java.
There's a lot to tell about how this situation came together but please don't get me started :-)

Thank you confirming the fix for the data corruption (let's ignore format corruption :-) ).

Given that what could be fixed has been fixed I'll close this report.

Philip Nienhuis <philipnienhuis>
Group Member
Tue 27 Jul 2021 11:19:53 PM UTC, comment #9: 

Well, that was interesting.  I can now open the .ods file after oct2xls and all the data/formulas appear to still be there and the updates were written (so that's good), but all visual formatting information has been lost.  So font, bold/italic/underline, font color, background color, column sizes, precision display, alignment, etc.  And not only in the updated range, but the entire worksheet.  Other worksheets were not affected.

So, better, but not there yet. :-(

I'm testing under Octave 6.1.1, io 2.6.3, linux 5.11 (Ubuntu 21.04), LibreOffice 7.1.4.2.

Greg Holmberg <gholmberg>
Mon 26 Jul 2021 10:28:23 AM UTC, comment #8: 

Thanks.
Well, trying the code from comment #0 with "before.ods" now works fine for me, using the patched file from comment #6 and the patched file from bug #60830 comment #2). Does it work for you as well?

I'm planning to make a new io package release (bug fix release) soon, so that would be nice to know. (Same for bug #60830, BTW)

If you find it too much of a hassle to swap those files I can upload a prerelease io-2.6.4 for you somewhere, with a time-bombed link.

Philip Nienhuis <philipnienhuis>
Group Member
Mon 26 Jul 2021 05:10:26 AM UTC, comment #7: 

Regarding comment #5, I apologize, I misspoke when I said that the script reproduces the problem with the attached file.  In fact, sample.ods shows the file after it is corrupted by oct2xls, not before.

I've attached a file "before.ods" that is the uncorrupted version. This was produced by invoking

    File > Download > OpenDocument format (.ods)

in Google Sheets.


(file #51690)

Greg Holmberg <gholmberg>
Sat 24 Jul 2021 11:21:52 PM UTC, comment #6: 

You happen to have hit a bug that must have been there from the very beginning when .ods was implemented for the OCT interface. Remarkable that it took so long for someone to stumble over it.

After I fixed it I hit another, known bug (that I can't find back now) relating to ftruncate() not being implemented. That induces trailing junk after an XML closing tag if an updated XML file (overwritten in place) happens to be shorter than the original one. I simply have the slack padded with spaces for the time being until ftruncate (ever) gets implemented; but TBH I fear that provisional fix will have a long lifetime.

The combined fix is here:
http://hg.code.sf.net/p/octave/io/rev/8edff1e5d7ad
It works fine for me now with your file "guitar@data.ods" from bug #53298.
If you want to try yourself, please swap __OCT_oct2ods__.m (also attached) in the io packages private/ subdir.

Setting status to "Ready For Test" for a while.


(file #51687)

Philip Nienhuis <philipnienhuis>
Group Member
Sat 24 Jul 2021 06:13:52 PM UTC, comment #5: 

The attached file 'sample.ods' can't be opened with any of the COM (= Excel), UNO (=LibreOffice), JOD and/or OCT interfaces.
Can you provide a better .ods file, please?

In the mean time I'll try to proceed with guitar2data.ods from bug #53298, but that's a rather complicated file judging by the nr. of declared ranges.

Philip Nienhuis <philipnienhuis>
Group Member
Sun 27 Jun 2021 09:56:23 PM UTC, comment #4: 

Philip, thank you for taking the time out of your vacation to look into this!

I worked around this problem by using UNO.  Since I already had LibreOffice 7.0 installed, it was easy to configure Octave to use it on Linux:

chk_spreadsheet_support ('', 0, '/usr/lib/libreoffice')

I've found the performance to be quite good.

It also lets me read/write .xlsx files, so works around the problem OCT has with those.

The only drawback is, it sometimes causes LibreOffice to exit!

Someday I hope to be able to write a LibreOffice macro to save the spreadsheet file, run the Octave script to update the spreadsheet, and reload the file.  Practically like the built-in recalc.  So, causing LibreOffice to exit will be a problem.  I may have to look into JOD...

Thanks again,

Greg

Greg Holmberg <gholmberg>
Sun 27 Jun 2021 08:46:26 PM UTC, comment #3: 

Over in bug #53298 I wrote:

> I tried your code on windows with dev Octave and io-2.6.3 but there it immediately stumbled over the "@" character in the file name. After changing the "@" into a "_" it hit a bug with the zip function ("no files to move"). That's where it ends for the time being as I have no time to look into it until well after mid-July, sorry.


Changing L.53 of __OCT_spsh_close__.m in the io package's private folder

[mvst, msg] = movefile (["." filesep fname ".zip"], filename, "f");

into

[mvst, msg] = movefile ([fname ".zip"], filename, "f");

solves the issue with zip.
ISTR a recent bug report or fix where s/th like this (prepending '.\') was also mentioned.

Anyway, with that newly written file I can confirm this bug report. The content.xml inside it is corrupt.
That's as far as I can get for the time being.

For the moment/as a workaround you can use a Java-based ods I/O interface; I'd advise jOpenDocument - AFAIK the fastest of all .ods I/O options in the io package and IMO better than the OCT interface for .ods anyway.

Philip Nienhuis <philipnienhuis>
Group Member
Sat 26 Jun 2021 09:28:54 PM UTC, comment #2: 

Notes about my environment.


ver
----------------------------------------------------------------------
GNU Octave Version: 6.2.0 (hg id: 5cfdf97c5350)
GNU Octave License: GNU General Public License
Operating System: Linux 5.8.0-59-generic #66-Ubuntu SMP Thu Jun 17 00:46:01 UTC 2021 x86_64
----------------------------------------------------------------------
Package Name  | Version | Installation directory
--------------+---------+-----------------------
     control  |   3.2.0 | /home/greg/snap/octave/78/octave/control-3.2.0
          io *|   2.6.3 | /home/greg/snap/octave/78/octave/io-2.6.3
      signal  |   1.4.1 | /home/greg/snap/octave/78/octave/signal-1.4.1



version -java
ans = no usable Java Runtime Environment (x86_64) found:
javaMethod: support for Java was unavailable or disabled when Octave was built



chk_spreadsheet_support([], 2)

(OCT interface... OK, included in io package)

1. Checking Octave's Java support... none.
This Octave has no built-in Java support. Skipping Java checks
Only ODS 1.2 (.ods) & OOXML (.xlsx) & .gnumeric r/w support present
ans = 0


Greg Holmberg <gholmberg>
Sat 26 Jun 2021 08:57:13 PM UTC, comment #1: 
Philip Nienhuis <philipnienhuis>
Group Member
Sat 26 Jun 2021 02:15:41 AM UTC, original submission:  

I have an .ods file created by Google Sheets (File > Download > OpenDocument format (.ods)).  I unzipped it, and I see that the meta.xml file says:


office:version="1.2">
<office:meta>
<meta:generator>LibreOfficeDev/6.0.5.2$Linux_X86_64 LibreOffice_project/


After download, I can open the file in LibreOffice 7.0.6.2 on Ubuntu 20.10 (Linux 5.8.0).

However, if I try to write data to it with oct2xls, and then open in LibreOffice, LibreOffice says:


Read error.
Format error discovered in the file in sub-document content.xml at 2,111654(row,col).


The following script reproduces the problem with the attached .ods file.  I'm using Octave 6.2.0 and io 2.6.3.


worksheet="Modeling";
xls = xlsopen("sample.ods", true);
[cells, xls, status] = xls2oct(xls, worksheet);
[xls, status] = oct2xls(cells, xls, worksheet);
xls = xlsclose(xls);


Greg Holmberg <gholmberg>

 

(Note: upload size limit is set to 16384 kB, after insertion of the required escape characters.)

Attach Files:
   
   
Comment:
   

Attached Files
file #51690:  before.ods added by gholmberg (24KiB - application/vnd.oasis.opendocument.spreadsheet)
file #51687:  __OCT_oct2ods__.m added by philipnienhuis (11KiB - text/plain)
file #51599:  sample.ods added by gholmberg (24KiB - application/vnd.oasis.opendocument.spreadsheet)

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by philipnienhuis
  • -email is unavailable- added by gholmberg (Submitted the item)
  • -email is unavailable- added by gholmberg
  •  

    There are 0 votes so far. Votes easily highlight which items people would like to see resolved in priority, independently of the priority of the item set by tracker managers.

    Only group members can vote.

     

    Follow 14 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2021-07-28 philipnienhuis StatusReady For Test Fixed
        Open/ClosedOpen Closed
    2021-07-26 gholmberg Attached File- Added before.ods, #51690
    2021-07-24 philipnienhuis Attached File- Added _OCT_oct2ods_.m, #51687
        StatusNeed Info Ready For Test
        Release6.2.0 other
    2021-07-24 philipnienhuis Assigned toNone philipnienhuis
    2021-07-24 philipnienhuis StatusConfirmed Need Info
    2021-06-27 philipnienhuis StatusNone Confirmed
        Operating SystemGNU/Linux Any
    2021-06-26 philipnienhuis Summaryio oct2xls corrupts content.xml [octave forge] (io) io oct2xls corrupts content.xml
        Carbon-Copy- Added philipnienhuis
    2021-06-26 gholmberg Attached File- Added sample.ods, #51599
        Carbon-Copy- Added gholmberg

    Back to the top

    Powered by Savane 3.13-d3ae.
    Corresponding source code