bugGNU Octave - Bugs: bug #47815, io package, OCT interface: xls2oct...

 
 

bug #47815: io package, OCT interface: xls2oct fails reading formula results

Submitted by:  Andreas Weber <andy1978>
Submitted on:  Sun 01 May 2016 09:59:06 AM UTC  
 
Category: Octave Forge PackageSeverity: 3 - Normal
Priority: 5 - NormalItem Group: Incorrect Result
Status: FixedAssigned to: Philip Nienhuis <philipnienhuis>
Originator Name: Open/Closed: Closed
Release: otherOperating 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 Jump to the original submission)

Tue 03 May 2016 08:40:54 PM UTC, comment #7:

I pushed two csets [1] and consider this fixed.

[1]:
http://hg.code.sf.net/p/octave/io/rev/d2834f64801d
http://hg.code.sf.net/p/octave/io/rev/4b7c35ce6c46

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 01 May 2016 10:57:24 PM UTC, comment #6:

Please try attached _OCT_xlsx2oct_.m (put in share/..../<io-2.4.1>/private/ and report back.

On my boxes this fixes your problem. The whole thing however hints to potential similar issues elsewhere, so before finalizing I'd like to investigate further.

The underlying issue is that regular expressions may appear fast (and they are) but real XML parsers are much more reliable.
This bug here means just another addition to already fairly complicated regular expressions. I sometimes fear that in the end, after a chain of more bugs like this (heaven forbid), the regular expressions may become so complicated that the speed advantage over XML parsers gets moot.

(file #37038)

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 01 May 2016 09:58:42 PM UTC, comment #5:
Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 01 May 2016 03:37:16 PM UTC, comment #4:

OK, sorry for having overlooked your mentioning of creating it with Excel. Indeed, in docprops/app.xml:

I'll have a look.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 01 May 2016 11:42:31 AM UTC, comment #3:

Same XML part (./xl/worksheets/sheet3.xml) but with linebreaks

Andreas Weber <andy1978>
Project Member
Sun 01 May 2016 11:32:01 AM UTC, comment #2:

As I already wrote, test1.xlsx was created by MS Excel.

If you unpack test1.xlsx (XLSX files are merely a zipped bunch of xml files) and you open ./xl/worksheets/sheet3.xml you would find

So there is clearly a (chached) string value "234,00" for Cell "B232"

Andreas Weber <andy1978>
Project Member
Sun 01 May 2016 10:53:13 AM UTC, comment #1:

I tend to close this bug report with "Invalid" or "Won't fix", but before that you can enlighten me a bit.

How did you create the last version of test1.xlsx ? Did you add the formula using the OCT interface?

Let me explain:

Exploring the spreadsheet file using LibreOffice I find that the offending cell contains a formula merely referencing another cell. That other cell again contains a (more complicated) formula.

That makes me wonder whether you entered the formula as a text string with the OCT interface.
The OCT interface does not contain a formula evaluator (nor a formula validator), so if you use that to enter or change a formula, the (cached) value in the XML cell will not be updated, and in case of new cell contents it will not even initialized.
FYI it is those cached formula result values that are read (unless you specify to read the formulas themselves).

Sure enough, after I read & saved the file with LibreOffice, your script worked fine and cell B232 correctly contained the value 234.

The only spreadsheet interfaces with formula validators and -evaluators are:

  • UNO (invoking LibreOffice / OpenOffice.org behind the scenes)
  • COM (invoking Excel behind the scenes)
  • Apache POI (Java based). I'm unsure if this one really works well; it did to some extent at the time I wrote io package's POI interface 7 years ago but for various reasons I'd rather invoke LibreOffice for the job.
Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Sun 01 May 2016 09:59:06 AM UTC, original submission:

io version 2.4.1

I've used the OCT interface and xls2oct extensively the last weeks.

In the attached xlsx (wrote from MS-Excel) there are several cells with citations and xls2oct fails on one cell.

The attached file doit.m:

If I empty Cell C223, then the above works.

Andreas Weber <andy1978>
Project Member

 

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

Attach File(s):
   
   
Comment:
   

Attached Files
file #37038:  __OCT_xlsx2oct__.m added by philipnienhuis (12KiB - text/x-objcsrc)
file #37033:  doit.m added by andy1978 (290B - d2l/unknowntype)
file #37034:  test1.xlsx added by andy1978 (42KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -unavailable- added by andy1978 (Submitted the item)
  • -unavailable- added by andy1978
  •  

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

    Only project members can vote.

     

    Please enter the title of George Orwell's famous dystopian book (it's a date):

     

     

    Follow 12 latest changes.

    Date Changed By Updated Field Previous Value => Replaced By
    Tue 03 May 2016 08:40:54 PM UTCphilipnienhuisStatusIn Progress=>Fixed
      Open/ClosedOpen=>Closed
    Sun 01 May 2016 10:57:24 PM UTCphilipnienhuisAttached File-=>Added _OCT_xlsx2oct_.m, #37038
    Sun 01 May 2016 09:58:42 PM UTCphilipnienhuisStatusNeed Info=>In Progress
      Operating SystemGNU/Linux=>Any
    Sun 01 May 2016 10:53:13 AM UTCphilipnienhuisStatusNone=>Need Info
      Assigned toNone=>philipnienhuis
      Release4.0.1=>other
      Summaryio package, OCT interface: xls2oct fails=>io package, OCT interface: xls2oct fails reading formula results
    Sun 01 May 2016 09:59:06 AM UTCandy1978Attached File-=>Added doit.m, #37033
      Attached File-=>Added test1.xlsx, #37034
      Carbon-Copy-=>Added philipnienhuis

    Back to the top


    Powered by Savane 3.1-cleanup1