That was it thanks for the feedback.
Avik Sengupta wrote:
> The HSSFDateUtil.isValidExcelDate(numrec.getValue())) function just checks if
> the double number is in the correct range. To check if the cell is formatted
> with the date, check the HSSFDateUtil.isCellDateFormatted(HSSFCell cell)
> method, that checks for the formatting on the cell irrespective of the value.
>
> Quoting Bill Neumann <[EMAIL PROTECTED]>:
>
>
>>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]>
>>
>>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
