poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bigwood, David" <dbigw...@metatomix.com>
Subject RE: Date Cell Recognition
Date Fri, 26 Mar 2004 14:14:03 GMT
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:sudhakar_koundinya@yahoo.com] 
> 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 <matt.puccini@acu-power.com> 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:dbigwood@metatomix.com]
> > 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:matt.puccini@acu-power.com] 
> > > 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:dbigwood@metatomix.com]
> > > 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:matt.puccini@acu-power.com] 
> > > > 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:dbigwood@metatomix.com]
> > > > 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:
> > poi-user-unsubscribe@jakarta.apache.org
> > > > For additional commands, e-mail:
> > poi-user-help@jakarta.apache.org
> > > > 
> > > > 
> > > > 
> > >
> >
> ---------------------------------------------------------------------
> > > > To unsubscribe, e-mail:
> > poi-user-unsubscribe@jakarta.apache.org
> > > > For additional commands, e-mail:
> > poi-user-help@jakarta.apache.org
> > > > 
> > > > 
> > > 
> > >
> >
> ---------------------------------------------------------------------
> > > To unsubscribe, e-mail:
> > poi-user-unsubscribe@jakarta.apache.org
> > > For additional commands, e-mail:
> > poi-user-help@jakarta.apache.org
> > > 
> > > 
> > >
> >
> ---------------------------------------------------------------------
> > > To unsubscribe, e-mail:
> > poi-user-unsubscribe@jakarta.apache.org
> > > For additional commands, e-mail:
> > poi-user-help@jakarta.apache.org
> > > 
> > > 
> > 
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail:
> > poi-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail:
> > poi-user-help@jakarta.apache.org
> > 
> > 
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail:
> > poi-user-unsubscribe@jakarta.apache.org
> > For additional commands, e-mail:
> > poi-user-help@jakarta.apache.org
> > 
> 
> 
> =====
> "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: poi-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: poi-user-help@jakarta.apache.org
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org


Mime
View raw message