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 <[email protected]> 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 <[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/> > > > -- *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/>
