David, I noticed after thinking more about your second question that you and I probably had the opposite problem - I was having things that weren't dates returning as dates, where your dates return as non-dates. Sorry about that. To answer your question, I have not noticed that. The sheets I download and read don't use custom formats, but using the code I sent you, they pass the date test. If you want to take a look at them, they are publicly available: http://www.ercot.com/ercotPublicWeb/PublicMarketInformation/mos/Operating_Day_Report/2004-01_BES.xls is an example of one. I read the sheets named after days of the month. i.e. "31"
-matt -----Original Message----- From: Bigwood, David [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 8:23 AM To: POI Users List Subject: RE: Date Cell Recognition Thanks for the response Matt. I have your code running now and I seem to almost be getting the generic XML version of the processed spreadsheet that I wanted. One thing I have noticed is that dates are only recognized as dates if the cell is CUSTOM formatted (i.e. the dd-mm-yyyy type) and not if they are specific DATE cell formats. Is that something you noticed as well? -DAB > -----Original Message----- > From: Matt Puccini [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 24, 2004 6:11 PM > To: POI Users List > Subject: RE: Date Cell Recognition > > > Sorry, that one is pretty important; here's the previous two > statements before the if: > > case HSSFCell.CELL_TYPE_NUMERIC : > double d = cell.getNumericCellValue(); > > For your other question: > ...should those clauses actually be ANDed or ORed? i.e. do > all the terms have to be true for a cell to be a date, or > just one of them... > > I'm not sure, to tell you the truth. I know I needed all > three conditions to determine a valid date - I had many > values return true for the first two conditions though they > weren't really dates. > > -matt > > > -----Original Message----- > From: Bigwood, David [mailto:[EMAIL PROTECTED] > Sent: Wednesday, March 24, 2004 3:55 PM > To: POI Users List > Subject: RE: Date Cell Recognition > > > Oh and one more thing where does the variable d come from in: > > HSSFDateUtil.isValidExcelDate(d) && > > > > > -----Original Message----- > > From: Matt Puccini [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, March 24, 2004 5:47 PM > > To: POI Users List > > Subject: RE: Date Cell Recognition > > > > > > David, > > > > I had some trouble with that too. I am using the usermodel, > > and my solution may be a bit clunky, but it worked for me. > Here it is: > > > > HSSFCell cell = row.getCell((short)c); > > //... > > if (HSSFDateUtil.isCellDateFormatted(cell) && > > HSSFDateUtil.isValidExcelDate(d) && > > isDateFormat(cell)) { > > //... > > } > > //... > > > > /** > > * Tests to see if a cell format is recognized as a date format. > > * Strangely, the HSSFCellStyle.getDataFormat() function will > > * sometimes return values that aren't valid indexes in the > > * HSSFDataFormat.getBuiltinFormat() function - thus the catch > > * ArrayIndexOutOfBoundsException. I had some dates return an > > * index of 164, which I hard-coded into the catch > clause. Make > > * sure it doesn't miss other valid date formats > because of this. > > */ > > > > private boolean isDateFormat(HSSFCell hCell) { > > try { > > String formatString = > > > > > HSSFDataFormat.getBuiltinFormat(hCell.getCellStyle().getDataFormat()); > > if(formatString.equals("m/d/yy") || > > formatString.equals("d-mmm-yy") || > > formatString.equals("d-mmm") || > > formatString.equals("mmm-yy") || > > formatString.equals("m/d/yy h:mm")) { > > return true; > > } > > } > > catch (ArrayIndexOutOfBoundsException ex) { > > /* a bit of hard code - I don't know why, but sometimes > > * valid dates are returning with an index of 164... > > * Anyone knows why please let me know. > > */ > > if(hCell.getCellStyle().getDataFormat() == 164) { > > return true; > > } > > return false; > > } > > return false; > > } > > > > -----Original Message----- > > From: Bigwood, David [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, March 24, 2004 3:30 PM > > To: POI Users List > > Subject: Date Cell Recognition > > > > > > Has anybody successfully managed to recognize a date cell and > > differentiate > > it from a numeric cell for either: > > > > 1. eventmodel > > 2. usermodel > > > > And would they care to contribute a code snippet. I cannot > > figure out how to > > do it reliably with the eventmodel and the method used in the > > usermodel (see > > below) always returns false even even when cell is date formatted. > > > > boolean isDate = HSSFDateUtil.isCellDateFormatted(cell); > > > > Any insight you may have would be great. > > > > -DAB > > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > > --------------------------------------------------------------------- > > To unsubscribe, e-mail: [EMAIL PROTECTED] > > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
