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

 
 

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

Submitted by:  None
Submitted on:  Fri 12 Jan 2018 09:14:08 PM UTC  
 
Category: Octave Forge PackageSeverity: 3 - Normal
Priority: 5 - NormalItem Group: Incorrect Result
Status: Ready For TestAssigned to: Philip Nienhuis <philipnienhuis>
Originator Name: radekivvOriginator Email: -unavailable-
Open/Closed: OpenRelease: 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 Jump to the original submission)

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>
Project Member
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>
Project MemberIn charge of this item.
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>
Project MemberIn charge of this item.
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>
Project MemberIn charge of this item.
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>
Project MemberIn charge of this item.
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 File(s):
   
   
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
  • -unavailable- added by mmuetzel (Updated the item)
  • -unavailable- added by philipnienhuis
  • -unavailable- added by None (Submitted the item)
  •  

    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
    Sat 20 Jan 2018 09:59:54 PM UTCmmuetzelAttached File-=>Added Excel2016_emptyFormulaResult.xlsx, #42995
    Thu 18 Jan 2018 08:31:36 PM UTCphilipnienhuisStatusNeed Info=>Ready For Test
      Assigned toNone=>philipnienhuis
    Mon 15 Jan 2018 10:30:48 PM UTCphilipnienhuisCarbon-Copy-=>Added mmuetzel
    Mon 15 Jan 2018 03:42:14 PM UTCNoneAttached File-=>Added Octave bug4.xlsx, #42914
    Sat 13 Jan 2018 04:56:36 PM UTCphilipnienhuisStatusNone=>Need Info
      Release4.2.1=>other
      Operating SystemMicrosoft Windows=>Any
      Summary(io-2.4.9) _OCT_xlsx2oct_.m Empty value tag (<v/>) due to a formula in Excel causes multiple cells to be skipped=>[octave-forge] (io-2.4.9) Empty value tag () due to a formula in Excel causes multiple cells to be skipped
    Fri 12 Jan 2018 11:38:24 PM UTCNoneAttached File-=>Added Bug fix.txt, #42897
    Fri 12 Jan 2018 09:14:08 PM UTCNoneAttached File-=>Added Octave bug1.txt, #42895
      Attached File-=>Added Octave bug3.txt, #42896

    Back to the top


    Powered by Savane 3.1-cleanup1