Re: installing java and apache for using spreadsheet I/O

[Philip Nienhuis]

Markus Bergholz wrote
> On Sat, Jul 12, 2014 at 5:40 AM, rcharan51 <

> address@hidden

> > wrote:
> 
>> 1.) as you can see from the image mix(75,4) contains the values present
>> in
>> D75 cell(02-01-3815) of input.xlsx  but mix(75,4) has
>>
>> >> mix(75,4)
>> ans =
>> {
>>   [1,1] =  699442
>> }
>> I think this is a error related to xlsread.
>>
> 
> address@hidden:~/tmp/xl$ grep -r "02-01-3815" *
> address@hidden:~/tmp/xl$
> 
> 
> There is no "02-01-3815" reference in input.xlsx
> 
> And in sheet1.xml cell D75 (75,4) the value is 699442
> <c r="D75" s="15">
> <v>
> 699442
> </v>
> </c>
> Afaiu, this is not an error (Matlab read excel dates (date-strings) as
> serial numbers too!).
> 
>>> [a,b,c]=xlsread('~/Downloads/input.xlsx');
>>> c(75,4)
> 
> ans =
> 
>     [699442]
> 
> So you have to convert your dates by yourself. Note: Excel starts counting
> dates at 01-Jan-1900 .. or something similar lol

... and Excel thinks 1900 is a leap year, a corner case to watch out for.


> octave:3> datestr (699442)
> ans = 03-Jan-1915
> 
> octave:15> datenum ('01-01-1900','dd-mm-yyyy')
> ans =  693962
> octave:16> datestr(699442 + 693962,'dd-mm-yyyy')
> ans = 04-01-3815
> octave:17> datestr(699442 + 693961,'dd-mm-yyyy')
> ans = 03-01-3815
> octave:18> datestr(699442 + 693960,'dd-mm-yyyy')
> ans = 02-01-3815

693960 is the conversion term, indeed.

Note that also when writing Octave/Matlab datenums to Excel, you'd need to
convert them beforehand (i.e., subtract 693960), otherwise Excel (and many
other spreadsheet I/O SW) gets confused, as you can see above.


> so xlsread is fine.

Yep.
Note that Octave's xlsread returns Octave datenums (epoch 0-Jan-0000) for
date/time cells. It doesn't always work, because the cells in question need
to be formatted as date / date-time cells. Date values in cells formatted as
numbers remain numbers and need to be corrected (add 693960) to get Octave
datenum values.

Philip




--
View this message in context: 
http://octave.1599824.n4.nabble.com/Re-installing-java-and-apache-for-using-spreadsheet-I-O-tp4664767p4665447.html
Sent from the Octave - General mailing list archive at Nabble.com.