bugGNU Octave - Bugs: bug #45303, xlsread in io package can't handle...

 
 

bug #45303: xlsread in io package can't handle Excel worksheet reordering

Submitted by:  Zachary Strider McGregor-Dorsey <tarrou>
Submitted on:  Thu 11 Jun 2015 04:52:26 PM 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)

Mon 22 Jun 2015 02:47:26 PM UTC, comment #7:

I consider this fixed.
I've released io-2.2.8 with the fix.

Closing report

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 19 Jun 2015 04:18:12 PM UTC, comment #6:

(Hmmm my last comment didn't come through)

The "...another bug" I spotted was related to the original one here, so was fixed along the way.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 19 Jun 2015 12:12:33 PM UTC, comment #5:

Yep this was an easy fix, in code I've simplified before but apparently not enough.

The attached cs fixes it for me. I'll push later today.
An io-2.2.8 is in the making anyway; but if you can't wait, or want to test, just swap the attached _OCT_xlsx2oct_.m with the one in <OCTAVE_HOME>/share/octave/packages/io-2.2.7/private/ and please report back if it works for you as well.

(On the way I spotted another bug when writing to .xslx, I'll try to fix that one later today.)

(file #34260, file #34261)

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 19 Jun 2015 10:32:48 AM UTC, comment #4:

Confirmed. I made an .xlsx workbook 'testsheet.xlsx' with four worksheets and some tracking contents, shifted worksheet #3 ('Sheet 3') to position #1 and got the following:

...so xlsfinfo does it right but the rest not.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 12 Jun 2015 06:43:40 PM UTC, comment #3:

"not stellar" - no worries, your answers are clear enough :-)

I'll have a look at the OCT interface stuff.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Fri 12 Jun 2015 01:10:46 PM UTC, comment #2:

Hi,

Thanks for these questions. I'm sorry my bug reporting is not stellar; this is the first time I have done so.

  1. How do you reorder sheets? Do you do that in Excel?

Yes, I reorder the sheets in Excel (by manually moving them). (This document is filled in by hand by other people, so lots of shenanigans may ensue.)

  1. What io package version?

2.2.7

  1. What interface does the io package report when you open an Excel file for the first time in a new Octave session? COM? OCT? UNO? POI?

OCT

  1. Do other interfaces than the one you used correctly pick up the proper worksheet?

I'm not sure. When I try to force the interface, it says "interface not supported" unless I use OCT. So I'm unable to test this.

thanks!

Zachary Strider McGregor-Dorsey <tarrou>
Thu 11 Jun 2015 08:06:51 PM UTC, comment #1:

A number of questions:

1. How do you reorder sheets? Do you do that in Excel?

2. What io package version?

3. What interface does the io package report when you open an Excel file for the first time in a new Octave session? COM? OCT? UNO? POI?

4. Do other interfaces than the one you used correctly pick up the proper worksheet?

Worksheet reordering is indeed a tricky issue. The main concern of the io developers was to be able to read and write (into) worksheets, not so much reordering them.

FYI, that is works in Matlab is obvious - Matlab invokes Excel itself behind the scenes. The io package can do that too using the COM interface. For other interfaces we have to mimic Excel's behavior in Octave as far as we can, but that isn't always possible.

Similar bugs has been fixed in (IIRC) io-2.2.5; some care was taken to comply with the OOXML spec but maybe (possibly) this part needs revisiting.

Philip Nienhuis <philipnienhuis>
Project MemberIn charge of this item.
Thu 11 Jun 2015 04:52:26 PM UTC, original submission:

When you manually reorder worksheets in Excel, the sheetId in the underlying xml file stays the same, but the order of the sheets in the <sheets></sheets> changes. Before reordering, sheetIds just go from 1 to n, in order.

It appears that the io package, when you run xlsread(filename, worksheetname), finds the worksheet name, looks at the sheetId and imports the data from the sheet that corresponds to that number. If the "Sheet3" has sheetId is 3, xlsread(filename, 'Sheet3') imports the 3rd sheet in <filename>.

However, if you move Sheet3 to the beginning of the workbook, it is now the first sheet as indicated by its placement in the <sheets></sheets> list, but sheetId remains 3. Thus xlsread(filename, 'Sheet3') still imports the 3rd sheet, which is wrong.

A similar problem occurs when you delete a sheet (so, for example, the 3rd sheet becomes the 2nd). Also, I should mention that this works in MatLab.

Hopefully, this is an easy fix.

Zachary Strider McGregor-Dorsey <tarrou>

 

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

Attach File(s):
   
   
Comment:
   

Attached Files
file #34260:  bug45303.cs added by philipnienhuis (842B - text/x-csharp)
file #34261:  __OCT_xlsx2oct__.m added by philipnienhuis (15KiB - text/x-objective-c)

 

Depends on the following items: None found

Items that depend on this one: None found

 

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

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

    Date Changed By Updated Field Previous Value => Replaced By
    Mon 22 Jun 2015 02:47:26 PM UTCphilipnienhuisStatusReady For Test=>Fixed
      Open/ClosedOpen=>Closed
    Fri 19 Jun 2015 12:12:33 PM UTCphilipnienhuisAttached File-=>Added bug45303.cs, #34260
      Attached File-=>Added _OCT_xlsx2oct_.m, #34261
      StatusConfirmed=>Ready For Test
    Fri 19 Jun 2015 10:32:48 AM UTCphilipnienhuisStatusNeed Info=>Confirmed
      Assigned toNone=>philipnienhuis
      Release4.0.0=>other
      Operating SystemMicrosoft Windows=>Any
    Thu 11 Jun 2015 08:06:51 PM UTCphilipnienhuisStatusNone=>Need Info
    Thu 11 Jun 2015 04:52:26 PM UTCtarrouCarbon-Copy-=>Added tarrou

    Back to the top


    Powered by Savane 3.1-cleanup1