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

 
 

bug #53459: [octave forge] (io) 2.4.10 function xlsread, results in unexpected error reading Excel (xlsx) file

Submitted by:  None
Submitted on:  Sun 25 Mar 2018 07:22:51 AM UTC  
 
Category:  Octave Forge Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Unexpected Error
Status:  Fixed Assigned to:  Philip Nienhuis <philipnienhuis>
Originator Name:  Werner Simbuerger Originator Email:  -email is unavailable-
Open/Closed:  Closed Release:  other
Operating System:  Any

Add a New Comment(Rich Markup)
   

You are not logged in

Please log in, so followups can be emailed to you.

 

( Jump to the original submission)

Tue 17 Apr 2018 08:00:54 PM UTC, comment #29:

After a little more testing I think this bug has been fixed.

Thanks Werner for your cooperation.

I've put io-2.4.11 release on the package release tracker. Hopefully it is in time for octave-4.4.0

Closing report

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 13 Apr 2018 05:12:41 PM UTC, comment #28:

@Philip: GOOD NEWS

At least my recent most important Octave functions for different raw data evaluation applications deliver correct results using your pre-release "io-2.4.11.tar.gz" :-) :-)

I think this bug fix enables the data extraction capability of 'xlsread()' significant - congratulations!

Further testing and bug finding will require new test cases and new functions, which will come over time.

I look forward to your new official 2.4.11 release.

Best regards,
Werner

Werner Simbuerger <q52y9rilim22nu2p>
Fri 13 Apr 2018 02:52:48 PM UTC, comment #27:

@Philip: yes, please provide a prerelease of "io-2.4.11.tar.gz" for testing

Werner Simbuerger <q52y9rilim22nu2p>
Fri 13 Apr 2018 11:35:41 AM UTC, comment #26:

OK, as to the calcChain.xml issues:
Turns out that only MS-Excel ever uses this file, and regenerates it if the file is missing in the archive. So just wiping it from the archive solves this last issue.
(Now, if all bugs could be solved this easily ...)

AFAICS I've fixed all bugs and errors related to this report.

@Werner: would you like to try an io-2.4.11 "prerelease" for me?

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 13 Apr 2018 07:32:11 AM UTC, comment #25:

It is easy to install a Java JRE. Then you can already use the UNO interface that invokes LibreOffice) or maybe even Apache POI - the latter is in some form present in many distro repos.

But OCT is (only for .xlsx) a lot faster than any of those. For .ods OCT is a pathetically slow. I'd advise jOpendocument for .ods.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Thu 12 Apr 2018 09:57:15 PM UTC, comment #24:

PS: I always use the OCT interface because it is the only one which works out of the box on Linux (Mint in my case) as well as Windows 7/10

Werner Simbuerger <q52y9rilim22nu2p>
Thu 12 Apr 2018 09:33:55 PM UTC, comment #23:

The read errors with "testcase3.xlsx" were caused by a very different and concealed bug, namely assuming that the "dimension" node (that holds a sheet's occupied data range) is in the first 512 bytes of a worksheet's XML. 'Normal' XML complie to that assumption.
I quickly found that in (private) function getusedrange.m the read "dimension" node was empty. Then it occurred to me that I saw earlier on that your xlsx files have exceptionally long header nodes. So amending that piece of code to read further than 512 bytes solved the new issue.

FYI, after the fix, xlsfinfo.m showed to have no problems reading unicode, neither in sheet names nor in Named Ranges; some excerpts are below in the verbatim block. xlsread has no problems either.

Only thing left is to find out what happens to xl/calcChain.xml when writing into complicated sheets. Tomorrow I may have some time left for that.
Again, only Excel complains, LibreOffice happily swallows files with new data added to existing complex sheets.

I must say (repeating myself) that maintaining this OOXML stuff takes an undue amount of time; however a personal pitfall is that I like these sort of puzzles. But there is an end to my patience. After all, the OCT interface doesn't quite perform bad at all I think.
Once I can push the new changesets and fixes I'll try to make a new io package release; hopefully this weekend. If you would like to test a new io "pre"release before it is officially out, just mention and I'll email it Friday.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Thu 12 Apr 2018 08:22:06 PM UTC, comment #22:

@Philip: I think if the basic types of sheets:

file #43669: 500V_SHORT.xlsx
file #43872: 1kV_SHORT.xlsx
file #43874: parameter.xlsx

are handled correctly we have achieved a great step which is worth to upload for the next revision of the package.

Werner Simbuerger <q52y9rilim22nu2p>
Thu 12 Apr 2018 08:04:58 PM UTC, comment #21:

@Werner:
Thanks for testing, I'll look into the new <<Testcase3.xlsx>> file.
From what you write in comment #19 it looks to be due to unicode issues; that is a weakness in octave itself rather than the io package. But we'll see.

I just fixed writing new sheets to your original 500V_SHORT.xlsx file; turned out I had most of the bookkeeping right, but I never had hit xlsx files with chartsheets and pivottables etc. (although I regularly use those at work - but I don't quite need let alone invoke Octave to analyze hours spent on projects by my colleagues). In the end it only required minor fixes.

Another issue is that writing data into existing files screws up xl/calcChain.xml; I have no idea why, maybe I'll look into that later.

Oh and I'll try pushing all the new fixes to sourceforge again; it's grown into a nice stack of csets right now :-)

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Thu 12 Apr 2018 07:40:08 PM UTC, comment #20:

@Philip: (file #43884, file #43885) work so far,
at least no error messages.

I did create another testcase <<Testcase3.xlsx>> which is a crowded Excel file with lot of special characters and lots of formulas etc.

I figured out that sheet no. 4 (4657H6B54) was not extracted in txtarr and nummarr accordingly. Maybe the other sheets fail too.
To be investigated.

Maybe, in general, the code of the io package is not able to handle such crowded sheets yet for correct import.

(file #43903)

Werner Simbuerger <q52y9rilim22nu2p>
Wed 11 Apr 2018 09:05:22 PM UTC, comment #19:

@Werner:
As to the files mentioned in comment 318: could be a stray "keyboard" in there, just enter "dbcont" then.

I've largely fixed the rest (exploring, writing), new versions will come later.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Wed 11 Apr 2018 07:45:53 PM UTC, comment #18:

@Werner, another attempt.
I think I finally grasped how the workbook file, various sheets + sheet types and relative ids stick together.

As Sourceforge's code section is largely inoperative ATM I cannot push the fixes now; I also have other pending patches for the COM interface since yesterday.
So I provisionally upload the private files__OCT_xlsx2oct__.m and _OCT_spsh_open_.m, please test reading xlsx files using the OCT interface with thosenew versions. Please put them in the io package's private/ subdir.
AFAICS they work properly for me now.

(Next up: exploring (xlsfinfo) and writing to complex xlsx files).

(file #43884, file #43885)

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Tue 10 Apr 2018 08:44:04 PM UTC, comment #17:

Thanks, I see what's happening.
I hope you don't mind helping me a bit by trying out new versions.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Tue 10 Apr 2018 07:48:36 PM UTC, comment #16:

@Philip: thanks, but now I get errors with a simple Excel file:

Please see <<parameter.xls>>

[numarr,txtarr,rawarr,limits]=xlsread('parameter.xlsx',1,[],'OCT');

Checking requested interface(s):

OCT*; (* = default interface)
error: Couldn't open worksheet xml file sheet
error: called from
_OCT_xlsx2oct_ at line 65 column 7
xls2oct at line 203 column 27
xlsread at line 238 column 29
main at line 9 column 29

Best regards,
Werner

(file #43874)

Werner Simbuerger <q52y9rilim22nu2p>
Tue 10 Apr 2018 06:18:29 PM UTC, comment #15:

@Werner:
You took the wrong commit, the one for writing that is only a partial fix.

You'd need to take the two commits in comment #11.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Tue 10 Apr 2018 04:04:40 PM UTC, comment #14:

@Philip: I did download the latest commit:
http://hg.code.sf.net/p/octave/io/rev/2cb3c1587b2d

Please see also:
<<1kV_SHORT.xlsx>>

I get the following errors again:

[numarr,txtarr,rawarr,limits]=xlsread('1kV_SHORT.xlsx','Voltage Wfms Data',[],'OCT');

Checking requested interface(s):

OCT*; (* = default interface)
error: Couldn't open worksheet xml file sheet
error: called from
_OCT_xlsx2oct_ at line 57 column 5
xls2oct at line 203 column 27
xlsread at line 238 column 29
main at line 9 column 29

It is not possible to read in specific sheets with name or number yet.

Regards, Werner

(file #43872)

Werner Simbuerger <q52y9rilim22nu2p>
Mon 09 Apr 2018 10:02:57 PM UTC, comment #13:

(Hey a post of mine went missing....)

@Werner:
Just grab the files from the last 3 commits (they're all from io's private/ subdir except NEWS) and report back if they work fine for you.
Writing is still a problem, I know.

If you see problems, please upload the most simple .xlsx file that shows the issue.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Mon 09 Apr 2018 09:59:49 PM UTC, comment #12:

Last 3 commits, it seems the writing partial patch also made it into the repo: :-)
http://hg.code.sf.net/p/octave/io/rev/2cb3c1587b2d

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Mon 09 Apr 2018 09:49:19 PM UTC, comment #11:

I've pushed the read and explore patches here:
http://hg.code.sf.net/p/octave/io/rev/2fb5cbd1eba7
http://hg.code.sf.net/p/octave/io/rev/1a22c5ce6165

What I have done for writing (just a partial fix) will be pushed as well (as soon as SourceForge is more responsive); as long as no new sheets are added Excel only may complain about xl/calcChain.xml.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Mon 09 Apr 2018 09:43:46 PM UTC, comment #10:

@Philip, Markus,

I figured out that your upload file '__OCT_xlsx2oct__.m' from Markus did solve the problem for '500V_SHORT.xlsx'. But for simple other standard Excel files now it fails. The original io 2.4.10 does not fail for such simple Excel files.

So, if you upload an updated version of '__OCT_xlsx2oct__.m' I would test it with different cases of Excel files.

Regards, Werner

Werner Simbuerger <q52y9rilim22nu2p>
Mon 09 Apr 2018 08:57:22 PM UTC, comment #9:

As I feared this is getting extremely complicated.

I got reading the files a la the example 500V_SHORT.xlsx running fine, plus fixed xlsfinfo.m for such files. That part wasn't too hard, yet much more involved than the patch by Markus.

But writing to this complicate type of file is a real PITA;

  • Just adding a value randomly in an empty cell risks screwing up calcChain.xml as apparently that supposedly empty cell may be part of some calc range. Excel complains, LibreOffice doesn't but that may be an LO bug.
  • Adding new worksheets within a mixed stack of chartsheets, macrosheets and plain worksheets requires extensive surgery in the internal XML file bookkeeping, a part that is already extremely complicated.

I'm thinking of just committing the changes for reading (xlsread) and exploring (xlsfinfo) and leave it at that for the time being.
Anyway I'd also like to make a new io package release soon, maybe w/o a solution for the writing issues mentioned here.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 08 Apr 2018 09:08:53 PM UTC, comment #8:

(progress report)
I started looking into this. It seems we're bitten by the order in which attributes appear in nodes in file xl/workbook.xml.rels in the .xlsx archive (always a risk when parsing XML with regexps). The code as it stands in _OCT_spsh_open_ has the order wrong for the file in question.
I have a more robust fix for the original bug report, but for function xlsfinfo.m more effort is required.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 25 Mar 2018 08:46:28 PM UTC, comment #7:

@Markus:
Good that you could help Werner (= anonymous?), thanks.

In the .xlsx archive I couldn't find any logic on how to connect the various chart- and worksheets to the actual sheets together with clues about their content, other than the order in [Content_Types].xml. xl/workbook.xml contains the links but not the sheet types.
So I guess a dive into the ECMA specs is required to find out how do it the right way. AFAICS a large part of the logic had better be implemented in _OCT_spshopen_.m

OTOH your patch may be good enough. Invoking the r:Id's may be good but I'd like to be sure; in the past I've tried several ways, none of them proved robust.

Anyway I have no time until after Easter to have a good look at this.

Note that also xlsfinfo.m chokes on the file.
And once again: care is needed for properly writing out the files esp. updating the various internal pointers when writing.

@comment #6:
.ods is a very inefficient and non-linear file format. Parsing it with regexps isn't doable so it is parsed/read row by row, cell by cell, unlike .xlsx where entire worksheets can be parsed with just one regexp. So little wonder that .xslx is so much faster.

[A little OT]:
During OctConf I talked to a few other developers and I got some support for my opinion:

  • There are multiple mature options for reading/writing spreadsheet files, they only need little glue to get them working in Octave; in fact all of the reliable and supported ones have since long been implemented in the io package. The common thing is that they are all depending on a Java JRE;
  • The only reason to invest in the OCT interface is that many core devs don't want a dependency on Java;
  • Which implies that much developer time has to be spent on reinventing the wheel, i.e. parsing and digging around in the low-level innards of .xlsx and .ods archives (gnumeric is relatively simple).

I sometimes think that the OCT interface had better be limited to simple .xlsx and .ods files and more demanding users had better be referred to the available Java-based interfaces.

All of which doesn't mean that I'm against the OCT interface. But its maintainability gets harder and harder.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 25 Mar 2018 07:48:44 PM UTC, comment #6:

@Markus: great - thanks a lot it works!

By the way: the xlsx (Excel) version is much faster than the ods (Libre Office Calc) version.

xlsx execution time 2.4 seconds versus ods execution time 40.6 seconds. 17 x slower :-(

Thanks and best regards,
Werner

Test Case XLSX:

tic;
[numarr,txtarr,rawarr,limits]=xlsread('500V_SHORT.xlsx','Current Wfms Data',[],'OCT');
%[numarr,txtarr,rawarr,limits]=odsread('500V_SHORT.ods','Current Wfms Data',[],'OCT');
toc;

Checking requested interface(s):
OCT*; (* = default interface)
Elapsed time is 2.4006 seconds.

Test Case ODS:

tic;
%[numarr,txtarr,rawarr,limits]=xlsread('500V_SHORT.xlsx','Current Wfms Data',[],'OCT');
[numarr,txtarr,rawarr,limits]=odsread('500V_SHORT.ods','Current Wfms Data',[],'OCT');
toc;

Checking requested interface(s):
OCT*; (* = default interface)
Elapsed time is 40.6201 seconds.

Anonymous
Sun 25 Mar 2018 07:24:51 PM UTC, comment #5:

@Werner: Here the patched file.

(file #43675)

Markus Mützel <mmuetzel>
Project Member
Sun 25 Mar 2018 06:48:05 PM UTC, comment #4:

@Markus: Thanks for the hint about the hidden sheet and for the patch.

Could you please upload also the patched file '__OCT_xlsx2oct__.m' ?

File to be replaced by the patch:

C:\Octave\Octave-4.2.2\share\octave\packages\io-2.4.10\private\__OCT_xlsx2oct__.m

Anonymous
Sun 25 Mar 2018 06:08:35 PM UTC, comment #3:

@Werner: "odsread" seems to be working for me if you take the hidden sheet "Event Timeline Data" into account:

I am also attaching a patch for _OCT_xlsx2oct_.m that fixes the issue with file #43669 for me.
@Philip: I have inverted the logic to prefer the r:id over a direct enumeration of the sheet%d.xml files. Do you think that might cause problems in other cases?

(file #43673)

Markus Mützel <mmuetzel>
Project Member
Sun 25 Mar 2018 04:54:21 PM UTC, comment #2:

Update: also the function odsread does not work. There is no error message from odsread, but the loaded data (numarr, rawarr etc) is wrong.

see <<500V_SHORT.ods>

Test Case:

[numarr,txtarr,rawarr,limits]=odsread('500V_SHORT.ods',9,[],'OCT');

Checking requested interface(s):
OCT*; (* = default interface)

numarr(1:4,1:4)
ans =

NaN 500.0000e+00 500.0000e+00 500.0000e+00
NaN 1.0000e+00 2.0000e+00 3.0000e+00
-180.8220e+00 384.2639e-03 1.4938e+00 -1.0517e+00
-179.8220e+00 -112.6511e-03 1.6107e+00 -1.3491e+00

numarr (and rawarr) does not coincide with the <<500V_SHORT.ods>> content.

Best regards,
Werner

(file #43671)

Anonymous
Sun 25 Mar 2018 01:51:38 PM UTC, comment #1:

Confirmed.

This is another "new" situation we hadn't seen til now. Thanks for this example.
A quick look shows it is probably related to Chart sheets interfering in the sheet order. I fear this bug can take a while to be fixed; the way the OCT interface opens .xlsx files needs a thorough review, ditto writing/adding data to such files. So that's why I set status to "Postponed".

A workaround is to install a Java JRE (if you haven't already had one installed), install the Apache POI stuff, add it to the javaclasspath (or use chk_spreadsheet_support.m in the io package to do that for you), and read the file with the POI interface.
If you have LibreOffice or OpenOffice.org you can also use that with a Java JRE to read the data into Octave (UNO interface)
Also Excel itself with the windows package works (COM interface).

BTW in passing I noted another bug that I'll fix right away.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 25 Mar 2018 07:22:51 AM UTC, original submission:

The function xlsread (io oackage version 2.4.10) does not read xlsx and xls Excel file with more complex sheet strcture.

Please try it with the attached example: 500V_SHORT.xlsx

Please try to read all sheets, or specific sheets (in my case sheet no. 9).

Test Case 1)

[numarr,txtarr,rawarr,limits]=xlsread('500V_SHORT.xlsx',9,[],'OCT');

Checking requested interface(s):
OCT*; (* = default interface)
error: 'id' undefined near line 52 column 58
error: called from
_OCT_xlsx2oct_ at line 52 column 15
xls2oct at line 203 column 27
xlsread at line 238 column 29
main at line 21 column 29

Test Case 2)

[numarr,txtarr,rawarr,limits]=xlsread('500V_SHORT.xlsx','Current Wfms Data',[],'OCT');

Checking requested interface(s):
OCT*; (* = default interface)
error: 'rawdata' undefined near line 91 column 17
error: called from
_OCT_xlsx2oct_ at line 91 column 7
xls2oct at line 203 column 27
xlsread at line 238 column 29
main at line 20 column 29

Best regards,
Werner Simbuerger

Anonymous

 

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

Attach Files:
   
   
Comment:
   

Attached Files
file #43903:  Testcase3.xlsx added by q52y9rilim22nu2p (828KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #43884:  __OCT_spsh_open__.m added by philipnienhuis (8KiB - application/octet-stream)
file #43885:  __OCT_xlsx2oct__.m added by philipnienhuis (10KiB - application/octet-stream)
file #43874:  parameter.xlsx added by q52y9rilim22nu2p (5KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #43872:  1kV_SHORT.xlsx added by q52y9rilim22nu2p (2MiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #43675:  __OCT_xlsx2oct__.m added by mmuetzel (9KiB - application/octet-stream)
file #43673:  bug53459_sheet_by_name.patch added by mmuetzel (2KiB - application/octet-stream)
file #43671:  500V_SHORT.ods added by None (1MiB - application/vnd.oasis.opendocument.spreadsheet)
file #43669:  500V_SHORT.xlsx added by None (2MiB - application/octet-stream)

 

Depends on the following items: None found

Items that depend on this one: None found

 

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

    Do you think this task is very important?
    If so, you can add your encouragement to it.
    This task has 0 encouragements so far.

    Only project members can vote.

     

     

     

    Follow 18 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2018-04-17 philipnienhuis StatusReady For Test => Fixed
        Open/ClosedOpen => Closed
    2018-04-12 q52y9rilim22nu2p Attached File- => Added Testcase3.xlsx, #43903
    2018-04-11 philipnienhuis Attached File- => Added _OCT_spsh_open_.m, #43884
        Attached File- => Added _OCT_xlsx2oct_.m, #43885
        StatusIn Progress => Ready For Test
    2018-04-10 q52y9rilim22nu2p Attached File- => Added parameter.xlsx, #43874
    2018-04-10 q52y9rilim22nu2p Attached File- => Added 1kV_SHORT.xlsx, #43872
    2018-04-09 philipnienhuis StatusPostponed => In Progress
    2018-04-08 philipnienhuis Assigned toNone => philipnienhuis
    2018-03-29 jwe Summaryio package (2.4.10), function xlsread, results in unexpected error reading Excel (xlsx) file => [octave forge] (io) 2.4.10 function xlsread, results in unexpected error reading Excel (xlsx) file
    2018-03-25 mmuetzel Attached File- => Added _OCT_xlsx2oct_.m, #43675
    2018-03-25 mmuetzel Attached File- => Added bug53459_sheet_by_name.patch, #43673
    2018-03-25 None Attached File- => Added 500V_SHORT.ods, #43671
    2018-03-25 philipnienhuis StatusNone => Postponed
        Release4.2.2 => other
        Operating SystemMicrosoft Windows => Any
    2018-03-25 None Attached File- => Added 500V_SHORT.xlsx, #43669

    Back to the top


    Powered by Savane 3.4