[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: installing java and apache for using spreadsheet I/O
From: |
Philip Nienhuis |
Subject: |
Re: installing java and apache for using spreadsheet I/O |
Date: |
Sat, 12 Jul 2014 09:23:38 -0700 (PDT) |
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.