You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Ilya Kasnacheev <il...@office.uw.ru> on 2007/07/13 14:50:22 UTC
HSSF: Retrieving numeric cell contents in their actual format
I'm using POI HSSF, and I need to dump Excel file into XML.
I've wrote most of it, but still have one problem unresolved:
I'm looking for a way to get numeric cells' content as a string, formatted
exactly as they would be displayed by Excel itself.
I mean, date 38629 in "DD.MM.YYYY" should yield "04.10.2005", and 2.25 in
"# ?/?" should yield "2 1/4".
I haven't found a way to do that, and googling redirects me into
HSSFDataFormat which is unrelated, I fail to form a relevant query.
Is there a way do get that string representation? Is it already stored in
Excel files with possibility to retrieve? Or maybe there's a formatter
somewhere which I've overlooked? A converter from HSSF format to Java's
NumberFormat and DateFormat (even if not universal)?
Maybe any third-party solutions? I guess this problem should be pretty
popular.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: HSSF: Retrieving numeric cell contents in their actual format
Posted by Dónal Doyle <Do...@idiro.com>.
Have never tried this. But my guess is that the data is stored as a number
in the cell in excel and some formatter is applied in Excel. Maybe if you
can check the format of the cell and apply your own similar formatting in
Java.
Hope this helps.
Dónal
Donal Doyle
Idiro Technologies
Synergy Centre, ITT Dublin,
Tallaght, Dublin 24, Ireland
T +353.1.657.2956
M+353.86.876.1952
F +353.1.443.0582
E: donal.doyle@idiro.com
-----Original Message-----
From: Ilya Kasnacheev [mailto:ilyak@office.uw.ru]
Sent: 13 July 2007 13:50
To: user@poi.apache.org
Subject: HSSF: Retrieving numeric cell contents in their actual format
I'm using POI HSSF, and I need to dump Excel file into XML.
I've wrote most of it, but still have one problem unresolved:
I'm looking for a way to get numeric cells' content as a string, formatted
exactly as they would be displayed by Excel itself.
I mean, date 38629 in "DD.MM.YYYY" should yield "04.10.2005", and 2.25 in
"# ?/?" should yield "2 1/4".
I haven't found a way to do that, and googling redirects me into
HSSFDataFormat which is unrelated, I fail to form a relevant query.
Is there a way do get that string representation? Is it already stored in
Excel files with possibility to retrieve? Or maybe there's a formatter
somewhere which I've overlooked? A converter from HSSF format to Java's
NumberFormat and DateFormat (even if not universal)?
Maybe any third-party solutions? I guess this problem should be pretty
popular.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.476 / Virus Database: 269.10.4/898 - Release Date: 12/07/2007
16:08
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSF: Retrieving numeric cell contents in their actual format
Posted by Igor Androsov <ia...@yahoo.com>.
Here are 3 methods you may need to impliment date
conversion. These are not my functions but part of one
of POI examples I got it long ago from one of POI
forums or sample cases. Do not recall where but here
it is:
/**
* Given a double, checks if it is a valid
Excel date.
*
* @return true if valid
* @param value the double value
*/
public static boolean isValidExcelDate(double
value)
{
return (value > -Double.MIN_VALUE);
}
///////////////////////////////////////////////////////////////
// Method returns Java date pattern mapped from
Excel date
public static String getCellDateFormat(HSSFCell
cell)
{
String dt = "dd-MMM-yy";
HSSFCellStyle style = cell.getCellStyle();
int i = style.getDataFormat();
switch(i)
{
// Internal Date Formats as described on page 427
in Microsoft Excel Dev's Kit...
case 0x0e: //m/d/yyyy
dt = "MM/dd/yyyy";
break;
case 0x0f: //d-mmm
dt = "dd-MMM";
break;
case 0x10: //d-mmm-yy
dt = "dd-MMM-yy";
break;
case 0x11: //mmm-yy
dt = "MMMM-yy";
break;
case 0x12: //h:mmAM/PM
dt = "hh:mm aa";
break;
case 0x13: //h:mm:ssAM/PM
dt = "hh:mm:ss aa";
break;
case 0x14: //h:mm
dt = "hh:mm";
break;
case 0x15: //h:mm:ss
dt = "hh:mm:ss";
break;
case 0x16: //m/d/yyyy h:mm
dt = "MM/dd/yyyy hh:mm";
break;
case 0x2d: //mm:ss
dt = "mm:ss";
break;
case 0x2e: //[h]:mm:ss
dt = "hh:mm:ss";
break;
case 0x2f: //mm:ss.0
dt = "mm:ss.SSSS";
break;
default:
dt = "dd-MMM-yy";
break;
}
return dt;
}
//////////////////////////////////////////////////////////////////
// method to determine if the cell is a date, versus
a number...
public static boolean isCellDateFormatted(HSSFCell
cell)
{
boolean bDate = false;
double d = cell.getNumericCellValue();
if ( isValidExcelDate(d) ) {
HSSFCellStyle style = cell.getCellStyle();
int i = style.getDataFormat();
switch(i) {
// Internal Date Formats as described on page 427
in Microsoft Excel Dev's Kit...
case 0x0e:
case 0x0f:
case 0x10:
case 0x11:
case 0x12:
case 0x13:
case 0x14:
case 0x15:
case 0x16:
case 0x2d:
case 0x2e:
case 0x2f:
bDate = true;
break;
default:
bDate = false;
break;
}
}
return bDate;
}
Igor
--- Ilya Kasnacheev <il...@office.uw.ru> wrote:
> ÷ ÓÏÏÂÝÅÎÉÉ ÏÔ 14 éÀÌØ 2007 05:49 Igor Androsov
> ÎÁÐÉÓÁÌ(a):
> > You can use HSSFCel object to get the data type,
> if
> > its string string you done. If its other types
> numeric
> > it may be date number etc you can formatit.
> > I used this code to deal with dates numerics:
> Thanx! I have similar code in place, but still have
> some mysteries:
>
> > {
> > case HSSFCell.CELL_TYPE_STRING:
> > cl = cell.getStringCellValue();
> > break; case
> > HSSFCell.CELL_TYPE_NUMERIC: icl =
> > cell.getNumericCellValue();
> > if (isCellDateFormatted(cell))
> {
> I've tried this function, it failed to recongise
> date formats from my
> ooo-saved excel document with
>
> So I've falled to home-grown
> private static boolean isDateFormat(HSSFCell cell,
> HSSFDataFormat format)
> {
> short fmt = cell.getCellStyle().getDataFormat();
> String df = format.getFormat(fmt);
> if(df.indexOf('D') >= 0
> || df.indexOf('M') >= 0
> || df.indexOf('Y') >= 0
> || df.indexOf('H') >= 0
> || df.indexOf('S') >= 0)
> return true;
> return false;
> }
>
> It kinda works, but I guess I might have missed
> something.
>
> > // format in form of
> > M/D/YY
> > Calendar cal =
> > Calendar.getInstance();
> >
> > cal.setTime(getJavaDate(icl,false));
> > String pattern
> =
> > getCellDateFormat(cell);
> I can't find this function in library, and syntax
> suggests it's your locally
> defined method.
>
> Can you please send its contents to me or to this
> list, I guess it's really
> what I'm looking for.
>
> Anyway, thanx for support!
>
>
---------------------------------------------------------------------
> To unsubscribe, e-mail:
> user-unsubscribe@poi.apache.org
> For additional commands, e-mail:
> user-help@poi.apache.org
>
>
____________________________________________________________________________________
We won't tell. Get more on shows you hate to love
(and love to hate): Yahoo! TV's Guilty Pleasures list.
http://tv.yahoo.com/collections/265
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSF: Retrieving numeric cell contents in their actual format
Posted by Ilya Kasnacheev <il...@office.uw.ru>.
В сообщении от 14 Июль 2007 05:49 Igor Androsov написал(a):
> You can use HSSFCel object to get the data type, if
> its string string you done. If its other types numeric
> it may be date number etc you can formatit.
> I used this code to deal with dates numerics:
Thanx! I have similar code in place, but still have some mysteries:
> {
> case HSSFCell.CELL_TYPE_STRING:
> cl = cell.getStringCellValue();
> break; case
> HSSFCell.CELL_TYPE_NUMERIC: icl =
> cell.getNumericCellValue();
> if (isCellDateFormatted(cell)) {
I've tried this function, it failed to recongise date formats from my
ooo-saved excel document with
So I've falled to home-grown
private static boolean isDateFormat(HSSFCell cell, HSSFDataFormat format)
{
short fmt = cell.getCellStyle().getDataFormat();
String df = format.getFormat(fmt);
if(df.indexOf('D') >= 0
|| df.indexOf('M') >= 0
|| df.indexOf('Y') >= 0
|| df.indexOf('H') >= 0
|| df.indexOf('S') >= 0)
return true;
return false;
}
It kinda works, but I guess I might have missed something.
> // format in form of
> M/D/YY
> Calendar cal =
> Calendar.getInstance();
>
> cal.setTime(getJavaDate(icl,false));
> String pattern =
> getCellDateFormat(cell);
I can't find this function in library, and syntax suggests it's your locally
defined method.
Can you please send its contents to me or to this list, I guess it's really
what I'm looking for.
Anyway, thanx for support!
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSF: Retrieving numeric cell contents in their actual format
Posted by Nick Burch <ni...@torchbox.com>.
On Fri, 13 Jul 2007, Igor Androsov wrote:
> From previous post here is function that check for
> date format:
> // method to determine if the cell is a date, versus
> a number...
> public static boolean isCellDateFormatted(HSSFCell
Or you could just call
org.apache.poi.hssf.usermodel.HSSFDateUtil.isADateFormat
That ought to cope with the cases where the formatting is for a date, but
isn't one of the standard excel 97 date formatting strings
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSF: Retrieving numeric cell contents in their actual format
Posted by Igor Androsov <ia...@yahoo.com>.
>From previous post here is function that check for
date format:
//////////////////////////////////////////////////////////////////
// method to determine if the cell is a date, versus
a number...
public static boolean isCellDateFormatted(HSSFCell
cell)
{
boolean bDate = false;
double d = cell.getNumericCellValue();
if ( isValidExcelDate(d) ) {
HSSFCellStyle style = cell.getCellStyle();
int i = style.getDataFormat();
switch(i) {
// Internal Date Formats as described on page 427
in Microsoft Excel Dev's Kit...
case 0x0e:
case 0x0f:
case 0x10:
case 0x11:
case 0x12:
case 0x13:
case 0x14:
case 0x15:
case 0x16:
case 0x2d:
case 0x2e:
case 0x2f:
bDate = true;
break;
default:
bDate = false;
break;
}
}
return bDate;
}
--- Igor Androsov <ia...@yahoo.com> wrote:
> You can use HSSFCel object to get the data type, if
> its string string you done. If its other types
> numeric
> it may be date number etc you can formatit.
>
> I used this code to deal with dates numerics:
>
> {
> case HSSFCell.CELL_TYPE_STRING:
> cl = cell.getStringCellValue();
> break; case
> HSSFCell.CELL_TYPE_NUMERIC: icl =
> cell.getNumericCellValue();
> if (isCellDateFormatted(cell))
> {
> // format in form of
> M/D/YY
> Calendar cal =
> Calendar.getInstance();
>
> cal.setTime(getJavaDate(icl,false));
> String pattern =
> getCellDateFormat(cell);
> SimpleDateFormat
> df = new SimpleDateFormat(pattern);
> String dateStr =
> df.format(cal.getTime());
>
> }
> else
> String dateStr = Double.toString(icl));
> break;
> --- Ilya Kasnacheev <il...@office.uw.ru> wrote:
>
> > I'm using POI HSSF, and I need to dump Excel file
> > into XML.
> >
> > I've wrote most of it, but still have one problem
> > unresolved:
> > I'm looking for a way to get numeric cells'
> content
> > as a string, formatted
> > exactly as they would be displayed by Excel
> itself.
> > I mean, date 38629 in "DD.MM.YYYY" should yield
> > "04.10.2005", and 2.25 in
> > "# ?/?" should yield "2 1/4".
> >
> > I haven't found a way to do that, and googling
> > redirects me into
> > HSSFDataFormat which is unrelated, I fail to form
> a
> > relevant query.
> >
> > Is there a way do get that string representation?
> Is
> > it already stored in
> > Excel files with possibility to retrieve? Or maybe
> > there's a formatter
> > somewhere which I've overlooked? A converter from
> > HSSF format to Java's
> > NumberFormat and DateFormat (even if not
> universal)?
> >
> > Maybe any third-party solutions? I guess this
> > problem should be pretty
> > popular.
> >
> >
> >
>
---------------------------------------------------------------------
> > To unsubscribe, e-mail:
> > user-unsubscribe@poi.apache.org
> > For additional commands, e-mail:
> > user-help@poi.apache.org
> >
> >
>
>
>
>
>
____________________________________________________________________________________
> Looking for a deal? Find great prices on flights and
> hotels with Yahoo! FareChase.
> http://farechase.yahoo.com/
>
>
---------------------------------------------------------------------
> To unsubscribe, e-mail:
> user-unsubscribe@poi.apache.org
> For additional commands, e-mail:
> user-help@poi.apache.org
>
>
____________________________________________________________________________________
Yahoo! oneSearch: Finally, mobile search
that gives answers, not web links.
http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: HSSF: Retrieving numeric cell contents in their actual format
Posted by Igor Androsov <ia...@yahoo.com>.
You can use HSSFCel object to get the data type, if
its string string you done. If its other types numeric
it may be date number etc you can formatit.
I used this code to deal with dates numerics:
{
case HSSFCell.CELL_TYPE_STRING:
cl = cell.getStringCellValue();
break; case
HSSFCell.CELL_TYPE_NUMERIC: icl =
cell.getNumericCellValue();
if (isCellDateFormatted(cell)) {
// format in form of
M/D/YY
Calendar cal =
Calendar.getInstance();
cal.setTime(getJavaDate(icl,false));
String pattern =
getCellDateFormat(cell);
SimpleDateFormat
df = new SimpleDateFormat(pattern);
String dateStr =
df.format(cal.getTime());
}
else
String dateStr = Double.toString(icl));
break;
--- Ilya Kasnacheev <il...@office.uw.ru> wrote:
> I'm using POI HSSF, and I need to dump Excel file
> into XML.
>
> I've wrote most of it, but still have one problem
> unresolved:
> I'm looking for a way to get numeric cells' content
> as a string, formatted
> exactly as they would be displayed by Excel itself.
> I mean, date 38629 in "DD.MM.YYYY" should yield
> "04.10.2005", and 2.25 in
> "# ?/?" should yield "2 1/4".
>
> I haven't found a way to do that, and googling
> redirects me into
> HSSFDataFormat which is unrelated, I fail to form a
> relevant query.
>
> Is there a way do get that string representation? Is
> it already stored in
> Excel files with possibility to retrieve? Or maybe
> there's a formatter
> somewhere which I've overlooked? A converter from
> HSSF format to Java's
> NumberFormat and DateFormat (even if not universal)?
>
> Maybe any third-party solutions? I guess this
> problem should be pretty
> popular.
>
>
>
---------------------------------------------------------------------
> To unsubscribe, e-mail:
> user-unsubscribe@poi.apache.org
> For additional commands, e-mail:
> user-help@poi.apache.org
>
>
____________________________________________________________________________________
Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.
http://farechase.yahoo.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org