Hello,
I would like to think the same, but the function is really only checking
against a double and it appears that it can be fooled into thinking that
the date is valid, while it is not.
I am parsing an Excel spreadsheet and I just entered a couple fields to
test, one with a data and one with a number (10,000). Here is the code
that I check with.
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord) record;
System.out.println("number: " +
HSSFDateUtil.getJavaDate(numrec.getValue())
+ "format: " + numrec.getXFIndex() + "is valid: " +
HSSFDateUtil.isValidExcelDate(numrec.getValue()));
and here is the output from it
number: Tue Jun 28 00:00:00 EDT 1960 format: 21 is valid: true
number: Wed May 18 00:00:00 EDT 1927 format: 22 is valid:true //this is
bad line which as 10,000 in the spreadsheet.
Bill
Jason Hoffman wrote:
> Well, as I understood it, there is no way a numeric field can pass the
> "isCellDateFormatted" logic unless it actually has one of the documented
> Date Formats as its format. If it is actually a number, I would
> question why IS it date formatted?
> I have witnessed some strangeness in Excel, where a cell is populated
> with a number, formatted with another format, and then changed to a date
> format. The cell does not actually CHANGE its display to date until
> someone re-enters the value into the cell. I wonder if this could be
> what you are seeing here?
>
> Jason Hoffman
> Bill Neumann wrote:
>
>> Hello,
>>
>> I have been working on the same problem and the solutions I have seen
>> so far do not seem to be totally valid, since a numeric field can also
>> be just a number and not a date and pass the test given by
>> isCellDateFormatted. So from what I can see there is not really a way
>> to validate whether a field is really a number or a date.
>>
>> Bill
>> Andrew C. Oliver wrote:
>>
>>> I'd rather see an "isDateFormat") in HSSFFormat.
>>>
>>> -Andy
>>>
>>> Glen Stampoultzis wrote:
>>>
>>>> HSSFCell makes sense. Patch away.
>>>>
>>>> -- Glen
>>>>
>>>> ----- Original Message -----
>>>> From: "Jason Hoffman" <[EMAIL PROTECTED]>
>>>> To: <[EMAIL PROTECTED]>
>>>> Sent: Friday, May 03, 2002 10:12 PM
>>>> Subject: Re: Dates!!
>>>>
>>>>
>>>>> Glen,
>>>>>
>>>>> If this method was to be added to POI ( assuming you haven't already
>>>>> done it ) would you have any preference as to its location.
>>>>> Personally,
>>>>> I think it makes sense in HSSFCell, but wanted to get a blessing
>>>>> before
>>>>> creating the patch. Any thoughts?
>>>>>
>>>>> Glen Stampoultzis wrote:
>>>>>
>>>>>> Okay, I've done some investigation of this. Looks like your date has
>>>>>>
>>>> been
>>>>
>>>>>> defined as a custom format.
>>>>>>
>>>>>> Not sure what you can do about that one. There's really nothing
>>>>>> there
>>>>>> except the format string to tell whether it's a date or not and
>>>>>> unfortunately the format string can take many forms.
>>>>>>
>>>>>> Regards,
>>>>>>
>>>>>> Glen
>>>>>>
>>>>>> ----- Original Message -----
>>>>>> From: "Agarwal, Vinay (Cognizant)" <[EMAIL PROTECTED]>
>>>>>> To: <[EMAIL PROTECTED]>
>>>>>> Cc: <[EMAIL PROTECTED]>
>>>>>> Sent: Friday, May 03, 2002 2:46 PM
>>>>>> Subject: RE: Dates!!
>>>>>>
>>>>>>
>>>>>>> Yeah sure....
>>>>>>> It's fmc_sample.xls
>>>>>>>
>>>>>>>
>>>>>>> -----Original Message-----
>>>>>>> From: Glen Stampoultzis [mailto:[EMAIL PROTECTED]]
>>>>>>> Sent: Friday, May 03, 2002 9:58 AM
>>>>>>> To: [EMAIL PROTECTED]
>>>>>>> Subject: Re: Dates!!
>>>>>>>
>>>>>>>
>>>>>>> Must be an undocumented format. Are you able to send a sample
>>>>>>>
>>>>>> spreadsheet?
>>>>>>
>>>>>>> (Either to the list or [EMAIL PROTECTED] if you don't want the whole
>>>>>>>
>>>> world
>>>>
>>>>>>> seeing your workbook).
>>>>>>>
>>>>>>> Thanks,
>>>>>>>
>>>>>>> Glen
>>>>>>>
>>>>>>> ----- Original Message -----
>>>>>>> From: "Agarwal, Vinay (Cognizant)" <[EMAIL PROTECTED]>
>>>>>>> To: <[EMAIL PROTECTED]>
>>>>>>> Sent: Friday, May 03, 2002 2:27 PM
>>>>>>> Subject: RE: Dates!!
>>>>>>>
>>>>>>>
>>>>>>>> Date format in excel looks like '01-Jan-94'
>>>>>>>>
>>>>>>>> -----Original Message-----
>>>>>>>> From: Glen Stampoultzis [mailto:[EMAIL PROTECTED]]
>>>>>>>> Sent: Friday, May 03, 2002 9:44 AM
>>>>>>>> To: [EMAIL PROTECTED]
>>>>>>>> Subject: Re: Dates!!
>>>>>>>>
>>>>>>>>
>>>>>>>> What does your date format look like in Excel?
>>>>>>>>
>>>>>>>> -- Glen
>>>>>>>>
>>>>>>>> ----- Original Message -----
>>>>>>>> From: "Agarwal, Vinay (Cognizant)" <[EMAIL PROTECTED]>
>>>>>>>> To: <[EMAIL PROTECTED]>
>>>>>>>> Sent: Friday, May 03, 2002 2:13 PM
>>>>>>>> Subject: RE: Dates!!
>>>>>>>>
>>>>>>>>
>>>>>>>>> It doesn't work for me......
>>>>>>>>> Interesting tning is when I add one more case to your list of
>>>>>>>>> cases
>>>>>>>>>
>>>>>> ,it
>>>>>>
>>>>>>>>> works.
>>>>>>>>>
>>>>>>>>> I added case 165: and it worked....
>>>>>>>>>
>>>>>>>>> Something like this...
>>>>>>>>>
>>>>>>>>> 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:
>>>>>>>>> case 165: // IT WORKS HERE IN THIS CASE
>>>>>>>>> bDate = true;
>>>>>>>>> break;
>>>>>>>>> default:
>>>>>>>>> bDate = false;
>>>>>>>>> break;
>>>>>>>>>
>>>>>>>>> Any answers to this......
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> -----Original Message-----
>>>>>>>>> From: Hoffman, Jason [mailto:[EMAIL PROTECTED]]
>>>>>>>>> Sent: Thursday, May 02, 2002 6:15 PM
>>>>>>>>> To: '[EMAIL PROTECTED]'
>>>>>>>>> Subject: RE: Dates!!
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Okay, here is a little code I used to determine if the cell was a
>>>>>>>>>
>>>>>> number
>>>>>>
>>>>>>>> or
>>>>>>>>
>>>>>>>>> date, and then format appropriately. I hope it helps. I keep
>>>>>>>>> meaning
>>>>>>>>>
>>>>>>> to
>>>>>>>
>>>>>>>>> submit a patch with the helper method below.... but just
>>>>>>>>> haven't had a
>>>>>>>>> chance.
>>>>>>>>>
>>>>>>>>> /////// code snippet ////////////
>>>>>>>>> case HSSFCell.CELL_TYPE_NUMERIC:
>>>>>>>>> double d = cell.getNumericCellValue();
>>>>>>>>> // test if a date!
>>>>>>>>> if (isCellDateFormatted(cell)) {
>>>>>>>>> // format in form of M/D/YY
>>>>>>>>> cal.setTime(HSSFDateUtil.getJavaDate(d));
>>>>>>>>> cellText =
>>>>>>>>>
>>>>>>>> (String.valueOf(cal.get(Calendar.YEAR))).substring(2);
>>>>>>>>
>>>>>>>>> cellText = cal.get(Calendar.MONTH)+1 + "/" +
>>>>>>>>> cal.get(Calendar.DAY_OF_MONTH) +
>>>>>>>>> "/" +
>>>>>>>>> cellText;
>>>>>>>>> }
>>>>>>>>> /////// end code snippet ////////////
>>>>>>>>>
>>>>>>>>> // HELPER METHOD BELOW TO DETERMINE IF DATE
>>>>>>>>>
>>>>>>>>> // 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 ( HSSFDateUtil.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;
>>>>>>>>> }
>>>>>>>>>
>>>>>>>>> -----Original Message-----
>>>>>>>>> From: Agarwal, Vinay (Cognizant) [mailto:[EMAIL PROTECTED]]
>>>>>>>>> Sent: Thursday, May 02, 2002 5:22 AM
>>>>>>>>> To: [EMAIL PROTECTED]
>>>>>>>>> Subject: Dates!!
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> Hi, I am not sure if this is a bug .
>>>>>>>>> I am trying to read a xls file which has a cell with Date
>>>>>>>>> format.
>>>>>>>>> I have used EventExample.java for this purpose. What is
>>>>>>>>> happening
>>>>>>>>>
>>>>>> is
>>>>>>
>>>>>>>>> that
>>>>>>>>> instead of date it returns me a number. I looked at the APIs for
>>>>>>>>>
>>>>>>>> DateRecord
>>>>>>>>
>>>>>>>>> but
>>>>>>>>> there isn't such class. I am just wondering how to get around this
>>>>>>>>>
>>>>>>>> problem,I
>>>>>>>>
>>>>>>>>> am
>>>>>>>>> aware that Excel stores dates as numbers and probably i am getting
>>>>>>>>>
>>>>>> that
>>>>>>
>>>>>>>>> number.
>>>>>>>>>
>>>>>>>>> Why dont we have DateRecord class similar to NumericRecord.
>>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>> --
>> To unsubscribe, e-mail:
>> <mailto:[EMAIL PROTECTED]>
>> For additional commands, e-mail:
>> <mailto:[EMAIL PROTECTED]>
>>
>>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
