CODEWITHSUNDEEP
apache-poi
user1052610
user1052610

Reputation: 4719

Using POI to find out format for a cell

What is the correct way to get the CellFormatType for a cell (DATE, NUMBER, TEXT, GENERAL) ?

cell.getCellStyle.getDataFormat() returns a Short value, which does not map to the above constants.

I cannot just use cell.getCellType for the following reason. For certain rows, there may be a string prefix like <> or > in front of the value. In that case, getCellType will return CELL_TYPE_STRING. So the only way to get the underlying type appears to be to look at the format for the column.

Thanks

Upvotes: 1

Views: 2009

Answers (1)

Dave
Dave

Reputation: 46349

I don't know a direct route from Cell to CellFormatType, but there are various ways to determine particular types:

  • General? getDataFormat will be 0 (or more reliably, getDataFormatString will be "General")
  • Date? Check out DateUtil.isCellDateFormatted
  • Text? Typically you'd check for Cell.getCellType = CELL_TYPE_STRING, but since you say you can't do this (see comments below), you could also try checking if getDataFormatString is "@" or "text" (both are possibilities for plain text)
  • Number? Again, usually you'd just check for CELL_TYPE_NUMERIC. Note that dates are also considered numbers, so check for dates first.

(If the cell type is CELL_TYPE_FORMULA you should check getCachedFormulaResultType instead)

I cannot just use cell.getCellType for the following reason. For certain rows, there may be a string prefix like <> or > in front of the value

I'm not familiar with this problem. Perhaps you have a custom format which prepends characters before the number? My notes on text detection above might help you in this case, but I suggest you double-check that your spreadsheet actually has the relevant information (i.e. it isn't just Excel being clever when you re-open the sheet): log the cell type, format and format string, and confirm that there actually is a difference on the relevant cells. It's possible that your cell is actually being saved as text, with no distinguishing mark. In this case, you'll need to add some special heuristics for your specific scenario.

Finally, since you seem confused about the returned value from CellStyle.getDataFormat; it's just an index in a sheet-wide index → format-string table. You can (and I'd say usually should) use getDataFormatString to get the string format directly. The standard cell formats are listed in BuiltinFormats, which you can use as a reference to see which format strings you might find (but always check the format string, not the ID, otherwise you'll fail to detect custom formats correctly).

Upvotes: 3

Related Questions