problem with date values in cells - POI thinks these are numeric cells

[Ravi Sundaar <rs...@hotmail.com>]

I have spreadsheets that contains values like "5/10/2008". When I inspect the cell, the type of the cell that contains these kind of date values (using getCellType), the type is HSSFCell.CELL_TYPE_NUMERIC and not HSSFCell.CELL_TYPE_STRING.
 
I am able to retrieve the value only using getNumericCellValue() and not getRichStringCellValue() as for strings. The value I retrieve looks something like 39581.0 because of this.
 
The interesting quirk is that, if the value is "15/10/2008" everything works ok. The API recognizes the cell as a string and retrieves appropriately.
 
Is there a workaround for this problem?
 
Thanks,
Ravi.
 
 
 
 
 
_________________________________________________________________
Got Game? Win Prizes in the Windows Live Hotmail Mobile Summer Games Trivia Contest
http://www.gowindowslive.com/summergames?ocid=TXT_TAGHM

Re: problem with date values in cells - POI thinks these are numeric cells

[Nick Burch <ni...@torchbox.com>]

On Tue, 5 Aug 2008, Ravi Sundaar wrote:
> I have spreadsheets that contains values like "5/10/2008". When I 
> inspect the cell, the type of the cell that contains these kind of date 
> values (using getCellType), the type is HSSFCell.CELL_TYPE_NUMERIC

That's normal. That's how excel stores dates.

These two methods should help:
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDateUtil.html#isCellDateFormatted(org.apache.poi.hssf.usermodel.HSSFCell)
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html#getDateCellValue()

> The interesting quirk is that, if the value is "15/10/2008" everything 
> works ok.

Your excel is probably set to US mode, so it thinks that 15/10/2008 is the 
10th day of the 15th month, gives in and treats it as a string. You'll 
need to tell excel you're based outside of the US, so it expects 
dd/mm/yyyy (I forget exactly what the trick to do this is though)

Nick

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

RE: problem with date values in cells - POI thinks these are numeric cells

["May, James" <Ja...@FMR.com>]

Dates are numeric cells as far as excel and POI are concerned. Good,
bad, or ugly... that's just how excel stores date values. To get the
formatted string value of a date, or other numeric cell, use the
formatCellValue(HSSFCell) method on an instance of HSSFDataFormatter.
See
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDataForm
atter.html

This is a new class that is in svn trunk right now.

James

-----Original Message-----
From: Ravi Sundaar [mailto:rsundaar@hotmail.com] 
Sent: Tuesday, August 05, 2008 11:11 AM
To: user@poi.apache.org
Subject: problem with date values in cells - POI thinks these are
numeric cells

I have spreadsheets that contains values like "5/10/2008". When I
inspect the cell, the type of the cell that contains these kind of date
values (using getCellType), the type is HSSFCell.CELL_TYPE_NUMERIC and
not HSSFCell.CELL_TYPE_STRING.
 
I am able to retrieve the value only using getNumericCellValue() and not
getRichStringCellValue() as for strings. The value I retrieve looks
something like 39581.0 because of this.
 
The interesting quirk is that, if the value is "15/10/2008" everything
works ok. The API recognizes the cell as a string and retrieves
appropriately.
 
Is there a workaround for this problem?
 
Thanks,
Ravi.
 
 
 
 
 
_________________________________________________________________
Got Game? Win Prizes in the Windows Live Hotmail Mobile Summer Games
Trivia Contest
http://www.gowindowslive.com/summergames?ocid=TXT_TAGHM


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org