How can I read numeric strings in Excel cells as string (not numbers)?

Question

I had same problem. I did cell.setCellType(Cell.CELL_TYPE_STRING); before reading the string value, which solved the problem regardless of how the user formatted the cell.

I don't think we had this class back when you asked the question, but today there is an easy answer.

What you want to do is use the DataFormatter class. You pass this a cell, and it does its best to return you a string containing what Excel would show you for that cell. If you pass it a string cell, you'll get the string back. If you pass it a numeric cell with formatting rules applied, it will format the number based on them and give you the string back.

For your case, I'd assume that the numeric cells have an integer formatting rule applied to them. If you ask DataFormatter to format those cells, it'll give you back a string with the integer string in it.

Also, note that lots of people suggest doing cell.setCellType(Cell.CELL_TYPE_STRING), but the Apache POI JavaDocs quite clearly state that you shouldn't do this! Doing the setCellType call will loose formatting, as the javadocs explain the only way to convert to a String with formatting remaining is to use the DataFormatter class.

A simple example of using this class:

DataFormatter dataFormatter = new DataFormatter();
String formattedCellStr = dataFormatter.formatCellValue(cell);

The below code worked for me for any type of cell.

InputStream inp =getClass().getResourceAsStream("filename.xls"));
Workbook wb = WorkbookFactory.create(inp);
DataFormatter objDefaultFormat = new DataFormatter();
FormulaEvaluator objFormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) wb);

Sheet sheet= wb.getSheetAt(0);
Iterator<Row> objIterator = sheet.rowIterator();

while(objIterator.hasNext()){

    Row row = objIterator.next();
    Cell cellValue = row.getCell(0);
    objFormulaEvaluator.evaluate(cellValue); // This will evaluate the cell, And any type of cell will return string value
    String cellValueStr = objDefaultFormat.formatCellValue(cellValue,objFormulaEvaluator);

}

I would recommend the following approach when modifying cell's type is undesirable:

if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
    String str = NumberToTextConverter.toText(cell.getNumericCellValue())
}

NumberToTextConverter can correctly convert double value to a text using Excel's rules without precision loss.

As already mentioned in the Poi's JavaDocs (https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/Cell.html#setCellType%28int%29) don't use:

cell.setCellType(Cell.CELL_TYPE_STRING);

but use:

DataFormatter df = new DataFormatter();
String value = df.formatCellValue(cell);

More examples on http://massapi.com/class/da/DataFormatter.html

Yes, this works perfectly

recommended:

        DataFormatter dataFormatter = new DataFormatter();
        String value = dataFormatter.formatCellValue(cell);

old:

cell.setCellType(Cell.CELL_TYPE_STRING);

even if you have a problem with retrieving a value from cell having formula, still this works.