bugGNU Octave - Bugs: bug #51512, [octave forge] (io) Missing or...


bug #51512: [octave forge] (io) Missing or wrong types when using xlsread with OCT interface

Submitted by:  Markus Mützel <mmuetzel>
Submitted on:  Wed 19 Jul 2017 09:32:10 PM UTC  
Category:  Octave Forge Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Incorrect Result
Status:  Fixed Assigned to:  Philip Nienhuis <philipnienhuis>
Originator Name:  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)

Fri 20 Oct 2017 02:57:02 PM UTC, comment #30:

Turns out the old code from a previous version was still in place.
I updated the pertinent regexp call & processing code cf. your style, Markus, and pushed the final fix.

Again closing report.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Mon 16 Oct 2017 09:32:06 AM UTC, comment #29:

Reopening, turns out the regexp for formulas doesn't work as intended.
I'll look into it

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 15 Oct 2017 07:53:32 PM UTC, comment #28:

I've pushed the final fix here:
based on file #42122 with a few edits & additional bug fixes (for bugs by me that is)

Markus, thanks very much for your assistance.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sat 14 Oct 2017 06:32:12 PM UTC, comment #27:

Thanks, Markus.

Even to be able to detect formulas spanning multiple cells we'd need another dedicated regexp + scan the entire worksheet XML. ATM that hurts a little bit too much in my head :-)

I'd agree to await bug reports - I wonder how many users do use the "formulas_as_text" option (I do sporadically but that's just one).

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sat 14 Oct 2017 01:31:12 PM UTC, comment #26:

Yes, file #41505 is the latest version I have here.

IMHO, it is fair to not implement the function span logic (now). Do you think it would be possible to at least display a warning/info to a user that queries formulas? Maybe something along the lines of "Formula in cell C2 spans C2:H2."
But maybe that can be done in another bug report.

Markus Mützel <mmuetzel>
Project Member
Wed 11 Oct 2017 08:46:10 PM UTC, comment #25:

I had a try with _OCT_xls2oct_.m where I split up the regexp for numeric data into 6 different regexps (see attached).

Separate regexps:

and with the "original" combined regexp:

My conclusion here:
As to speed, one combined regexp to catch all XML syntaxes is better than separate regexps for each specific XML syntax.
But it may be that splitting up the XML txt string may be better to avoid OOM issues. But as I haven't hit those (even with big spreadsheets filling up RAM usage by Octave to > 7 GB) for the time being I'll ignore this risk.

As to stanza #3 in your comment #23, I think I'll stick to the combined regexp and not implement distinct groups, sacrificing some speed if a user wants formula output.
One reason is that AFAICS several distinct regexps would needed for each "class" of formula result (double, text or boolean) to be able to avoid reading those values as you suggest. But maybe I'm wrong?

Hopefully this weekend I'll commit a patch for one of my _OCT_xlsx2oct_ .m versions in comment #18 or (peferrably) your amended version in comment #19 so that this bug can (finally) be closed.
Do you still have the latter version? was that file #41505 ?

Subsequently I hope to be able to make a new io release soon after.

(file #42122)

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Tue 10 Oct 2017 07:45:14 PM UTC, comment #24:

Looking into the row/column span files logic, I think it would require a lot of code to mimic this in Octave ( / OF-io).
I'd prefer to leave this as-is for now.

I'll experiment a little bit with leaving out the '|' chars in regexp templates and replace them by separate regexps.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sat 07 Oct 2017 01:21:24 PM UTC, comment #23:


attached are example files for formulas spanning several cell. The first one has formulas spanning several rows in a column. The second one has formulas spanning several columns in one row. For the third file, I let a formula span several rows in one column and then expanded the area to the right (over multiple columns). The actual formulas seem to be given in the "header cell" of the span with a reference to the scope to which they apply.
To get the formula of each respective cell, one would have to take the relative position of the cell to the "header cell" into account.

Wrt which version would be best, I think it comes down to personal taste. I didn't notice much difference (i.e. none at all) when it comes to performance.
Thinking of the additional logic for the formula output, it might be easier to write and maintain if you arranged distinct cases in reasonable groups. Formulas could then be read in dedicated regexps depending on whether the value or the formula itself should be returned. That might also save some time that was unnecessarily spent reading the values if a user requested the formulas.
But it's probably best for you to decide as the maintainer.

After having written the above, I found this benchmark [1] from which I don't know whether it is still valid. However, the following suggests that it might be better performance-wise to use loads of dedicated regexps:
"Several BT-based algorithms such as onig and PCRE are 10X slower than using two regex separately, although this is not true for all BT-based algorithms."
Since we are using PCRE, did you ever try to get rid of all "|" in the regular expressions?

[1] http://lh3lh3.users.sourceforge.net/reb.shtml

(file #42076, file #42077, file #42078)

Markus Mützel <mmuetzel>
Project Member
Fri 06 Oct 2017 08:11:12 AM UTC, comment #22:

(picking up again)

Do you have an example file for the multi-line formulas in comment #16? I conclude the actual formula has to be fetched from the sharedStrings.xml file (in your exmple t pos. 4 (0-based indexing), which adds some more oomplexity.

I don't know yet which version would be best - my very latest version with less code but more complex regexp, or the previous one.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 25 Aug 2017 07:47:16 PM UTC, comment #21:

The reference for cell date values (t="d" attribute value) is in "ECMA-376, Fifth Edition, Part 1 - Fundamentals And Markup Language Reference" (d/ldable from the web, use a search engine), in the pdf doc in the zip, in par. 18.18.11.
Date values are to be in ISO 8601 format.

Due to some private affairs I didn't have much time when I got back from vacation, hopefully next week I'll have some more time to look into your patches (thanks for those).

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 18 Aug 2017 08:11:15 PM UTC, comment #20:

I've been playing a little with the regexps as well, will report when I'm back (after 21th).
In the mean time, note that Excel reads the file faster than the time Octave's str2double() needs to just process the numeric cell values.

BTW there is a ref to OOXML's date format, I'll dig it up for you next week.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 13 Aug 2017 03:29:54 PM UTC, comment #19:

The last version seems to run at more or less the same speed as the version before.
Your file (file #41434):

My file (file #41352 + changes from comment #16):

Immediately followed by "COM":

I could not find any reference to a date/time type in the specifications. The only available types I found are:

Dates are stored as doubles in my tests. But I suppose it doesn't hurt to leave the logic in if you have seen that type in some file.

In the attached file, I tried to replace any "." match with a negative match to avoid the trouble with PCRE's MATCH_LIMIT. Additionally, I changed slightly how the matched results are checked for consistency such that multiples of 3 errors can also be detected.
The file still performs approximately identically:

(file #41505)

Markus Mützel <mmuetzel>
Project Member
Sun 06 Aug 2017 01:58:03 AM UTC, comment #18:

-file got lost in preview, attempt #2-

(file #41434)

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 06 Aug 2017 01:56:12 AM UTC, comment #17:

Thanks Markus,

Attached is yet another version (perhaps a bit incomplete) in which I tried to cut down on the number of different regexps.

However, on my laptop (which BTW is 2 X slower than my desktop) it didn't make much difference reading the file in comment #14, it was ~128 secs (version of comment #13) versus ~125 secs. (this version). Inside profshow's results there wasn't much difference either. Apparently there's little to be gained by combining regexps, it seems to be the total complexity that counts.
Maybe you can experiment with it a little if you want.

From tomorrow on I'll have patchy internet access for 2 weeks so expect little or no sign of life from me until Aug. 21 or so.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Wed 02 Aug 2017 08:48:26 PM UTC, comment #16:

It definitely is an improvement if more data can be fetched. And you could reduce code complexity at the same time. Excellent.

I had problems reading a big file with approximately 1000x1000 cells saved with Excel 2016 that contains doubles, Booleans, and strings (no dates) as values and as formula results. (That file did not upload the last time, probably due to file size limits. File size 8 MB. xml string size 44.2 MB.)
With Octave 4.2.1 on Windows, it gave the following warning and then caused a segfault:

The help string for "regexp" was updated recently by Dan Sebald regarding this issue.

Additionally, a formula can span several rows. I didn't realize that until now. In this case, a cell could be described like this in xml:

Note that the f-tag has no content. That isn't yet matched by the regexp in the file from comment #13.
I tried with the following regexp in the failing line:

I don't know whether this is the most efficient regular expression. But it doesn't segfault for me and matches all strings.

Compared to "COM" (Excel 2016):

Excel (via COM) is still 16x faster for me. But you should probably compare with the same file on the same system to get comparable results. I can send you the file I used for testing by email if you would like to test with that one.
My system: Windows 10 Home 1703, Core i7 7500U @ 2.7 GHz, 8 GB RAM, Octave installed on a SSD but reading the test file from a HDD.

Markus Mützel <mmuetzel>
Project Member
Fri 28 Jul 2017 06:26:35 PM UTC, comment #15:

Comparing with original io-2.4.7, reading the 1000x1000 spreadsheet using OCT takes only 60 secs. But then there wasn't a separate regexp for boolean (logical) data, nor for "inlineStr" string data.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 28 Jul 2017 06:10:54 PM UTC, comment #14:

Some timings on my Core Duo i5 box (Win 7 Prof., 8 GB RAM & SSD):

So COM (w. Excel 2013 as work horse) is by far the fastest, UNO (invoking LibreOffice) by far the slowest, and OCT some 25 X slower than COM.
In a way disappointing that proprietary SW is so much faster, but on the bright side: OCT is still 2nd fastest.

Some profiling:

I tried experimenting with preallocating regexp's output array but that didn't help much.
These profiling results show that tuning the regexp, if at all possible, is probably the best way to get better performance.
Maybe the modification I made (obtaining the cell addresses and contents in one regexp) is slower than getting them separately with two regexps as in the original __OCT_xlsx2oct__.m .
Note: in this case (for a 1000x1000 array) the XML string to be processed is about 37 MB in size.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Wed 26 Jul 2017 09:35:20 PM UTC, comment #13:


Attached is a patch that AFAICS should work fine now. I also had to fix __OCT_oct2xlsx__.m to beter comply with the OOXML specs.
Could you please try this patch and report back?
To make it easier I also attached the fixed __OCT_oct2xlsx__.m and __OCT_xlsx2oct__.m files, you can just swap them into the io/../private subdir.

I wouldn't mind if you (or someone else) improve the patch. I'll see what it looks like when I'm back late August, then hopefully it has been tested / tested+improved sufficiently so that the final fix can be pushed.


(file #41350, file #41351, file #41352)

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Tue 25 Jul 2017 08:10:00 PM UTC, comment #12:

(progress report)
I started overhauling most of the regular expressions.

At this point all numeric & boolean contents are catched correctly; I'm presently stuck in the strings dept. It seems inline strings were never processed correctly so there's an improvement looming.

It seems we now also have an option to correctly extract date/time values.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Tue 25 Jul 2017 12:37:18 PM UTC, comment #11:

(found some spare time)
Comparing LO53's sheet1.xml and Excel2013's sheet1.xml it turns out that Excel writes a different attribute order than LO.
Luckily the 'r=' (cell address) and 't=' tags are always in that order (r first).

Experimenting further I saw that probably half of the regexps can be dropped as cell addresses and cell content can be obtained in one regexp and separated using reshape. Some additional checks are required to avoid out-of-phase matches (which would be killing for reshape). Maybe I can finish this this week, or maybe next week. After Aug 6 I'm 2 week w/o much web access.

Anyway I'm happy that the xlsx read part of the OCT interface gets some attention.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Tue 25 Jul 2017 06:11:19 AM UTC, comment #10:

Ah boolean and double regexps reversed :-)
(I need coffee)

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Tue 25 Jul 2017 06:10:36 AM UTC, comment #9:

Singling out booleans as you do is the way to go, yes.

Cf. ECMA specs 's="<number>"' tags can be ignored as they just refer to styles. So a regexp along the way of

should work better. The "(?:.......)" clauses (for tags that may not be present) need attention. I've experimented a little yesterday evening, but this has to be sorted out more rigorously.
A flowchart is needed to decide what data to read/tags to search for in what order. Some regexps may even be dropped, hopefully leading to faster reading.
For reference I've attached worksheet1.xml from the files made by Excel2013 and LO5.2. What I usually do is just read them with fread(<fid, Inf, "char=>char")' as text strings into the Octave terminal and then experiment with regexp() until I get it right.

I think what Excel writes is still the reference (as that's the file "format" most users will encounter); LO / OOo is not completely ECMA-compliant.

The timings you mention aren't that bad. Have you compared with Excel itself (just reading, not tru COM interface per se)?

(file #41326, file #41327)

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Mon 24 Jul 2017 08:54:54 PM UTC, comment #8:


I timed different variations of the patch while reading the attached file with roughly 1000x1000 cells.
With the current version of _OCT_xlsx2oct_.m: 79.3s
With the "cellfun patch" from comment #1: 108.5s
With a dedicated regexp for reading Booleans (patch attached to this comment): 92.6s

However, most of the additional time is spend reading the strings that the current version skips. Without that particular change (optional s-tag), the time reduced to: 79.2s.

So there is hardly any reduction in speed (if any at all), when taking the number of cells into account that are read as non-empty.

(file #41322)

Markus Mützel <mmuetzel>
Project Member
Mon 24 Jul 2017 07:47:28 PM UTC, comment #7:


The OOXML specs are here:

Looking through it I think several regexps in _OCT_xlsx2oct_.m may have to be reconsidered. Once done there's no need for helper subfunctions. I know what to do but the next weeks I lack time, hopefully I can pick that up after my vacation, or maybe you or someone else will beat me to it.
Now that I mention: I remember to have struggled a bit with it before; at the time I found Excel itself wasn't always complying to the ECMA rules and Libre-/OpenOffice still less.

I agree LibreOffice is a bit lacking. You may want to file an upstream bug report about setting the result type of boolean formulas to "numeric". The folks at LibreOffice seem relatively responsive.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Mon 24 Jul 2017 02:28:15 PM UTC, comment #6:

OK, 10,000 x 1000 may be a bit excessive; that's 80+ MB just for the double array, and maybe tens of times times bigger for the XML text string to be read.

1000 x 1000 should do.

As to the LibreOffice quirks (the same as OpenOffice.org quirks) you mention in the bottom of your original report:
In the UNO interface there's no dedicated ID for boolean values, AFAIU because the LO/OOo code base doesn't contain it, so there's no way (at least not that I know) to find out the type/class of data in a spreadsheet cell. That's quite a bit of a limitation if you ask me; IIRC I may even have filed a bug report for that long ago (~2011 or 2012) for OOo.
To show what I mean, see L.50-55 and L.102-109 in _UNO_spsh2oct_.m and here:

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 21 Jul 2017 06:02:41 PM UTC, comment #5:

Not even that, but regexps can be tuned as well. Speed differenes between slow and fast regexps can be up to tens of percents.
There's a bit of hidden literature bout it on the web and I think Rik knows a lot about it.

I think the cellfun stuff you wrote could be much faster if the actual contents (is_bool_value) were written in C++

Be sure to benchmark with large spreadsheets (10,000 rows by -say- 1000 columns or so).

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 21 Jul 2017 05:32:05 PM UTC, comment #4:


I did not expect that "regexp" would be faster on long inputs than a call to "cellfun" with rather simple functions. But preliminary test indicate that "regexp" is impressively fast.
I will prepare an adapted changeset and try to upload it maybe early next week together with some benchmark results if possible.

Markus Mützel <mmuetzel>
Project Member
Fri 21 Jul 2017 07:41:41 AM UTC, comment #3:

Markus, I'm busy with it; may take some time.

I'm not surprised LibreOffice (and for that matter, OpenOffice.org) and Excel write the OOXML differently, that was a quirk I had to code around early on. That's one of the tricky things when reading XML with regexps rather than with an XML parser - XML gurus tend to frown upon that. This is a major weak point in the OCT spreadsheet interface.

I see a new subfunc "is_bool_value". I'd like to find out if adapting regexps is faster.
The OCT interface used to be blazingly fast for OOXML, much faster than Excel and Matlab, but adding more and more type/subtypes (than just double) and Excel-quirk-workarounds made it less efficient.
So I think the way this should be fixed (regexp, or postprocessing regexp output in a separate subfunc, or some other way) should depend on timings with large test spreadsheets, ideally with mixed types.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Thu 20 Jul 2017 09:57:52 AM UTC, comment #2:

Thx I'll have a look

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Wed 19 Jul 2017 09:40:43 PM UTC, comment #1:

Here the patch.

(file #41256)

Markus Mützel <mmuetzel>
Project Member
Wed 19 Jul 2017 09:32:10 PM UTC, original submission:

Attached are four example xlsx files created with Excel 2007, Excel 2013, LibreOffice 5.2 and LibreOffice 5.3.
When reading from the Excel files, the cells that contain string formula results are read as empty. The cells that contain Boolean values or formulas are read as numerics.
When reading from the LibreOffice files, the cells that contain formulas with numeric or logical results are read as strings. Boolean values are read as numeric.

The following script displays the issues:

I will attach a patch that fixes these issues as soon as I have a bug number.
I could not figure out how to read Boolean values from formulas correctly from the LibreOffice files. But I am inclined to blame LibreOffice since they tag these cells as numeric (t="n") instead of Boolean (t="b"). The cells even show up incorrectly (numeric instead of logical) when opening the files in LibreOffice. So xlsread will read these cells as numeric, too. (Better than as strings imho.)

Markus Mützel <mmuetzel>
Project Member


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

Attach Files:

Attached Files
file #42122:  __OCT_xlsx2oct__.m added by philipnienhuis (11KiB - application/octet-stream)
file #42076:  Excel2016_row_span.xlsx added by mmuetzel (9KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #42077:  Excel2016_column_span.xlsx added by mmuetzel (9KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #42078:  Excel2016_rows_then_columns.xlsx added by mmuetzel (10KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #41505:  __OCT_xlsx2oct__.m added by mmuetzel (10KiB - application/octet-stream)
file #41434:  __OCT_xlsx2oct__.m added by philipnienhuis (10KiB - d2l/unknowntype)
file #41350:  bug_#51512_PRN.patch added by philipnienhuis (15KiB - application/x-download)
file #41351:  __OCT_oct2xlsx__.m added by philipnienhuis (20KiB - text/x-objective-c)
file #41352:  __OCT_xlsx2oct__.m added by philipnienhuis (10KiB - text/x-objective-c)
file #41322:  bug51512_xlsx2oct_types_v2.patch added by mmuetzel (7KiB - application/octet-stream)
file #41256:  bug51512_xlsx2oct_types.patch added by mmuetzel (6KiB - application/octet-stream)
file #41252:  Excel2007_test.xlsx added by mmuetzel (8KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #41253:  LibreOffice52_test.xlsx added by mmuetzel (5KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #41254:  Excel2013_test.xlsx added by mmuetzel (8KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #41255:  LibreOffice53_test.xlsx added by mmuetzel (5KiB - 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 jwe (Updated the item)
  • -email is unavailable- added by mmuetzel (Submitted the item)
  • -email is unavailable- added by mmuetzel

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

    Date Changed by Updated Field Previous Value => Replaced by
    2017-10-20 philipnienhuis StatusIn Progress => Fixed
        Open/ClosedOpen => Closed
    2017-10-16 philipnienhuis Open/ClosedClosed => Open
    2017-10-16 philipnienhuis StatusFixed => In Progress
    2017-10-15 philipnienhuis StatusReady For Test => Fixed
        Open/ClosedOpen => Closed
    2017-10-11 philipnienhuis Attached File- => Added _OCT_xlsx2oct_.m, #42122
    2017-10-07 mmuetzel Attached File- => Added Excel2016_row_span.xlsx, #42076
        Attached File- => Added Excel2016_column_span.xlsx, #42077
        Attached File- => Added Excel2016_rows_then_columns.xlsx, #42078
    2017-08-13 mmuetzel Attached File- => Added _OCT_xlsx2oct_.m, #41505
    2017-08-12 jwe Summaryof-io: Missing or wrong types when using xlsread with OCT interface => [octave forge] (io) Missing or wrong types when using xlsread with OCT interface
    2017-08-06 philipnienhuis Attached File- => Added _OCT_xlsx2oct_.m, #41434
    2017-07-26 philipnienhuis Attached File- => Added _OCT_xlsx2oct_.m, #41352
        StatusIn Progress => Ready For Test
    2017-07-26 philipnienhuis Attached File- => Added bug_#51512_PRN.patch, #41350
        Attached File- => Added _OCT_oct2xlsx_.m, #41351
    2017-07-25 philipnienhuis StatusPatch Submitted => In Progress
    2017-07-25 philipnienhuis Attached File- => Added Ex13_sheet1.xml, #41326
        Attached File- => Added lo52_sheet1.xml, #41327
    2017-07-24 mmuetzel Attached File- => Added bug51512_xlsx2oct_types_v2.patch, #41322
    2017-07-20 philipnienhuis Assigned toNone => philipnienhuis
    2017-07-19 mmuetzel Attached File- => Added bug51512_xlsx2oct_types.patch, #41256
        StatusNone => Patch Submitted
    2017-07-19 mmuetzel Attached File- => Added Excel2007_test.xlsx, #41252

    Back to the top

    Powered by Savane 3.3