I'm using HSSF-POI for reading excel data. The problem is I have values in a cell that look like a number but really are strings. If I look at the format cell in Excel, it says the type is "text". Still the HSSF Cell thinks it's numeric. How can I get the value as a string?
If I try to use cell.getRichStringValue
, I get exception; if cell.toString
, it's not the exact same value as in Excel sheet.
Edit: until this gets resolved, I'll use
new BigDecimal(cell.getNumericCellValue()).toString()
Best Answer
The class you're looking for in POI is DataFormatter
When Excel writes the file, some cells are stored as literal Strings, while others are stored as numbers. For the latter, a floating point value representing the cell is stored in the file, so when you ask POI for the value of the cell that's what it actually has.
Sometimes though, especially when doing Text Extraction (but not always), you want to make the cell value look like it does in Excel. It isn't always possible to get that exactly in a String (non full space padding for example), but the DataFormatter class will get you close.
If you're after a String of the cell, looking much as you had it looking in Excel, just do:
The formatter will return String cells as-is, and for Numeric cells will apply the formatting rules on the style to the number of the cell