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