You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Erick Lichtas <el...@linoma.com> on 2009/12/09 17:40:22 UTC
Date formatted cell with fraction of seconds
Hi all,
I am reading data from a spreadsheet using code similar to that below.
switch (cell.getCellType()) {
...
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
// handle date type
...
}
// handle numeric type
...
}
I am running into an issue when the cell format is a custom pattern like
'yyyy-mm-dd hh:mm:ss.0'. Notice the 0 at the end of the pattern, used to
display the 10th of a second. For this cell, the
DateUtil.isCellDateFormatted(cell) is returning false and the value I am
getting is a number.
Upon inspection of the isCellDateFormatted() method, the lines
// Otherwise, check it's only made up, in any case, of:
// y m d h s - / , . :
// optionally followed by AM/PM
if(fs.matches("^[yYmMdDhHsS\\-/,. :]+[ampAMP/]*$")) {
return true;
}
do not take in to consideration the zeros that could be present in a date
pattern. According to Microsoft Office Support, "If you want to display
fractions of a second, use a number format similar to h:mm:ss.00.", found at
http://support.microsoft.com/kb/264372
Is there a workaround for determining that a cell formatted like 'yyyy-mm-dd
hh:mm:ss.0' is a Date cell? Or would I have to modify this regex pattern in
the DateUtil class in order to compensate for zeros?
Thanks in advance,
Erick Lichtas
<http://www.linomasoftware.com/> Linoma-Software-Logo
1409 Silver Street, Ashland, NE 68003
1-800-949-4696 x714
RE: Date formatted cell with fraction of seconds
Posted by Erick Lichtas <el...@linoma.com>.
Hi all,
I have copied the DateUtil code into my own local class and updated the
regular expression syntax to accommodate for zeros in the pattern. I will
use my local DateUtil class for now, but wanted to see everyone's thoughts
on my changes.
So my test cases are as follows
I have updated the pattern on line 234 of the DateUtil class
Original: "^[yYmMdDhHsS\\-/,. :]+[ampAMP/]*$"
Updated: "^[yYmMdDhHsS\\-/ :][yYmMdDhHsS\\-/,. :]*[.0 ]*[ampAMP/]*$"
Adding 0 to the pattern is tricky because it could cause multiple problems.
If 0 was simply added to the first group of characters, then simple Number
patterns, such as 0 or 0.00, would be matched. 0 in most cases should only
be used at the end of the date/time pattern. In fact, it is only mentioned
by Microsoft Support (http://support.microsoft.com/kb/264372) to be used to
show fractions of seconds.
I have added the first group of characters "[yYmMdDhHsS\\-/ :]" to ensure
that the date/time pattern does not start with . (period) or , (comma).
This is so that the patterns similar to .0 do not match the date pattern
either.
Here are a hand full of test patterns I passed through this method that work
as expected.
"yyyy-mm-dd hh:mm:ss.000"
"yyyy-mm-dd hh:mm:ss"
"hh:mm:ss.0 am/pm"
"yyyy-mm-dd hh:mm:ss.000 A/P"
"mm/dd/yy"
"mm-dd-yyyy"
"hh:mm:ss"
"mm:ss.0"
"mm:ss.000"
"0.000"
".00"
".0"
"#.#"
"#.000"
""
I am hoping some of you have some suggestions to improve this pattern even
more. Also if everyone is happy with it, I would like to propose that this
pattern get updated to support 0's in the pattern.
Thanks
Erick
-----Original Message-----
From: MSB [mailto:markbrdsly@tiscali.co.uk]
Sent: Wednesday, December 09, 2009 11:23 AM
To: user@poi.apache.org
Subject: Re: Date formatted cell with fraction of seconds
Hello Erik,
To my mind there are two courses of action open to you. The first would be
to patch POI so that the check made by the DateUtils class returns true for
dates that are formarred in that particular manner.
The other would be to change the way that you are checking for dates; get
the HSSFCellStyle object that is applied to the cell, call the
getDataFormatString() method on that object and test the String that you
will receive back from that method call. Speaking completely off of the top
of my head, I would reckon that you could modify the existing code that you
have to perform the DateUtil.isCellDateFormatted() check first and then, if
this fails, recover the cells style, get the format String and then test it
then finally, if this test fails assume you have a numeric value rather than
a date. I have never used formats like those that you describe so I do not
know what format String you will get back but I would hope that it resembles
the examples you gave in your original post; it will be easy enough to test
anyway.
The latter option would most likely solve your problem but it would be great
if you would consider submitting a patch for the API at some time in the
future.
Yours
Mark B
PS the getDataFormatString() method is defined on the Cell interface so it
will work equally well with code that addresses the OpenXML or binary
streams or even with that written using the SS model.
Erick Lichtas wrote:
>
> Hi all,
>
>
>
> I am reading data from a spreadsheet using code similar to that below.
>
>
>
> switch (cell.getCellType()) {
>
> ...
>
> case Cell.CELL_TYPE_NUMERIC:
>
> if (DateUtil.isCellDateFormatted(cell)) {
>
> // handle date type
>
> ...
>
> }
>
> // handle numeric type
>
> ...
>
> }
>
>
>
> I am running into an issue when the cell format is a custom pattern like
> 'yyyy-mm-dd hh:mm:ss.0'. Notice the 0 at the end of the pattern, used to
> display the 10th of a second. For this cell, the
> DateUtil.isCellDateFormatted(cell) is returning false and the value I am
> getting is a number.
>
>
>
> Upon inspection of the isCellDateFormatted() method, the lines
>
>
>
> // Otherwise, check it's only made up, in any case, of:
>
> // y m d h s - / , . :
>
> // optionally followed by AM/PM
>
> if(fs.matches("^[yYmMdDhHsS\\-/,. :]+[ampAMP/]*$")) {
>
> return true;
>
> }
>
>
>
> do not take in to consideration the zeros that could be present in a date
> pattern. According to Microsoft Office Support, "If you want to display
> fractions of a second, use a number format similar to h:mm:ss.00.", found
> at
> http://support.microsoft.com/kb/264372
>
>
> Is there a workaround for determining that a cell formatted like
> 'yyyy-mm-dd
> hh:mm:ss.0' is a Date cell? Or would I have to modify this regex pattern
> in
> the DateUtil class in order to compensate for zeros?
>
>
>
> Thanks in advance,
>
>
>
> Erick Lichtas
>
>
>
> <http://www.linomasoftware.com/> Linoma-Software-Logo
>
> 1409 Silver Street, Ashland, NE 68003
>
> 1-800-949-4696 x714
>
>
>
>
>
>
--
View this message in context:
http://old.nabble.com/Date-formatted-cell-with-fraction-of-seconds-tp2671335
0p26714019.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4673 (20091209) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
__________ Information from ESET NOD32 Antivirus, version of virus signature
database 4676 (20091210) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Date formatted cell with fraction of seconds
Posted by MSB <ma...@tiscali.co.uk>.
Hello Erik,
To my mind there are two courses of action open to you. The first would be
to patch POI so that the check made by the DateUtils class returns true for
dates that are formarred in that particular manner.
The other would be to change the way that you are checking for dates; get
the HSSFCellStyle object that is applied to the cell, call the
getDataFormatString() method on that object and test the String that you
will receive back from that method call. Speaking completely off of the top
of my head, I would reckon that you could modify the existing code that you
have to perform the DateUtil.isCellDateFormatted() check first and then, if
this fails, recover the cells style, get the format String and then test it
then finally, if this test fails assume you have a numeric value rather than
a date. I have never used formats like those that you describe so I do not
know what format String you will get back but I would hope that it resembles
the examples you gave in your original post; it will be easy enough to test
anyway.
The latter option would most likely solve your problem but it would be great
if you would consider submitting a patch for the API at some time in the
future.
Yours
Mark B
PS the getDataFormatString() method is defined on the Cell interface so it
will work equally well with code that addresses the OpenXML or binary
streams or even with that written using the SS model.
Erick Lichtas wrote:
>
> Hi all,
>
>
>
> I am reading data from a spreadsheet using code similar to that below.
>
>
>
> switch (cell.getCellType()) {
>
> ...
>
> case Cell.CELL_TYPE_NUMERIC:
>
> if (DateUtil.isCellDateFormatted(cell)) {
>
> // handle date type
>
> ...
>
> }
>
> // handle numeric type
>
> ...
>
> }
>
>
>
> I am running into an issue when the cell format is a custom pattern like
> 'yyyy-mm-dd hh:mm:ss.0'. Notice the 0 at the end of the pattern, used to
> display the 10th of a second. For this cell, the
> DateUtil.isCellDateFormatted(cell) is returning false and the value I am
> getting is a number.
>
>
>
> Upon inspection of the isCellDateFormatted() method, the lines
>
>
>
> // Otherwise, check it's only made up, in any case, of:
>
> // y m d h s - / , . :
>
> // optionally followed by AM/PM
>
> if(fs.matches("^[yYmMdDhHsS\\-/,. :]+[ampAMP/]*$")) {
>
> return true;
>
> }
>
>
>
> do not take in to consideration the zeros that could be present in a date
> pattern. According to Microsoft Office Support, "If you want to display
> fractions of a second, use a number format similar to h:mm:ss.00.", found
> at
> http://support.microsoft.com/kb/264372
>
>
> Is there a workaround for determining that a cell formatted like
> 'yyyy-mm-dd
> hh:mm:ss.0' is a Date cell? Or would I have to modify this regex pattern
> in
> the DateUtil class in order to compensate for zeros?
>
>
>
> Thanks in advance,
>
>
>
> Erick Lichtas
>
>
>
> <http://www.linomasoftware.com/> Linoma-Software-Logo
>
> 1409 Silver Street, Ashland, NE 68003
>
> 1-800-949-4696 x714
>
>
>
>
>
>
--
View this message in context: http://old.nabble.com/Date-formatted-cell-with-fraction-of-seconds-tp26713350p26714019.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org