bugGNU Octave - Bugs: bug #53298, [octave forge] (io) OCT interface...

 
 

bug #53298: [octave forge] (io) OCT interface xlswrite wipes previous text cell contents

Submitter:  None
Submitted:  Tue 06 Mar 2018 09:05:32 PM UTC
   
 
Category:  Octave Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Incorrect Result
Status:  Fixed Assigned to:  philipnienhuis
Originator Name:  grg Originator Email:  -email is unavailable-
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

Sun 27 Jun 2021 07:38:25 AM UTC, comment #17: 

See bug #60825 for follow-up on comments 15 and up.

Philip Nienhuis <philipnienhuis>
Group Member
Sat 26 Jun 2021 08:29:11 PM UTC, comment #16: 

Which Octave version, which io package version, which spreadsheet I/O interface?
If it is "oct", I'd suggest to try the jOpenDocument interface ("jod"), IMO the best (& fastest) Java-based one for .ods. See the help for xlsopen / xlsread for more details; there's also an html doc in the io package's doc/ subdir.

The original bug report was about .xlsx files, not .ods.
I only have vague reminiscence about how it got fixed, let alone if I did fix it or if it got silently fixed by changes in core Octave. I can't find pertinent csets in the log (http://hg.code.sf.net/p/octave/io).
Furthermore ATM it is unclear if your issue is the same bug; maybe it's a different issue - .ods I/O using the oct interface hasn't been tested in the wild quite as much as .xlsx.

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.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 18 Jun 2021 08:10:46 PM UTC, comment #15: 

Not sure if this is the same problem, but I have an .ods file created by Google Sheets (File > Download > OpenDocument format (.ods)).  After download, I can open the file in LibreOffice 7.0.6.2 on Ubuntu 20.10 (Linux 5.8.0).

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,356968(row,col).

Here's a simple script to re-create it.  I have also attached the .odf file, "guitar data.ods".

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


Greg Holmberg <gholmberg>
Sun 22 Apr 2018 05:59:24 PM UTC, comment #14: 

AFAICS this bug has been fixed.
I've put io-2.4.11 on the package release tracker, hopefully the new release is made soon.

Philip Nienhuis <philipnienhuis>
Group Member
Wed 14 Mar 2018 07:42:08 PM UTC, comment #13: 

After a bit of thinking over I'll try to fix it in a more robust way.
Elsewehere in the io package's spreadsheet code there may be more constructs like the one at hand that may fail similarly.

Philip Nienhuis <philipnienhuis>
Group Member
Tue 13 Mar 2018 05:30:04 PM UTC, comment #12: 

The fix is more involved than I hoped; what's missing is a way to truncate the file. A feature request has been entered (see bug #53337).

In the mean time I'll upload a workaround to the io package tomorrow.

Philip Nienhuis <philipnienhuis>
Group Member
Mon 12 Mar 2018 08:52:21 PM UTC, comment #11: 

OK, confirmed as well.

What happens is that a private subfunction rewrites content.xml but the new contents are shorter than the original file, so after the new closing tag there's cruft left that screws up xml parsers.
Looks like an easy fix.

Philip Nienhuis <philipnienhuis>
Group Member
Sun 11 Mar 2018 11:03:25 PM UTC, comment #10: 

sorry I forgot the attachments:

tmp.ods is the template that I made from tmp.xls using libreoffice.

tmp_results.ods is what I get after running the osdwrite command reported earlier.


(file #43518, file #43519)

Giorgio <grg>
Sun 11 Mar 2018 09:01:34 PM UTC, comment #9: 

? I don't see attached tmp.ods nor fnout ?

Or is tmp.ods the same as tmp.xlsx? If so, how did you make it?

Philip Nienhuis <philipnienhuis>
Group Member
Sun 11 Mar 2018 08:20:15 PM UTC, comment #8: 

Apologies, I meant odswrite.m not ods2oct.m

I tried to write the same OUT_olci array into tmp.ods (attached) using this command:


status = odswrite(fnout, OUT.olci.avg', input_parameters.sheet, 'B5:H5');


but when I then tried to open the tmp.ods file I got this error message:

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


Giorgio <grg>
Fri 09 Mar 2018 08:19:55 PM UTC, comment #7: 

In the mean time I pushed the -essentially one-character- .xlsx fix here:
http://hg.code.sf.net/p/octave/io/rev/35cd7e7a276e

I'll leave this report open for a few days or until I get feedback from the ods2oct issue (comment #4), whichever comes first.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 09 Mar 2018 12:54:45 PM UTC, comment #6: 

Would you be able to provide a bit more detail as to waht errors you got with oct2ods.m?
I've looked into the .ods write stuff but can't find anything that might go wrong in a similar way / related way as with .xlsx files.

Philip Nienhuis <philipnienhuis>
Group Member
Thu 08 Mar 2018 06:36:53 PM UTC, comment #5: 

oct2ods? good that you mention, I'll have a look at that as well.

Philip Nienhuis <philipnienhuis>
Group Member
Thu 08 Mar 2018 02:46:21 PM UTC, comment #4: 

Yes that fixed it: thanks!

By the way, I think I had tried also oct2ods.m and I had a similar problem.

Thanks again,
grg

Giorgio <grg>
Thu 08 Mar 2018 01:37:26 PM UTC, comment #3: 

This bug is related to a rare occurrence of nested and then specially decorated substrings that wasn't accounted for as we hadn't met it in the wild until now.  So the test files you uploaded were extremely helpful if not vital for the fix :-)

I have a fix (just one character somewhere :-) ), I'll push it later this week.

But first, please try the following and report back if it fixes your problem:

Swap the attached file __OCT_oct2xlsx__.m in place of the existing one.
To find where it lives:
In Octave type:
loc = strrep (which ("oct2xls"), "oct2xls.m", "private")

If it works the above procedure is the proposed temporary solution until a new io package release is out.


(file #43490)

Philip Nienhuis <philipnienhuis>
Group Member
Wed 07 Mar 2018 08:17:34 AM UTC, comment #2: 

Q.: How was the original Excel file made? (with what program?)

I do not know what program exactly was used to make the very original file (I got it from somebody else). However, I do not use Excel so I converted the original xls file into an xlsx using libreoffice (5.1.6.2.0 10(Build:2))


Giorgio <grg>
Tue 06 Mar 2018 10:19:21 PM UTC, comment #1: 

Confirmed.

What I see when opening the file with Excel 2013 is complaints about SharedStrings.xml and a missing xml closing tag.
FYI, SharedStrings.xml (a file in the zip archive that any .xlsx file really is) contains most of the text cell contents in he entire .xlsx file.
My analysis is that SharedStrings.xml has been garbled by xlswrite and is unrecognized and as a consequence all strings (text cell contents) in the workbook have disappeared from sight.

I'll look into it.

Q.: How was the original Excel file made? (with what program?)

I have adapted the title - it is uncertain if merged cells have anything to do with this.

Philip Nienhuis <philipnienhuis>
Group Member
Tue 06 Mar 2018 09:05:32 PM UTC, original submission:  

File tmp.xlsx contains two worksheets ("Irradiance" and "low radiance_222").

Both worksheets contain merged cells and pre-existing information in several cells (merged and not).

When I try to write data from octave in range B5:H5 of worksheet "Irradiance", xlswrite wipes out all the information contained in the cells where I am not writing.

Interestingly, xlswrite also wipes out all pre-existing data from worksheet "low radiance_222", but keeps the existing info in range B5:H5.

I use the following commands:


OUT_olci = [4.541      8.5416      14.437      24.884      40.084       51.27      55.424];
input_parameters.sheet = "Irradiance";
fnout = "tmp.xlsx";
status = xlswrite(fnout, OUT_olci, input_parameters.sheet, 'B5:H5')


I'm attaching the following files:
1) tmp.xlsx
2) tmp_result.xlsx (what I get)
3) tmp_expected.xlsx (what I'm expecting).

Thanks for any help you may provide.
grg
 

Anonymous

 

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

Attach Files:
   
   
Comment:
   

Attached Files
file #51583:  guitar@data.ods added by gholmberg (59KiB - application/vnd.oasis.opendocument.spreadsheet)
file #43518:  tmp.ods added by grg (14KiB - application/vnd.oasis.opendocument.spreadsheet)
file #43519:  tmp_result.ods added by grg (19KiB - application/vnd.oasis.opendocument.spreadsheet)
file #43478:  tmp.xlsx added by None (12KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #43479:  tmp_expected.xlsx added by None (12KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #43480:  tmp_result.xlsx added by None (10KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -email is unavailable- added by gholmberg (Posted a comment)
  • -email is unavailable- added by grg (Posted a comment)
  • -email is unavailable- added by jwe (Updated the item)
  • -email is unavailable- added by None (Submitted the item)
  •  

    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 16 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2021-06-18 gholmberg Attached File- Added guitar@data.ods, #51583
    2018-04-22 philipnienhuis StatusReady For Test Fixed
        Open/ClosedOpen Closed
    2018-03-11 grg Attached File- Added tmp.ods, #43518
        Attached File- Added tmp_result.ods, #43519
    2018-03-08 philipnienhuis StatusConfirmed Ready For Test
    2018-03-08 philipnienhuis Attached File- Added _OCT_oct2xlsx_.m, #43490
    2018-03-07 jwe Summary(octave-forge) io: (OCT interface) xlswrite wipes previous text cell contents [octave forge] (io) OCT interface xlswrite wipes previous text cell contents
    2018-03-06 philipnienhuis StatusNone Confirmed
        Assigned toNone philipnienhuis
        Release4.2.1 other
        Operating SystemGNU/Linux Any
        Summaryxlswrite fails when merge cells exist in worksheet(s) (octave-forge) io: (OCT interface) xlswrite wipes previous text cell contents
    2018-03-06 None Attached File- Added tmp.xlsx, #43478
        Attached File- Added tmp_expected.xlsx, #43479
        Attached File- Added tmp_result.xlsx, #43480

    Back to the top

    Powered by Savane 3.13-f8d8.
    Corresponding source code