Thanks ,this looks really useful. One thing, could you advise how you have defined global variable months? Thanks -DAB
> -----Original Message----- > From: Koundinya (Sudhakar Chavali) > [mailto:[EMAIL PROTECTED] > Sent: Friday, March 26, 2004 1:21 AM > To: POI Users List > Subject: RE: Date Cell Recognition > > > Hope this helps > private String getDate(HSSFCell cell) { > > // format in form of M/D/YY > java.util.Calendar _calendar = > java.util.Calendar.getInstance(); > > //get the Java Date from the HSSF cell > > _calendar.setTime(HSSFDateUtil.getJavaDate(cell.getNumericCell > Value())); > > //get Cell data format > HSSFCellStyle cellStyle = cell.getCellStyle(); > short dataFormat = cellStyle.getDataFormat(); > > int hours = _calendar.get(Calendar.HOUR_OF_DAY); > int minuites = _calendar.get(Calendar.MINUTE); > int seconds = _calendar.get(Calendar.SECOND); > > String cellText = ""; > > // Identify Cell data format > > switch (dataFormat) { > case 14: { //0xe, "m/d/yy" > cellText = > (String.valueOf(_calendar.get(Calendar.YEAR))).substring(2); > cellText = _calendar.get(Calendar.MONTH) + 1 + "/" + > _calendar.get(Calendar.DAY_OF_MONTH) + "/" + > cellText; > break; > } > case 15: { //0xf, "d-mmm-yy" > cellText = > (String.valueOf(_calendar.get(Calendar.YEAR))).substring(2); > cellText = _calendar.get(Calendar.DAY_OF_MONTH) + "-" + > months.get(new > Integer(_calendar.get(Calendar.MONTH))) + "-" + > cellText; > break; > > } > case 16: { //0x10, "d-mmm" > cellText = _calendar.get(Calendar.DAY_OF_MONTH) + "-" + > months.get(new > Integer(_calendar.get(Calendar.MONTH))); > break; > > } > case 17: { //0x11, "mmm-yy" > cellText = > (String.valueOf(_calendar.get(Calendar.YEAR))).substring(2); > cellText = months.get(new > Integer(_calendar.get(Calendar.MONTH))) + "-" + > cellText; > break; > > } > case 22: { //0x16, "m/d/yy h:mm" > cellText = > (String.valueOf(_calendar.get(Calendar.YEAR))).substring(2); > cellText = _calendar.get(Calendar.MONTH) + 1 + "/" + > _calendar.get(Calendar.DAY_OF_MONTH) + "/" + > cellText; > cellText = cellText + " " + hours + ":" + minuites; > break; > } > case 18: { // 0x12, "h:mm AM/PM" > > String tmz = "AM"; > if (hours > 12) { > hours = hours - 12; > tmz = "PM"; > > } > cellText = cellText + " " + hours + ":" + minuites + " " > + tmz; > break; > } > case 19: { //0x13, "h:mm:ss AM/PM" > String tmz = "AM"; > if (hours > 12) { > hours = hours - 12; > tmz = "PM"; > } > cellText = cellText + " " + hours + ":" + minuites + ":" > + seconds + > " " + tmz; > break; > } > case 20: { //0x14, "h:mm" > cellText = cellText + " " + hours + ":" + minuites; > break; > } > case 21: { //0x15, "h:mm:ss" > cellText = cellText + " " + hours + ":" + minuites + ":" > + seconds; > break; > } > default: { > cellText = > (String.valueOf(_calendar.get(Calendar.YEAR))).substring(2); > cellText = _calendar.get(Calendar.DAY_OF_MONTH) + "-" + > months.get(new > Integer(_calendar.get(Calendar.MONTH))) + "-" + > cellText; > cellText = cellText + " " + hours + ":" + minuites + ":" > + seconds; > break; > } > } > return cellText; > } > > > > > > > > > > > --- Matt Puccini <[EMAIL PROTECTED]> wrote: > > 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/mo > s/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] > > > > > ===== > "No one can earn a million dollars honestly."- William > Jennings Bryan (1860-1925) > > "Make everything as simple as possible, but not simpler."- > Albert Einstein (1879-1955) > > "It is dangerous to be sincere unless you are also stupid."- > George Bernard Shaw (1856-1950) > > __________________________________ > Do you Yahoo!? > Yahoo! Finance Tax Center - File online. File on time. > http://taxes.yahoo.com/filing.html > > --------------------------------------------------------------------- > 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]
