bugGNU Octave - Bugs: bug #45498, Minor bugs with xlswrite from...

 
 

bug #45498: Minor bugs with xlswrite from package io-2.2.8

Submitter:  None
Submitted:  Wed 08 Jul 2015 08:10:04 PM UTC
   
 
Category:  Octave Package Severity:  3 - Normal
Priority:  5 - Normal Item Group:  Incorrect Result
Status:  Fixed Assigned to:  philipnienhuis
Originator Name:  Originator Email:  -email is unavailable-
Open/Closed:  * Closed Release:  * 4.0.0
Operating System:  * Any Fixed Release:  None
Planned Release:  None
* Mandatory Fields

Add a New Comment Rich Markup
   

Jump to the original submission

Mon 13 Jul 2015 06:23:27 PM UTC, comment #7: 

Thank you for all your work.

Certainly there are good reasons to deviate from Matlab compatibility in Octave commands. On the other hand, many users of Octave may have developed complex programs at first in Matlab and want to transfer the code for farer distribution to Octave. Then this task is easy, provided that the Octave commands are compatible to Matlab as far as possible.

As you mentioned in comment #1 there are possibilities for settings to mimic Matlab and override the more sensible Octave behaviour. So in my opinion it would be a good solution to have additional parameters in Octave commands to switch on the Octave syntax or to be compatible with Matlab. 
The different behaviour in Matlab xlswrite in creating 3 empty worksheets (your comment #2) in my opinion is not as important, since only those worksheets matter, which are deliberately programmed and written to by the programmer.

In connection with a broad distribution of Matlab/Octave programs, it would be a very useful feature to have the possibility to "compile" an Octave program together with its sub-routines to one executable, in order to be executed on other machines, which have not necessarily installed Matlab or even no Octave version.

Anonymous
Fri 10 Jul 2015 12:12:27 PM UTC, comment #6: 

Write precision has been fixed; now up to 15 digits are preserved when using the OCT interface and very small numbers (and very large ones) are kept as well.

Along the way I hit a few other bugs, fixed as well.

A few minutes ago I've uploaded io-2.2.9 with these bug fixes to the release tracker, with a little luck it can be downloaded soon.

I have no plans to change the Matlab incompatibilities soon; that might be tackled as soon as the spreadsheet I/O goes to core later this year, at the discretion of Octave's lead dev. Several issues
have to be discussed and fixed anyway then.

Thanks for your investigations (very helpful) and bug report.

Closing report.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 10 Jul 2015 08:52:14 AM UTC, comment #5: 

As to your second bug, I mixed a few things up:
I overlooked the small values issue and figured you complained about the worksheet stack incompatibilities.

Sorry about that, heavy weeks behind me.

I'll look into that now.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 10 Jul 2015 08:50:07 AM UTC, comment #4: 

Fixed those errors with your first bug here:
http://hg.code.sf.net/p/octave/io/rev/9383a48f0c8a

This will be in a io-2.2.9 release. If you can't wait, grab  _OCT_oct2xlsx_.m from the repo (http://hg.code.sf.net/p/octave/io/file/9383a48f0c8a/inst/private/__OCT_oct2xlsx__.m, click "raw", Save As). For convenience I'll attach it here.
Put it in <Octave_install_dir>/share/octave/packages/io-2.2.8/private

Philip Nienhuis <philipnienhuis>
Group Member
Fri 10 Jul 2015 07:54:38 AM UTC, comment #3: 

I'll tackle the errors you gave for your first bug later today.

Philip Nienhuis <philipnienhuis>
Group Member
Fri 10 Jul 2015 07:52:35 AM UTC, comment #2: 

As to your first bug:
Actually this works as intended as well:

>> arr = [1 2; 3 4]
arr =

   1   2
   3   4

>> xlswrite('filename.xlsx', arr, 2, 'B2:Z100', 'oct')
Checking requested interface(s):
OCT*; (* = default interface)
ans =  1
>> xlsfinfo ('filename.xlsx')
  1: Sheet2                           (Used range ~ B2:C3)
ans = Microsoft Excel Spreadsheet


..although it isn't Matlab-compatible, acknowledged.

Again this is an example of Matlab's dependency on MS-Excel behavior:
When Excel creates a new spreadsheet file ("workbook") it -by default !- creates three empty worksheets.
Now, Matlab relies on Excel's default behavior here and blindly assumes three worksheets are there. Octave doesn't as it does not rely on Excel. It simply creates just one worksheet called "Sheet2".
One can instruct Excel to just make one empty worksheet when creating a new workbook. Not sure what Matlab would do then with Excel, I'll try later.

In the io package, when Excel is used (with the COM interface - "interface" refers to support SW) and a new workbook is created, the various I/O routines delete all extra worksheets except the one that was written to. This was done to maintain similar behavior for all interfaces.

I'm not sure if we should follow Matlab here. Octave's (=io package's) other spreadsheet interfaces simply have no provisions for this behavior. They can be instructed to do so, but I'm afraid at the cost of possibly undue and fragile overhead.
At the minimum I'll add a warning that just one worksheet is created.

Philip Nienhuis <philipnienhuis>
Group Member
Wed 08 Jul 2015 09:25:01 PM UTC, comment #1: 

Thanks for the extensive testing & reporting.

As to bug #1: Should be an easy fix, I'll look into it soon.

As to bug #2: This was an explicit choice from the very start of the spreadsheet I/O in Octave's io package.
It was made to protect existing cell contents outside the specified range against accidental overwrites when arrays had been silently extended by over-indexing (something that I often saw happening at my work).
I was never fond of this and other choices made by Matlab (for another example: that what happens when writing arrays smaller then the specified range - the "unused" cells in the specified range are filled with bogus info that screwed up ensuing operations for me).  Much of ML behavior is actually dependent on MS-Excel that ML relied on from the start.

One workaround for you until some next io release would be to assess the array size in each cycle and then compute the current range-to-be-specified using calccelladdress() (also in the io package).

For me as developer an option would be using the argument "spsh_opts" that is called in the oct2xls.m function (that is invoked by xlswrite). xlswrite could call oct2xls.m with some setting for mimicking Matlab behavior (and overriding the IMO more sensible behavior now in io).
I think such a trick could be a good solution to try to mimic much other Matlab behavior in xlswrite and xlsread, esp. as there are plans to move the entire spreadsheet I/O into core Octave.

OS -> any

Philip Nienhuis <philipnienhuis>
Group Member
Wed 08 Jul 2015 08:10:04 PM UTC, original submission:  

xlswrite can be used after installing the io-package with:

pkg install io-2.2.8.tar.gz

The io-2.2.8.tar.gz file has to be located in the Octave working directory. Then

pkg load io

has to be entered.
The following bugs with xlswrite are observed:

1)   writing a second Excel-sheet to an existing file with
     xlswrite('filename.xlsx', arr, 2, 'B2:Z100')
will result in an error. If filename.xlsx does not exist, the Excel-file is created with "Sheet2" as first table. In order to add a second sheet to an existing file, the sheet-name has to be written explicitly with  apostrophes:
     xlswrite('filename.xlsx', arr, 'Sheet3', 'B2:Z100')

2)   Small numbers in array arr will lose precision, when written to the Excel-file. Numbers below 1e-12 are written as 0 to the file. For an array of small numbers all information is lost.

3)   When an Excel-file is newly created by xlswrite it must not exist. An error results, if an Excel file, e.g. 'filename.xlsx', exists in the starting directory of the Octave-run, although the program has changed to a sub-directory where no such file exists and where the file has to be created.

 
Here is an example of program execution with Octave 4.0.0 for bug 1):

>>  arr=rand(3,4); xlswrite('filename.xlsx',arr,1,'B2:Z100')

Detected XLS interfaces: ans =  1

>>  xlswrite('filename.xlsx', arr, 2, 'B2:Z100')

error: 'string' undefined near line 127 column 19
error: called from
    _OCT_oct2xlsx_ at line 127 column 7
    _OCT_oct2spsh_ at line 74 column 18
    oct2xls at line 207 column 18
    xlswrite at line 218 column 20
error: evaluating argument list element number 1
error: called from
    _OCT_oct2xlsx_ at line 127 column 7
    _OCT_oct2spsh_ at line 74 column 18
    oct2xls at line 207 column 18
    xlswrite at line 218 column 20


An example for bug 2) is provided below:

arr=rand(3,4); xlswrite('filename.xlsx',arr,1,'B2:Z100')
xlswrite('filename.xlsx', arr*1e-4, 'Sheet2', 'B2:Z100')
xlswrite('filename.xlsx', arr*1e-12,'Sheet2', 'B6:Z100')

This sequence of commands leads to an Excel file 'filename.xlsx' with the original array arr in Sheet1 and scaled arrays arr with lost precision in Sheet2.


In order to be compatible with Matlab syntax, it would also be desirable not to restrict the array to the cell region specified in xlswrite. E.g. in Matlab xlswrite('filename.xlsx',arr,1,'B2') writes the full array arr starting at B2 as upper left cell. Octave only writes the first element of arr to B2. However, different program runs can create different sizes of array arr. In that case the Matlab syntax is more convenient. As a workaround it is  possible in Octave to give a cell region larger than the extension of arr, but this does not lead to a generally valid programming technique.

Anonymous

 

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

Attach Files:
   
   
Comment:
   

No files currently attached

 

Depends on the following items: None found

Items that depend on this one: None found

 

Carbon-Copy List
  • -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 5 latest changes.

    Date Changed by Updated Field Previous Value => Replaced by
    2015-07-10 philipnienhuis StatusIn Progress Fixed
        Open/ClosedOpen Closed
    2015-07-10 philipnienhuis StatusNone In Progress
    2015-07-08 philipnienhuis Assigned toNone philipnienhuis
        Operating SystemMicrosoft Windows Any

    Back to the top

    Powered by Savane 3.13-caa5.
    Corresponding source code