Apache POI-HSSF: Getting Decimal Instead of Text String

Question

I am using Apache POI-HSSF for working with Excel files.

I have a cell in my spreadsheet that looks like "115". I verified that it is formatted as "Text" (Format Cells -> Text).

However, when I read it in as row.getCell(0).toString()

I get this string: "115.0"

This is incorrect. I should be getting "115" since it's explicitly formatted as Text. How can I get the desired result? The cell can be anything, numbers or characters, and I expect the same string as in the cell. Thanks

score 2 · Answer 1 · answered Apr 16 '15 at 02:17

You should be calling the HSSFCell.getCellType() method to determine its type. Here's a method that handles cells of type String or Numeric. (You can easily add other types.) The format used for numbers will be a valid format, but won't necessarily match that of the SpreadSheet. (That's covered below.)

public static String getCellStringValue(final HSSFCell cell) {
    int cellType = cell.getCellType();
    String value;
    if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
        // Locale is optional here
        DataFormatter dataFormatter = new DataFormatter(Locale.US);
        value = dataFormatter.formatCellValue(cell);
    } else {
        // HSSFCell.CELL_TYPE_STRING
        value = cell.getStringCellValue();
    } // more cell types are possible. Add whatever you need.
    return value;
}

That code won't necessarily format the number as it appears in Excel. If you need the format to exactly match the spreadsheet format, you can get a formatter from the cell itself. To do this, you can use your DataFormatter instance to create a Format instance:

public static String getCellStringValue(final HSSFCell cell) {
    int cellType = cell.getCellType();
    String value;
    if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
        // Locale is optional here
        DataFormatter dataFormatter = new DataFormatter(Locale.US);
        Format format = dataFormatter.createFormat(cell);
        value = format.format(cell.getNumericCellValue());
    } else {
        // HSSFCell.CELL_TYPE_STRING
        value = cell.getStringCellValue();
    } // more cell types are possible. Add whatever you need.
    return value;
}

score 2 · Accepted Answer · answered Jun 04 '11 at 06:05

Formatted as text does not mean stored as text, they're different. Excel has stored your cell as a number, and when you ask POI for the cell you get a numeric cell back.

If you ask the cell you get back what type it is, you'll discover it's of type CELL_TYPE_NUMERIC and not CELL_TYPE_STRING

What you'll likely want to do is use the DataFormatter class to have your cell formatted as per Excel. It'll then look like you expect. (As will cells formatted as currency, percentages etc too)

Apache POI-HSSF: Getting Decimal Instead of Text String

2 Answers2