poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bengt Rodehav <be...@rodehav.com>
Subject Detect if a cell is date formatted
Date Mon, 07 Mar 2016 15:52:57 GMT
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

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message