You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by jaypee <mj...@rediffmail.com> on 2005/07/15 09:20:06 UTC
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: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
Re: How to read data as it appears in the excel sheet.........
Posted by Peter Wooster <pk...@gmail.com>.
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 <rd...@draper.com> 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
>
> (rdargusch@draper.com)
>
>
> ==============================
> 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:karl-heinz.zengerle@sawag.com]
> 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:news@sea.gmane.org] Im Auftrag von jaypee
> Gesendet: Freitag, 15. Juli 2005 09:20
> An: poi-user@jakarta.apache.org
> 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: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
> Mailing List: http://jakarta.apache.org/site/mail2.html#poi
> The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
RE: How to read data as it appears in the excel sheet.........
Posted by Ric Dargusch <rd...@draper.com>.
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
(rdargusch@draper.com)
==============================
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:karl-heinz.zengerle@sawag.com]
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:news@sea.gmane.org] Im Auftrag von jaypee
Gesendet: Freitag, 15. Juli 2005 09:20
An: poi-user@jakarta.apache.org
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: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
AW: How to read data as it appears in the excel sheet.........
Posted by "KHZ (SAW)" <ka...@sawag.com>.
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:news@sea.gmane.org] Im Auftrag von jaypee
Gesendet: Freitag, 15. Juli 2005 09:20
An: poi-user@jakarta.apache.org
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: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
Mailing List: http://jakarta.apache.org/site/mail2.html#poi
The Apache Jakarta Poi Project: http://jakarta.apache.org/poi/