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]>
