Problem with oct2xls range

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Problem with oct2xls range

Theodoros Samaras

Greetings,

 

Starting just recently to use octave, I encountered the following situation while trying to export data to an .xlsx spreadsheet.

 

pkg load io;

file = 'test.xlsx';

A = zeros(4,4)

for i=1:rows(A)

  for j=1:columns(A)

    A(i,j)=int32(100*rand());

  endfor

endfor

rownum=1;

##The following command exports all the matrix elements in cells A1:D4

status=xlswrite(sprintf('%c',file'),A,'Sheet1',sprintf('A%d',rownum'));

 

   17   50   43   85

   88    7    9   40

   73    3   41   79

   63   90   74    0

 

 

##While the following will write ONLY the first matrix element in cell A1

XLS = xlsopen(file);

[XLS,status] = oct2xls(A,XLS,'Sheet1',sprintf('A%d',rownum'));

XLS=xlsclose(XLS);

 

   17 

 

Is there any difference in range definition between the two functions ?

Am I doing something wrong ?

Tested in more than machines with different OS (Win 7, Win 10) running GNU Octave 5.1.0   and io-2-4-12.

 

Any ideas ?

 

Thanking you in advance

 

Th. S

 

 

 

 

 



Reply | Threaded
Open this post in threaded view
|

Re: Problem with oct2xls range

nrjank
   17   50   43   85

   88    7    9   40

   73    3   41   79

   63   90   74    0

 

 

##While the following will write ONLY the first matrix element in cell A1

XLS = xlsopen(file);

[XLS,status] = oct2xls(A,XLS,'Sheet1',sprintf('A%d',rownum'));

XLS=xlsclose(XLS);

 

   17 

 



verifying that I can reproduce this issue with oct2xls, with or without the windows package loaded to use the Excel COM interface.
Octave 5.1.0 on Windows 10 Pro with Office 2013 installed, using packages io 2.4.12 and windows 1.3.1:


>> pkg load io
>> chk_spreadsheet_support ()
ans = 0
>> A = int32(100*rand(5))
A =
   2  70  45  45  14
  99  79  68   5  30
  45  85   8  72  33
  99  90  47  30  10
  19  19  75  92  63
>> rownum = 1
rownum =  1
>> status = xlswrite('xlswritetest.xlsx',A,'Sheet1','A1')
Detected XLS interfaces: warning: strmatch is obsolete; use strncmp or strcmp instead
status =  1

output file xlswritetest.xlsx is created and contains the full array A.

>> XLS = xlsopen('o2xtest.xlsx',1)
XLS =
  scalar structure containing the fields:
    xtype = OCT
    app = xlsx
    filename = o2xtest2.xlsx
    workbook = c:\Programs\Octave\Temp\oct-ZATASh
    changed =  3
    limits = [](0x0)
    sheets =
      scalar structure containing the fields:
        sh_names =
        {
          [1,1] = Sheet1
        }
        rid =  1
        sheetid =  1
        shId =  1
        type =  1

>> [XLS,status] = oct2xls(A,XLS, 'Sheet1', 'A1')
XLS =
  scalar structure containing the fields:
    xtype = OCT
    app = xlsx
    filename = o2xtest.xlsx
    workbook = c:\Programs\Octave\Temp\oct-ZATASh
    changed =  2
    limits = [](0x0)
    sheets =
      scalar structure containing the fields:
        sh_names =
        {
          [1,1] = Sheet1
        }
        rid =
           1   2
        sheetid =  1
        shId =  1
        type =  1

>> XLS = xlsclose(XLS)

The output file o2xtest.xlsx only has the A(1,1) value written in cell A1.  looking in the workbook temp location mentioned in the struct, the workbook.xml file only contains information for that single cell.

According to the oct2xls help file:

"If RANGE is omitted or just the top left cell of the range is specified, the actual range to be used is determined by the size of ARR.  If nothing is specified for RANGE the top left cell is assumed to be 'A1'.
...
If RANGE contains merged cells, only the elements of ARR corresponding to the top or left Excel cells of those merged cells will be written, other array cells corresponding to that cell will be ignored."

So, according to the help it _should_ be producing the same output as xlswrite, unless that latter condition is somehow being invoked (intentionally or accidentally).  In any case, something seems off. 

I get the exact same behavior with or without windows package loaded (i.e, with chk_spreadsheet_support () = 1)



Reply | Threaded
Open this post in threaded view
|

Re: Problem with oct2xls range

PhilipNienhuis
In reply to this post by Theodoros Samaras
Theodoros Samaras wrote

> Greetings,
>
> Starting just recently to use octave, I encountered the following
> situation
> while trying to export data to an .xlsx spreadsheet.
>
>
> pkg load io;
>
> file = 'test.xlsx';
>
> A = zeros(4,4)
>
> for i=1:rows(A)
>
>   for j=1:columns(A)
>
>     A(i,j)=int32(100*rand());
>
>   endfor
>
> endfor
>
> rownum=1;
>
> ##The following command exports all the matrix elements in cells A1:D4
>
> status=xlswrite(sprintf('%c',file'),A,'Sheet1',sprintf('A%d',rownum'));
>
>
>    17   50   43   85
>
>    88    7    9   40
>
>    73    3   41   79
>
>    63   90   74    0
>  
>
> ##While the following will write ONLY the first matrix element in cell A1
>
> XLS = xlsopen(file);
>
> [XLS,status] = oct2xls(A,XLS,'Sheet1',sprintf('A%d',rownum'));
>
> XLS=xlsclose(XLS);
>
>  
>
>    17  
>
>  
>
> Is there any difference in range definition between the two functions ?
>
> Am I doing something wrong ?
>
> Tested in more than machines with different OS (Win 7, Win 10) running GNU
> Octave 5.1.0   and io-2-4-12.
>
> Any ideas ?

Can you file a bug report, please?
The issue is that specifying an address of just one single cell rather than
a range (i.e., rather than using ":" between cell addresses) shouldn't
truncate the output array.

Thanks,

Philip



--
Sent from: http://octave.1599824.n4.nabble.com/Octave-General-f1599825.html


Reply | Threaded
Open this post in threaded view
|

Re: Problem with oct2xls range

nrjank
done.

https://savannah.gnu.org/bugs/index.php?56444

On Wed, Jun 5, 2019 at 1:28 PM PhilipNienhuis <[hidden email]> wrote:

>
> Theodoros Samaras wrote
> > Greetings,
> >
> > Starting just recently to use octave, I encountered the following
> > situation
> > while trying to export data to an .xlsx spreadsheet.
> >
> >
> > pkg load io;
> >
> > file = 'test.xlsx';
> >
> > A = zeros(4,4)
> >
> > for i=1:rows(A)
> >
> >   for j=1:columns(A)
> >
> >     A(i,j)=int32(100*rand());
> >
> >   endfor
> >
> > endfor
> >
> > rownum=1;
> >
> > ##The following command exports all the matrix elements in cells A1:D4
> >
> > status=xlswrite(sprintf('%c',file'),A,'Sheet1',sprintf('A%d',rownum'));
> >
> >
> >    17   50   43   85
> >
> >    88    7    9   40
> >
> >    73    3   41   79
> >
> >    63   90   74    0
> >
> >
> > ##While the following will write ONLY the first matrix element in cell A1
> >
> > XLS = xlsopen(file);
> >
> > [XLS,status] = oct2xls(A,XLS,'Sheet1',sprintf('A%d',rownum'));
> >
> > XLS=xlsclose(XLS);
> >
> >
> >
> >    17
> >
> >
> >
> > Is there any difference in range definition between the two functions ?
> >
> > Am I doing something wrong ?
> >
> > Tested in more than machines with different OS (Win 7, Win 10) running GNU
> > Octave 5.1.0   and io-2-4-12.
> >
> > Any ideas ?
>
> Can you file a bug report, please?
> The issue is that specifying an address of just one single cell rather than
> a range (i.e., rather than using ":" between cell addresses) shouldn't
> truncate the output array.
>
> Thanks,
>
> Philip
>
>
>
> --
> Sent from: http://octave.1599824.n4.nabble.com/Octave-General-f1599825.html
>
>