poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bengt Rodehav <be...@rodehav.com>
Subject Re: Detect if a cell is date formatted
Date Wed, 09 Mar 2016 09:16:04 GMT
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/>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message