It seems to work so I think I'll stick with this solution for now. /Bengt
2016-03-09 18:47 GMT+01:00 Blake Watson <[email protected]>: > 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/> >
