Reading string value from Excel with HSSF but it's double

Question

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()

Answer 1

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:

 // Create a formatter, do this once
 DataFormatter formatter = new DataFormatter(Locale.US);

 .....

 for(Cell cell : row) {
     CellReference ref = new CellReference(cell);
     // eg "The value of B12 is 12.4%"
     System.out.println("The value of " + ref.formatAsString() + " is " + formatter.formatCellValue(cell));
 }

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