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/>
