Nan and write on excel "65535"

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

Nan and write on excel "65535"

Octave - General mailing list
hi,
before with version octave 4.4.1 i write on excel an array with nan value
(A=nan(rows(3),columns(3)) and i saw empty cells

Now with version 5.5.X i see on excel 65535

I try to use NA (A=NA(rows(3),columns(3)) but the problem is the same :(






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


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

siko1056
On Thu, May 16, 2019 at 8:35 PM shivax via Help-octave <[hidden email]> wrote:
hi,
before with version octave 4.4.1 i write on excel an array with nan value
(A=nan(rows(3),columns(3)) and i saw empty cells

Now with version 5.5.X i see on excel 65535

I try to use NA (A=NA(rows(3),columns(3)) but the problem is the same :(


Please be a bit more verbose about your problem.  For example your Octave version is 5.1.0. Do you use the io-package for Excel im-/export in what version and how do you export your matrix to Excel?  Give a small code excerpt what I can copy and paste into my Octave and see the results you are struggling with.

Best,
Kai


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

Octave - General mailing list
hi,
i give you some screenshot about my code

https://gyazo.com/5b93c88546b92bfb10a2336df72c24dc


https://gyazo.com/df6114e0b38131098c96a861a4ee4d95


in the top of code i write this :

pkg load windows  % queste 2 RIGHE SCRIVONO NEI FOGLIO EXCEL
pkg load io
pkg load nan

with a version octave 4,x i don't had a problem

I upgrade version to octave 5.x and i see this problem :(



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


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

PhilipNienhuis
help-octave-3 wrote

> hi,
> i give you some screenshot about my code
>
> https://gyazo.com/5b93c88546b92bfb10a2336df72c24dc
>
>
> https://gyazo.com/df6114e0b38131098c96a861a4ee4d95
>
>
> in the top of code i write this :
>
> pkg load windows  % queste 2 RIGHE SCRIVONO NEI FOGLIO EXCEL
> pkg load io
> pkg load nan
>
> with a version octave 4,x i don't had a problem
>
> I upgrade version to octave 5.x and i see this problem :(

From the information you gave us it is impossible to find out what you did,
what you want, and what went wrong.
Screenshots are no help, especially if you show just a little piece. We need
code a minimal code example plus data to show the error so that we can try
to reproduce the errors you see.

As you say it is writing to an Excel file goes wrong, what you can do is
saving the array you want to write to an Excel file using the command:

save -v6 name_of_mat_file.mat <array>

(substitute the name of the array you want to write in <array>)
and upload it somewhere.
Then we can try to find out what happens.

By the way, what operating system do you use? Windows, Linux or Mac OS?

Philip




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


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

Octave - General mailing list
thank for your answer..

i do a minimal code to see you


test_WriteExcel.m
<http://octave.1599824.n4.nabble.com/file/t372898/test_WriteExcel.m>  


i use window10 professional





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


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

Octave - General mailing list
in case of problem to open file

here i write the code:


function ret=test_WriteExcel()
 
  global OutputEquity;

strx="Test.xlsx";
 
printf("\n PRINT EQUITY \n");


nomeFileExcel=strcat(OutputEquity,strx);
nomeFoglio="Test";
riga=1;
colonna=1;
deleteFileExcel=true;


 newNomeEquity(1,1)= {"    "};  
 newNomeEquity(2,1)="Data Rilev";
 newNomeEquity(1,2)= NA;  
 newNomeEquity(2,2)="Index Rilev";
 con=0;
 
filtro_Bypass=NA(33,4);
filtro_Bypass
 
 for fr=1:columns(filtro_Bypass)  
con++;
  matrixx(:,con)=filtro_Bypass(:,fr);
  newNomeEquity(1,con+2)=NA;
  newNomeEquity(2,con+2)="FiltroBYPass";    
con++;  
  matrixx(:,con)=NA;
  newNomeEquity(1,con+2)=NA;  
  newNomeEquity(2,con+2)=NA;  
endfor


if deleteFileExcel
      delete(nomeFileExcel);
      endif



strCompletaNum=TitanTrovaStringExcel(riga,colonna+2,rows(matrixx),columns(matrixx));

nomeFileExcel
rstatus=xlswrite(nomeFileExcel,matrixx,nomeFoglio,strCompletaNum);  




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


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

Octave - General mailing list
i miss to write a global variable…

i repeat code complete (before you must to create folder c:\TEST)

function ret=test_WriteExcel()
 
strx="Test.xlsx";
 
printf("\n PRINT EQUITY \n");


nomeFileExcel="c:/test/test.xlsx"
nomeFoglio="Test";
riga=1;
colonna=1;
deleteFileExcel=true;


 newNomeEquity(1,1)= {"    "};  
 newNomeEquity(2,1)="Data Rilev";
 newNomeEquity(1,2)= NA;  
 newNomeEquity(2,2)="Index Rilev";
 con=0;
 
filtro_Bypass=NA(33,4);
filtro_Bypass
 
 for fr=1:columns(filtro_Bypass)  
con++;
  matrixx(:,con)=filtro_Bypass(:,fr);
  newNomeEquity(1,con+2)=NA;
  newNomeEquity(2,con+2)="FiltroBYPass";    
con++;  
  matrixx(:,con)=NA;
  newNomeEquity(1,con+2)=NA;  
  newNomeEquity(2,con+2)=NA;  
endfor


if deleteFileExcel
      delete(nomeFileExcel);
      endif

strCompletaNum=TitanTrovaStringExcel(riga,colonna+2,rows(matrixx),columns(matrixx));

nomeFileExcel
rstatus=xlswrite(nomeFileExcel,matrixx,nomeFoglio,strCompletaNum);  



here output:

https://gyazo.com/ce9f9d8b29c00b0a916db26723034ddf



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


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

Octave - General mailing list

 add previous function this row:


pkg load windows  
pkg load io


( Is it possible to edit the post in case of errors??)







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


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

nrjank

( Is it possible to edit the post in case of errors??)


This is a mailing list not a web forum, so no.

Trying to run your code, saving the test_WriteExcel.m file in a C:\TEST folder, I get the following:

>> pkg load windows
>> pkg load io
>> test_WriteExcel
                                                                                                
 PRINT EQUITY
filtro_Bypass =
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
warning: delete: no such file: Test.xlsx
warning: called from
    delete at line 47 column 9
    test_WriteExcel at line 40 column 7
error: 'TitanTrovaStringExcel' undefined near line 45 column 16
error: called from
    test_WriteExcel at line 45 column 15



if I first create a blank 'Test.xlsx' file in C:\TEST, then I get:

                                                                                                
 PRINT EQUITY
filtro_Bypass =
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
    NA    NA    NA    NA
error: 'TitanTrovaStringExcel' undefined near line 45 column 16
error: called from
    test_WriteExcel at line 45 column 15


and then it deletes the blank Test.xlsx file.



I don't think this is emulating the error you're seeing, but I don't have time at the moment to debug the problem with your test script.  If you can generate another test script and a set of complete instructions for one of us to test it, we can give it a try.



Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

Octave - General mailing list
excuse me

I forgot to mention some features that my code refers to

Try this code:
(Create folder c:\test)


function ret=test_WriteExcel()
 
strx="Test.xlsx";
 
printf("\n PRINT EQUITY \n");

pkg load windows
pkg load io

nomeFileExcel="c:/test/test.xlsx"
nomeFoglio="Test";
riga=1;
colonna=1;
deleteFileExcel=true;


 newNomeEquity(1,1)= {"    "};  
 newNomeEquity(2,1)="Data Rilev";
 newNomeEquity(1,2)= NA;  
 newNomeEquity(2,2)="Index Rilev";
 con=0;
 
filtro_Bypass=NA(33,4);
filtro_Bypass
 
 for fr=1:columns(filtro_Bypass)  
con++;
  matrixx(:,con)=filtro_Bypass(:,fr);
  newNomeEquity(1,con+2)=NA;
  newNomeEquity(2,con+2)="FiltroBYPass";    
con++;  
  matrixx(:,con)=NA;
  newNomeEquity(1,con+2)=NA;  
  newNomeEquity(2,con+2)=NA;  
endfor


if deleteFileExcel
      delete(nomeFileExcel);
      endif

strCompletaNum ="C1:J33"

nomeFileExcel
rstatus=xlswrite(nomeFileExcel,matrixx,nomeFoglio,strCompletaNum);  
endfunction



Output:

https://gyazo.com/66da3e52b9efac41dae12c306930ba31



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


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

Przemek Klosowski-7
On 5/20/19 10:39 AM, shivax via Help-octave wrote:
> excuse me
>
> I forgot to mention some features that my code refers to
>
There's a good writeup of how to ask for help:

https://stackoverflow.com/help/reprex

Other people start with zero information about what you want to
accomplish, what data you have, what code you tried, what computer you
use, etc. You have to provide this information, hopefully in an
organized way that makes it easy to start working on your question.

Try putting yourself in the role of the person you are asking for help:
you have to start from scratch, perhaps create a new, empty directory
and copy some files you need, and then copy or write some small section
of code that demonstrated the problem. If you really follow that 'from
scratch' procedure, you'd avoid forgetting to pass on some assumptions
or information, because your own example would not work for you.

As a side benefit to this approach, I found that creating such simple
example often helped me discover my own mistakes, so I didn't need to
ask for help at all because I figured out the issue myself.



Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

nrjank
In reply to this post by nrjank
using Octave 5.1.0 on Windows 10 Pro, with MS Office 2013 and Java(TM)
SE Runtime Environment (build 1.8.0_212-b10)
(I don't have a pc without MS Office for testing.)

my simple test case:

>> pkg load windows;
>> pkg load io;
>> arrayNA = NA(5)
arrayNA =
    NA    NA    NA    NA    NA
    NA    NA    NA    NA    NA
    NA    NA    NA    NA    NA
    NA    NA    NA    NA    NA
    NA    NA    NA    NA    NA
>> arrayNAN = NaN(5)
arrayNAN =
   NaN   NaN   NaN   NaN   NaN
   NaN   NaN   NaN   NaN   NaN
   NaN   NaN   NaN   NaN   NaN
   NaN   NaN   NaN   NaN   NaN
   NaN   NaN   NaN   NaN   NaN
>> status = xlswrite('testna.xlsx',arrayNA)
status =  1
>> status = xlswrite('testnan.xlsx',arrayNAN)
status =  1
>> status = odswrite('testna.ods',arrayNA)
status =  1
>> status = odswrite('testnan.ods',arrayNAN)
status =  1

Looking at the files,  both of the xlsx files show:

65535 65535 65535 65535 65535
65535 65535 65535 65535 65535
65535 65535 65535 65535 65535
65535 65535 65535 65535 65535
65535 65535 65535 65535 65535

both of the .ods files show:

0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0


Note that Excel has a built in NA(), but not NAN(). I don't know if it
can represent NaN and just doesn't have it as a built in function or
anyway I can quickly see to input one.

unless this in a known event, it seems at least the mismatch between
output filetypes is a concern. for compatibly concerns, Matlab 2018b
has no representation of NA, but for NaN produces an empty
spreadsheet.


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

nrjank
On Mon, May 20, 2019 at 11:52 AM Nicholas Jankowski <[hidden email]> wrote:
>
> using Octave 5.1.0 on Windows 10 Pro, with MS Office 2013 and Java(TM)
> SE Runtime Environment (build 1.8.0_212-b10)
> (I don't have a pc without MS Office for testing.)
>
> my simple test case:
>

realized I still have v4.4.1 sitting around.  running the same tests,
the xlsx files are both blank for NA and NAN and the ods files are
both filled with zeros.

so definitely a change between versions. I don't know what the desired behavior
is. previous behavior (blank) matches Matlab for compatibility.  no
compatibility issue with ODS output due to Matlab not apparently
handling that format natively, but it seems odd that the two wouldn't
match.


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

PhilipNienhuis
nrjank wrote
> On Mon, May 20, 2019 at 11:52 AM Nicholas Jankowski &lt;

> jankowskin@

> &gt; wrote:
>>
>> using Octave 5.1.0 on Windows 10 Pro, with MS Office 2013 and Java(TM)
>> SE Runtime Environment (build 1.8.0_212-b10)
>> (I don't have a pc without MS Office for testing.)
>>
>> my simple test case:
>>
>
> realized I still have v4.4.1 sitting around.  running the same tests,
> the xlsx files are both blank for NA and NAN and the ods files are
> both filled with zeros.
>
> so definitely a change between versions. I don't know what the desired
> behavior
> is. previous behavior (blank) matches Matlab for compatibility.  no
> compatibility issue with ODS output due to Matlab not apparently
> handling that format natively, but it seems odd that the two wouldn't
> match.

With a self-crossbuilt Octave-6.0.0, I get (as to be expected) completely
empty spreadsheets for below commands:

>> xlswrite ("NaN.xlsx", NaN (3, 3), "NaN", "B2:G8", "oct")
Checking requested interface(s):
OCT*; (* = default interface)
ans =  1
>> xlswrite ("NaN.xlsx", NA (3, 3), "NaN", "B2:G8", "oct")
>> NA(3, 3)  ##  Just to see if that works as well
ans =
    NA    NA    NA
    NA    NA    NA
    NA    NA    NA
## So it does work

>> xlswrite ("NA.xlsx", NA (3, 3), "Na", "B2:G8", "oct")
ans =  1
>>

Same if I use COM instead of OCT interface (the issue was with default COM
if windows pkg is loaded, right?)

I have no Octave-5.1.0 installed (and no time to install it), but AFAICS
Octave-6.0.0 is doing the right thing.

Philip




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


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

Octave - General mailing list
hi,
but i don't understand how i solve it  

in octave 4.XX i don't see this problem..

I see problem in octave 5.1..

(octave 6.0 it's not out yet)





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


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

nrjank
On Mon, May 20, 2019 at 4:31 PM shivax via Help-octave <[hidden email]> wrote:
hi,
but i don't understand how i solve it 

in octave 4.XX i don't see this problem..

I see problem in octave 5.1..

(octave 6.0 it's not out yet)


shivax, the convention in this mailing list is to post under the previous replies, trimming unnecessary next.

Now, for reasons I cannot explain, I just ran a simple test again on the same machine (in v5.1.0)  and the NA test is producing blank cells.  

I was about to create a bug report with an easily reproducible test, but I'm having difficulty recreating the problem now.  I don't understand the particular COM interface, etc., how java and/or MS Office do/don't come into play on a windows machine, and how that does or doesn't change the problem. maybe someone else has some clues.


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

PhilipNienhuis
In reply to this post by Octave - General mailing list
help-octave-3 wrote
> hi,
> but i don't understand how i solve it  
>
> in octave 4.XX i don't see this problem..
>
> I see problem in octave 5.1..
>
> (octave 6.0 it's not out yet)

Luckily (or not) for you, I tried with Octave-5.1.0 and indeed, with the COM
interface (= invoking Excel behind the scenes) I get numerical "65536"
entries in place of NaN and NA "values".
With a "5.1.1" build (which after more fixes will become 5.2.0) I do NOT see
this bug.

I have no explanation for this, it's beyond me.
For me it is reassuring that the bug disappeared in later releases :-)

A workaround for you wold be to UNload the windows package. In that case
Excel won't be invoked and Octave will use native code and that doesn't have
the bug.

BTW, it never occurred to me to test for this NA/NaN behavior. I'll adapt
the spreadsheet test scripts in the io package to check for this.

Philip




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


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

nrjank

A workaround for you wold be to UNload the windows package. In that case
Excel won't be invoked and Octave will use native code and that doesn't have
the bug.

BTW, it never occurred to me to test for this NA/NaN behavior. I'll adapt
the spreadsheet test scripts in the io package to check for this.


thanks Philip, I'm now wondering if the windows pkg loading was the difference with seeing/not seeing the bug yesterday.  would that make this a windows package related bug needing appropriate tests? I'm not sure how one would write tests capturing cross-package behavior.


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

PhilipNienhuis
Nicholas Jankowski wrote:

>
>     A workaround for you wold be to UNload the windows package. In that case
>     Excel won't be invoked and Octave will use native code and that
>     doesn't have
>     the bug.
>
>     BTW, it never occurred to me to test for this NA/NaN behavior. I'll
>     adapt
>     the spreadsheet test scripts in the io package to check for this.
>
>
> thanks Philip, I'm now wondering if the windows pkg loading was the
> difference with seeing/not seeing the bug yesterday.  would that make
> this a windows package related bug needing appropriate tests? I'm not
> sure how one would write tests capturing cross-package behavior.

Yes the windows pkg is an essential part of the culprit. It provides
ActiveX that allows Excel to be invoked through the COM server on Windows.
Whatever it was, it seems the problem has solved itself.

Philip


Reply | Threaded
Open this post in threaded view
|

Re: Nan and write on excel "65535"

nrjank
ok. I'll log a bug report just to capture the info for 5.1.0, even though it will likely get a 'wontfix' since it's apparently corrected itself in future versions.

On Wed, May 22, 2019 at 1:10 PM Philip Nienhuis <[hidden email]> wrote:
Nicholas Jankowski wrote:
>
>     A workaround for you wold be to UNload the windows package. In that case
>     Excel won't be invoked and Octave will use native code and that
>     doesn't have
>     the bug.
>
>     BTW, it never occurred to me to test for this NA/NaN behavior. I'll
>     adapt
>     the spreadsheet test scripts in the io package to check for this.
>
>
> thanks Philip, I'm now wondering if the windows pkg loading was the
> difference with seeing/not seeing the bug yesterday.  would that make
> this a windows package related bug needing appropriate tests? I'm not
> sure how one would write tests capturing cross-package behavior.

Yes the windows pkg is an essential part of the culprit. It provides
ActiveX that allows Excel to be invoked through the COM server on Windows.
Whatever it was, it seems the problem has solved itself.

Philip