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