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.getNumericCellValue()));
//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/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]
>
=====
"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]
