userimage

Need to read numeric strings in Excel cells as string usings Apache POI?

My excel sheet contain the mixture of numeric and text values. When I am reading those Apache POI, it tells that A2(cell contain numeric data) is in numeric cell format.The problem is that the value in A2 can be 2 or 2.0 (and I want to be able to distinguish them) so I can"t just use .toString().

Any suggestion apreciable.

userimage

I had the same problem and after analysis here is the final workable dynamic solution for you.


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

}

Answer is