Mailing-List: contact user-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Users List" <user@poi.apache.org>
MIME-Version: 1.0
In-Reply-To: 
 <CAJ0TPG+OEq2eLr82XuNRRtqETRgU4_869jD4U9VSuLWg7++ERw@mail.gmail.com>
References: 
 <CAJ0TPGLWBx8xamA4_5_KfXcHZe+BAwbRBGS57YHz8V-XWPknww@mail.gmail.com>
	<CAFGkfm2-Zjmnayh4JgJvzd9HVXyY7CQuNfDTQMVU2gT_WOVO0w@mail.gmail.com>
	<CAJ0TPG+OEq2eLr82XuNRRtqETRgU4_869jD4U9VSuLWg7++ERw@mail.gmail.com>
Date: Wed, 9 Mar 2016 09:47:39 -0800
Message-ID: 
 <CAFGkfm0n6HGVuvY2rXe3aYh+i3SDV3ZEmmwM=EENhXfF18tGwg@mail.gmail.com>
Subject: Re: Detect if a cell is date formatted
From: Blake Watson <blake.watson@pnmac.com>
To: POI Users List <user@poi.apache.org>
Content-Type: multipart/alternative; boundary=001a11431b784dfe23052da14ba3

--001a11431b784dfe23052da14ba3
Content-Type: text/plain; charset=UTF-8

That's cool! I'd be concerned about whether it would work in a situation
where a column has been defined as a numeric one (i.e., so the cells could
easily be nil), but I hadn't thought of using "IsADateFormat" before...

On Wed, Mar 9, 2016 at 1:16 AM, Bengt Rodehav <bengt@rodehav.com> wrote:

> Thanks for your reply Blake,
>
> Yeah I was actually considering this (put a number in, check if date, then
> clear the cell) but I instead did this:
>
> I copied the following method from DateUtil and removed the check against
> the value of the cell.
>
>   public static boolean isCellDateFormatted(Cell cell) {
>     if (cell == null) return false;
>     boolean bDate = false;
>
> //    double d = cell.getNumericCellValue();
> //    if ( DateUtil.isValidExcelDate(d) ) {
>         CellStyle style = cell.getCellStyle();
>         if(style==null) return false;
>         int i = style.getDataFormat();
>         String f = style.getDataFormatString();
>         bDate = DateUtil.isADateFormat(i, f);
> //    }
>     return bDate;
> }
>
> Now only the actual format is checked, not the value. I would prefer not
> having to duplicate and maintain this code but I think it's still better
> than having to put temporary values in cells. I think the method is not
> properly named since it doesn't only check the formatting but also throws
> in a check concerning the cell value. I think it would be wise to add an
> extra parameter indicating whether the cell's value should be checked as
> well or if it is just a format check. The old version (with just the Cell
> parameter) could still exist but call the new method with a second
> parameter indicating that the cell's value should be checked.
>
> /Bengt
>
> 2016-03-07 18:53 GMT+01:00 Blake Watson <blake.watson@pnmac.com>:
>
> > It may not be possible, in the sense that Excel may not decide that a
> cell
> > is a date unless it has a numeric value in it and is so formatted. I've
> had
> > a lot of what seem to be odd phenomena around that, which make sense if
> you
> > consider how spreadsheets evolved and are commonly used.
> >
> > I realize it's an extra step but if you can't without putting a number
> in,
> > why not put a number in?
> >
> > On Mon, Mar 7, 2016 at 7:52 AM, Bengt Rodehav <bengt@rodehav.com> wrote:
> >
> > > I have a situation where an Excel template I get from a third party
> (this
> > > is for EIOPA's Solvency II reporting) wrongly has set date formatting
> in
> > a
> > > number of cells. I want to fix this programatically.
> > >
> > > I use code like this to try to detect date formatting:
> > >
> > > if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
> > >   if (DateUtil.isCellDateFormatted(cell)) {
> > >     System.out.println("  Date at sheet: " + sheetName + ", row: " +
> > > row.getRowNum() + ", column: " + cell.getColumnIndex());
> > >   }
> > > }
> > >
> > > However, when the cells are empty (remember this is a template), the
> cell
> > > type always seems to be Cell.CELL_TYPE_STRING not
> Cell.CELL_TYPE_NUMERIC
> > so
> > > the above code doesn't find any date cells.
> > >
> > > If I change the code to...
> > >
> > > if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
> > >   if (DateUtil.isCellDateFormatted(cell)) {
> > >     System.out.println("  Date at sheet: " + sheetName + ", row: " +
> > > row.getRowNum() + ", column: " + cell.getColumnIndex());
> > >   }
> > > }
> > >
> > > ...then I get an exceptions since the DateUtil.isCellDateFormatted()
> > > requires the cell to contain a numerice value.
> > >
> > > So, I conclude that the cell's type seems to be determined by the value
> > in
> > > the cell - not the metadata for the cell. This seems a bit strange to
> me.
> > > The problem is that if the user enters a numeric value in the cell,
> then
> > it
> > > will be date formatted (which it shouldn't). The user is allowed to
> enter
> > > any string (even a numeric value). So, I want to remove the date
> > formatting
> > > regardless of the cell type. But I cannot detect that it is a date
> > unless I
> > > enter a numeric value in the cell.
> > >
> > > Does anyone have a tip as to how I can detect date formatting without
> > > requiring a value in the cell?
> > >
> > > /Bengt
> > >
> >
> >
> >
> > --
> >
> > *Blake Watson*
> >
> > *PNMAC*
> > Application Development Manager
> > 5898 Condor Drive
> > Moorpark, CA 93021
> > (805) 330.4911 x7742
> > blake.watson@pnmac.com <melanie.petrosyan@pnmac.com>
> > www.PennyMacUSA.com <http://www.pennymacusa.com/>
> >
>


-- 

*Blake Watson*

*PNMAC*
Application Development Manager
5898 Condor Drive
Moorpark, CA 93021
(805) 330.4911 x7742
blake.watson@pnmac.com <melanie.petrosyan@pnmac.com>
www.PennyMacUSA.com <http://www.pennymacusa.com/>

--001a11431b784dfe23052da14ba3--