bugGNU Octave - Bugs: bug #52875, [octave forge] (io) Empty value...

 
 

bug #52875: [octave forge] (io) Empty value tag (<v/>) due to a formula in Excel causes multiple cells to be skipped

Submitter:  None
Submitted:  Fri 12 Jan 2018 09:14:08 PM UTC
   
 
Category:  Octave Package Severity:  2 - Minor
Priority:  3 - Low Item Group:  Incorrect Result
Status:  Fixed Assigned to:  philipnienhuis
Originator Name:  radekivv 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

Tue 25 Oct 2022 09:29:32 PM UTC, comment #14: 

Fixing this actual bug report.

The bug (actually feature request IMO) of shared formulas reading is something for another time. Since this bug report I haven't seen any bug reports about people needing that feature, nor from before that time. It's on my ToDo list but with low priority.

Philip Nienhuis <philipnienhuis>
Group Member
Sun 22 Apr 2018 07:44:41 PM UTC, comment #13: 

Turn out all works fine AFAICS.
The empty char cells in output array "txt" are created in parsecell.m and need to be there as txt is a rectangular cellstr array.

Recovering the shared formulas is a different challenge for another time.
Lowering priority for that last part.

Philip Nienhuis <philipnienhuis>
Group Member
Sun 25 Mar 2018 08:52:07 PM UTC, comment #12: 

Markus,
Indeed I use a 2.4.11-alpha :-)

I think some more regex-ting is needed to get rid of the empty text cells. Or maybe some other action, like wiping text cells contents (n the inernl arrys) that are known to have other content.

Something for after Easter.

Philip Nienhuis <philipnienhuis>
Group Member
Sun 25 Mar 2018 04:53:58 PM UTC, comment #11: 

@Philip: I am getting different results with io-2.4.10 (the one shipped with Octave 4.2.2 for Windows). Reading the commit messages, it looks like this bug was fixed after the release of io-2.4.10.
With the regular expression from the current tip of io, I get the same results as you do.
What does Matlab return? It might actually be that the (empty) string in cell C2 correctly causes the txt cell array to expand. It doesn't look like it reading The Mathwork's help page on xlsread. But who knows...

I won't be able to test this myself within the next week or two.

Markus Mützel <mmuetzel>
Group administrator
Sun 25 Mar 2018 02:00:44 PM UTC, comment #10: 

@Markus;
With io-2.4.10 I get this out of file #42995:

>> [num, txt, raw] = xlsread ('Excel2016_emptyFormulaResult.xlsx', 1, '', 'oct')
num =
     1     2   NaN     4     5

txt =
{
  [1,1] = A
  [2,1] =
  [1,2] = B
  [2,2] =
  [1,3] = C
  [2,3] =
  [1,4] = D
  [2,4] =
  [1,5] = E
  [2,5] =
}

raw =
{
  [1,1] = A
  [2,1] =  1
  [1,2] = B
  [2,2] =  2
  [1,3] = C
  [2,3] =
  [1,4] = D
  [2,4] =  4
  [1,5] = E
  [2,5] =  5
}


Output cellstr array txt has too many elements, but the numeric array is fine. So we have progress ... :-)

Philip Nienhuis <philipnienhuis>
Group Member
Sat 20 Jan 2018 09:59:54 PM UTC, comment #9: 

Nice catch. I never checked what empty formula results did.
I don't think that it has so much to do with shared formulas. The issue was that formula cells with empty values were skipped by the regexp before the patch (shared or not didn't matter, see the attached file without shared formulas).
If you like, you could close this report. I think the issue here is fixed. Reading back the formulas that are shared between multiple cells is a different issue imho.
Even so, I don't mind if you want to re-purpose this report for that issue. :-)

(file #42995)

Markus Mützel <mmuetzel>
Group administrator
Thu 18 Jan 2018 08:31:36 PM UTC, comment #8: 

In the mean time I pushed your proposed fix.
I'm still working on recovering the formulas, so I '' ll leave this open for now.

Status => Ready for test, as the fix for the reported bug is in io repo.

Philip Nienhuis <philipnienhuis>
Group Member
Mon 15 Jan 2018 10:30:48 PM UTC, comment #7: 

Thanks Radek, yes I see what you mean.

Your fix indeed works for your file and the default test script (io_xls_testscript.m, also in io), good! but I want to test some more spreadsheets I have lying around here to be sure (and to be sure there are no screw-ups elsewhere).

This is actually a case Markus Muetzel showed earlier, i.e., having shared formulas rather than shared strings, see bug #51512, comment #16 and comment #22 and up. It didn't get implemented at the time as this situation was supposedly rare, but now you've hit it :-) (I'll cc Markus, I'd bet he wants to know.)
With your fix we can at least read the shared formula values, but reading back the formulas is quite a bit more involved.

It may take a little while, hopefully next weekend I have more time.
Some days ago I had io-2.4.10 released so this fix will be in a next version, probably some months away, so no hurry yet.

Philip Nienhuis <philipnienhuis>
Group Member
Mon 15 Jan 2018 03:42:14 PM UTC, comment #6: 

@Philip
Got it! I am attaching an Excel file.
This is a typical case, it was happening for any Excel file that I tried to process so not specific to a particular file.

(file #42914)

Anonymous
Mon 15 Jan 2018 01:52:22 PM UTC, comment #5: 

@Radek
Yup, I saw that xml file but it doesn't open correctly here. There's translation inside like \" that screws up. It also contains  implicit references to other xml files, e.g, sharedStrings.xml, elsewhere in the hierarchical archive that .xlsx actually is.

So yes, an Excel .xlsx file that shows the problem would make debugging a lot more feasible.

Philip Nienhuis <philipnienhuis>
Group Member
Mon 15 Jan 2018 02:22:14 AM UTC, comment #4: 

Hi Philip,
I included XML file in my original e-mail (see bug1.txt).
If you need the Excel file, I can try to send it later.
Thanks for your help, Radek

Anonymous
Sat 13 Jan 2018 04:56:36 PM UTC, comment #3: 

Hi, thanks for the bug report.

Rather than code, could you please upload the spreadsheet that isn't read correctly? or if it's too big, reduce it so that it portrays the error?
Thanks

(adapted title for reference)

Philip Nienhuis <philipnienhuis>
Group Member
Fri 12 Jan 2018 11:38:24 PM UTC, comment #2: 

It seems I cannot get the code to be properly inserted here so I am attaching it below as the attachment file.

(file #42897)

Anonymous
Fri 12 Jan 2018 11:31:47 PM UTC, comment #1: 

I think I found a solution to this problem Replace line 91 in OCT_xlsx2oct.m with   val = regexp (rawdata, '<c r="(\w+)"(?:[^t>]*(>)|(?:[^t>]*t="(\w+)")>)(?:<f.+?(?:</f>|/>))?(?:<v(?:[^>]*)>([^<]*)</v>|<is><t>([^<]*)</t></is>|<v/>([^<]*))', "tokens");

Anonymous
Fri 12 Jan 2018 09:14:08 PM UTC, original submission:  

(io-2.4.9) Installed Octave 4.2.1 and IO Package 2.4.9. Read values from Excel sheet using xlsread function. Multiple cells returned NaN value rather than the original value. Debugging pinpointed the problem to the line 91 of _OCT_xlsx2oct_.m (reading xml file). Empty value tag (<v/>) due to a formula in Excel causes multiple cells to be skipped. Attaching two files below: original xml file and file with val values after the line 91 is executed.

Anonymous

 

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

Attach Files:
   
   
Comment:
   

Attached Files
file #42995:  Excel2016_emptyFormulaResult.xlsx added by mmuetzel (8KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #42914:  Octave bug4.xlsx added by None (23KiB - application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
file #42897:  Bug fix.txt added by None (167B - text/plain)
file #42895:  Octave bug1.txt added by None (33KiB - text/plain)
file #42896:  Octave bug3.txt added by None (13KiB - text/plain)

 

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 (Updated the item)
  • -email is unavailable- added by philipnienhuis
  • -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 17 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2022-10-25 philipnienhuis StatusReady For Test Fixed
        Open/ClosedOpen Closed
    2018-07-02 jwe Summary[octave-forge] (io-2.4.9) Empty value tag (&lt;v/&gt;) due to a formula in Excel causes multiple cells to be skipped [octave forge] (io) Empty value tag (<v/>) due to a formula in Excel causes multiple cells to be skipped
    2018-04-22 philipnienhuis Severity3 - Normal 2 - Minor
        Priority5 - Normal 3 - Low
    2018-01-20 mmuetzel Attached File- Added Excel2016_emptyFormulaResult.xlsx, #42995
    2018-01-18 philipnienhuis StatusNeed Info Ready For Test
        Assigned toNone philipnienhuis
    2018-01-15 philipnienhuis Carbon-Copy- Added mmuetzel
    2018-01-15 None Attached File- Added Octave bug4.xlsx, #42914
    2018-01-13 philipnienhuis StatusNone Need Info
        Release4.2.1 other
        Operating SystemMicrosoft Windows Any
        Summary(io-2.4.9) _OCT_xlsx2oct_.m Empty value tag (&lt;v/&gt;) due to a formula in Excel causes multiple cells to be skipped [octave-forge] (io-2.4.9) Empty value tag (<v/>) due to a formula in Excel causes multiple cells to be skipped
    2018-01-12 None Attached File- Added Bug fix.txt, #42897
    2018-01-12 None Attached File- Added Octave bug1.txt, #42895
        Attached File- Added Octave bug3.txt, #42896

    Back to the top

    Powered by Savane 3.13-4448.
    Corresponding source code