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

 
 

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

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

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>
Group Member
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>
Group Member
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>
Group Member
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:

>> xlsfinfo ('testsheet.xlsx', 'oct')
  1: Sheet3                           (Used range ~ C3:C3)
  2: Sheet1                           (Used range ~ A1:A1)
  3: Sheet2                           (Used range ~ B2:B2)
  4: Sheet4                           (Used range ~ D4:D4)
ans = Microsoft Excel Spreadsheet
>> [~, ~, raw] = xlsread ('testsheet.xlsx', 'Sheet1')
raw =
{
  [1,1] = Sheet 3
}
>>


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

Philip Nienhuis <philipnienhuis>
Group Member
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>
Group Member
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>
Group Member
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 Files:
   
   
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
  • -email is unavailable- added by tarrou (Submitted the item)
  • -email is unavailable- added by tarrou
  •  

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

    Date Changed by Updated Field Previous Value => Replaced by
    2015-06-22 philipnienhuis StatusReady For Test Fixed
        Open/ClosedOpen Closed
    2015-06-19 philipnienhuis Attached File- Added bug45303.cs, #34260
        Attached File- Added _OCT_xlsx2oct_.m, #34261
        StatusConfirmed Ready For Test
    2015-06-19 philipnienhuis StatusNeed Info Confirmed
        Assigned toNone philipnienhuis
        Release4.0.0 other
        Operating SystemMicrosoft Windows Any
    2015-06-11 philipnienhuis StatusNone Need Info
    2015-06-11 tarrou Carbon-Copy- Added tarrou

    Back to the top

    Powered by Savane 3.13-02a9.
    Corresponding source code