poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Blake Watson <blake.wat...@pnmac.com>
Subject Re: Detect if a cell is date formatted
Date Mon, 07 Mar 2016 17:53:32 GMT
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