As Karl-Heinz pointed out, that is the internal Excel date format.
It's a floating point number representing the number of fractional
days since the beginning of the 20th century. The orginal authors of
Excel were unaware that 1900 wasn't a leap year, so the dates are out
by one day, starting at 00:00 on Dec 31 1899. Excel uses local time,
not UTC. So to convert this to a java time do the following:
- adjust for the different start day Jan 1, 1970 is excel date 25,569
- multiply by the number of milliseconds in a day 8,6400,000
- convert to a Date using java.util.date.Date(long date)
- use the Calendar class to adjust timezone
- use java.text.SimpleDateFormat to convert it to text.
/peter
On 7/15/05, Ric Dargusch <[EMAIL PROTECTED]> wrote:
> Hi Jaypee-
>
> I've played with this Excel stuff a bit...
> I am enclosing a method that will convert the "internal
> representation (e.g. 38472) to the date format you want
> (e.g. 5/1/2005).
>
> Please send me a note and let me know if this works for you
> -Ric
>
> ([EMAIL PROTECTED])
>
>
> ==============================
> public static final int[] days_b4_month = { // Don't forget to check for
> leap year!
> 0,
> 31,
> 31+28,
> 31+28+31,
> 31+28+31+30,
> 31+28+31+30+31,
> 31+28+31+30+31+30,
> 31+28+31+30+31+30+31,
> 31+28+31+30+31+30+31+31,
> 31+28+31+30+31+30+31+31+30,
> 31+28+31+30+31+30+31+31+30+31,
> 31+28+31+30+31+30+31+31+30+31+30,
> 31+28+31+30+31+30+31+31+30+31+30+31
> };
>
> public static String convert(double excelDate)
> {
> boolean leap_day = false;
> int month = 0;
> int day = 0;
> int date = (int) excelDate;
> int yr = (int) (excelDate / 365.25);
> int year = yr + 1900;
> int yrDay = (int) excelDate - (yr * 365 + (int) (yr / 4));
> if ((yr % 4) == 0) // Leap years. (N.B. this algorithm will be
> inaccurate after 2099!)
> {
> if (yrDay == 59) // It's Feb 29! - Special case
> leap_day = true;
> else if (yrDay < days_b4_month[2])
> yrDay++; // Compensate for leap day.
> }
> if (leap_day)
> {
> month = 2;
> day = 29;
> }
> else
> {
> month = 0; // start in January.
> while (days_b4_month[month] < yrDay)
> month++;
> day = yrDay - days_b4_month[month-1];
> }
> String ret = new String(month + "/" + day + "/" + year);
> return ret;
> }
>
>
>
> -----Original Message-----
> From: KHZ (SAW) [mailto:[EMAIL PROTECTED]
> Sent: Friday, July 15, 2005 4:17 AM
> To: 'POI Users List'
> Subject: AW: How to read data as it appears in the excel sheet.........
>
> Hi Jaypee.
>
> The internal representation is 38473. This is the sequence number of the
> day starting at 1.1.1900 (+/-1). Look at threads having "date" in the
> subject. There such things are intensively discussed.
>
> So you want to achieve the same external representation and thus need
> data formatting which is "normal" Java. So look at the J2SE
> documentation of SUN - most probably in the classes java.util.Date or
> String or similar classes.
>
> The setting and getting data formats of a cell in POI is just used for
> forcing Excel to show the data later as wanted.
>
> Regards, Karl-Heinz.
>
>
> -----Ursprüngliche Nachricht-----
> Von: news [mailto:[EMAIL PROTECTED] Im Auftrag von jaypee
> Gesendet: Freitag, 15. Juli 2005 09:20
> An: [email protected]
> Betreff: How to read data as it appears in the excel sheet.........
>
> Hi Everyone,
> I am trying to read(just read) the data from an excel sheet column.
> The problem is, that particular column is formatted as "d/m/yy;@".
> When i
> read a cell in that column, it returns the alias value(eg., 38473) but
> not the
> actual value(eg., 5/1/05).
>
> How can i read the actual value(ie., 5/1/05) which is appears in
> that cell?
> I am using Version : POI api 2.5.1.
>
> Thanks in advance,
> Jaypee
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
