How to get formatted number value for cell in Apache POI?

Question

I wanted to get the value of a Numeric cell as a simple string.

Suppose the cell type is numeric with a value of 90% . Now I can not use cell.getStringCellValue() since it will throw an exception. I also cannot use cell.getNumericCellValue() as it will return me .9 , not 90% .

I want to save in db, which is of type varchar2, so I want only the value in the string.

I cannot change the cell type in xls as my end user job, I have to handle this in the code itself.

Also, the formatter does not work well, as there may be different types of cells in xls ... dd: mm, dd: mm: ss, formula, etc.

All I want is that regardless of the type of cell I need to get its value as a simple string.

Answer 1

You can force the value to return as a string using the methods below

 HSSFDataFormatter hdf = new HSSFDataFormatter(); System.out.println (hdf.formatCellValue(mycell)); 

will return "90%"

The API for this method is in http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html#formatCellValue%28org.apache.poi.ss.usermodel.Cell%29

It works directly even with HSSFCell

it worked for me even when my cell is HSSFCell

I also tried this order - which works.

 HSSFCell cell1 = (HSSFCell) row1.getCell(2); HSSFDataFormatter hdf = new HSSFDataFormatter(); System.out.println ("formatted "+ hdf.formatCellValue(cell1)); 

Answer 2

Try

 cell.getRichStringCellValue ().getString(); 

Look at this example
Here is the doc