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:[email protected]] Sent: Wednesday, December 09, 2009 11:23 AM To: [email protected] 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: [email protected] For additional commands, e-mail: [email protected] __________ 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: [email protected] For additional commands, e-mail: [email protected]
