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 <[email protected]>:
> 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 <[email protected]> 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
> [email protected] <[email protected]>
> www.PennyMacUSA.com <http://www.pennymacusa.com/>
>
