Validating the excel date for MM/dd/yyyy format

[Renjith R <ra...@gmail.com>]

Hi,

I am using Apache POI to read
Excel<http://www.aspose.com/categories/java-components/aspose.cells-for-java/default.aspx>
file(.xlsx)
and validate its contents.
We are having a date field in excel whcih should be in format MM/dd/yyyy.

Suppose if the date fild has two values as follows,

1) 04/24/2014
2) 04242014

I have to accept the first one and reject the second.
Since excel stores both these values in integer format, I am not able to
differentiate them.
Please suggest if you have any idea about how to solve this.

-- 
*RENJITH R*
9446011990

RE: Validating the excel date for MM/dd/yyyy format

["Crocker, David" <Da...@nrel.gov>]

Plus, don't forget the really useful utility, BuiltinFormats

http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html


-----Original Message-----
From: Nick Burch [mailto:apache@gagravarr.org] 
Sent: Friday, April 25, 2014 3:10 AM
To: POI Users List
Subject: Re: Validating the excel date for MM/dd/yyyy format

On Fri, 25 Apr 2014, Renjith R wrote:
> I have to accept the first one and reject the second.
> Since excel stores both these values in integer format, I am not able 
> to differentiate them.

Simple - fetch the cell style that applies to the cell, get the formatting string that it uses, and see if that's one of the date formats you like

Nick

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


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

Re: Validating the excel date for MM/dd/yyyy format

[Nick Burch <ap...@gagravarr.org>]

On Fri, 25 Apr 2014, Renjith R wrote:
> I have to accept the first one and reject the second.
> Since excel stores both these values in integer format, I am not able to
> differentiate them.

Simple - fetch the cell style that applies to the cell, get the formatting 
string that it uses, and see if that's one of the date formats you like

Nick

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

Re: Validating the excel date for MM/dd/yyyy format

[Renjith R <ra...@gmail.com>]

Since I am working at XSSF + SAX level, I am able to get the format of that
particular cell by accessing the Styles table using the following code.

XSSFCellStyle style = styles.getStyleAt(styleIndex);
String formatString = style.getDataFormatString();
System.out.println(formatString);

This gave me 'm/d/yy' for both values, which comes under BuiltinFormats.
But I strongly believe there should be some way excel differentiate both
these value. Do you think that i need to include something else to get the
correct format of 'MM/dd/yyyy' for one and number for the other.

Thanks in advance.


On Fri, Apr 25, 2014 at 12:31 PM, Renjith R <ra...@gmail.com> wrote:

> Hi,
>
> I am using Apache POI to read Excel<http://www.aspose.com/categories/java-components/aspose.cells-for-java/default.aspx> file(.xlsx)
> and validate its contents.
> We are having a date field in excel whcih should be in format MM/dd/yyyy.
>
> Suppose if the date fild has two values as follows,
>
> 1) 04/24/2014
> 2) 04242014
>
> I have to accept the first one and reject the second.
> Since excel stores both these values in integer format, I am not able to
> differentiate them.
> Please suggest if you have any idea about how to solve this.
>
> --
> *RENJITH R*
> 9446011990
>



-- 
*RENJITH R*
9446011990