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

 
 

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

Submitter:  Andreas Weber <andy1978>
Submitted:  Sun 01 May 2016 09:59:06 AM UTC
   
 
Category:  Octave Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Incorrect Result
Status:  Fixed Assigned to:  philipnienhuis
Originator Name:  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

Tue 03 May 2016 08:40:54 PM UTC, comment #7: 
Philip Nienhuis <philipnienhuis>
Group Member
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>
Group Member
Sun 01 May 2016 09:58:42 PM UTC, comment #5: 



Philip Nienhuis <philipnienhuis>
Group Member
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:

  <Application>
    Microsoft Excel
  </Application>


I'll have a look.

Philip Nienhuis <philipnienhuis>
Group Member
Sun 01 May 2016 11:42:31 AM UTC, comment #3: 

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


  <c r="A232" s="398" t="s">
    <v>2</v>
  </c>
  <c r="B232" s="265" t="str">
    <f ca="1">$B$201</f>
    <v>234,00</v>
  </c>
  <c r="C232" s="119"/>
  <c r="D232" s="119"/>
  <c r="E232" s="119"/>
  <c r="F232" s="119"/>
  <c r="G232" s="119"/>
  <c r="H232" s="119"/>
  <c r="I232" s="119"/>
  <c r="J232" s="152"/>
  <c r="K232" s="120"/>
</row>


Andreas Weber <andy1978>
Group 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


<row r="232" spans="1:11" ht="17.850000000000001" customHeight="1"><c r="A232" s="398" t="s"><v>2</v></c><c r="B232" s="265" t="str"><f ca="1">$B$201</f><v>234,00</v></c>


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

Andreas Weber <andy1978>
Group 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>
Group Member
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:

pkg load io
fn = "test1.xlsx";

xls = xlsopen (fn, 0, "OCT");

wsh = "Protokoll 1 Richtung";

tmp1 = xls2oct (xls, wsh, "B233")
assert (tmp1{1}, "345,00")

tmp2 = xls2oct (xls, wsh, "A232")
assert (tmp2{1}, "xyz")

tmp3 = xls2oct (xls, wsh, "B232")
## This fails
assert (tmp3{1}, "234,00")


If I empty Cell C223, then the above works.

Andreas Weber <andy1978>
Group Member

 

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

Attach Files:
   
   
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
  • -email is unavailable- added by andy1978 (Submitted the item)
  • -email is unavailable- added by andy1978
  •  

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

    Date Changed by Updated Field Previous Value => Replaced by
    2016-05-03 philipnienhuis StatusIn Progress Fixed
        Open/ClosedOpen Closed
    2016-05-01 philipnienhuis Attached File- Added _OCT_xlsx2oct_.m, #37038
    2016-05-01 philipnienhuis StatusNeed Info In Progress
        Operating SystemGNU/Linux Any
    2016-05-01 philipnienhuis StatusNone Need Info
        Assigned toNone philipnienhuis
        Release4.0.1 other
        Summaryio package, OCT interface: xls2oct fails io package, OCT interface: xls2oct fails reading formula results
    2016-05-01 andy1978 Attached File- Added doit.m, #37033
        Attached File- Added test1.xlsx, #37034
        Carbon-Copy- Added philipnienhuis

    Back to the top

    Powered by Savane 3.13-3230.
    Corresponding source code