Get actual cell value of excel cell with POI witho

Question

I catch my cells from an .xls file like this:

cell.getStringCellValue();

But since some of the cells are numeric, I have to do this instead:

                try
                {
                    cells[colIx] = cell.getStringCellValue();
                } catch (IllegalStateException e)
                {
                    cells[colIx] = String.valueOf(cell.getNumericCellValue());
                }

since it's getting a double and then converts it to a string this results in some unwanted operations like:

• 1 converts into 1.0 (not that kind of a big problem)
• 16711680 converts to 1.671168E7

How do I solve this and get the actual cell value instead of some converted numbers? Also, all of the cells are defined as default in excel

Answer 1

try using cell.getCellType() as you have

1. numeric
2. date
3. numeric in scientific notation

Answer 2

You can use Apache POI DataFormatter's formatCellValue(Cell cell) method as it returns the formatted value of a cell as a String regardless of the cell type.

According to DataFormatter doc -

DataFormatter contains methods for formatting the value stored in an Cell. This can be useful for reports and GUI presentations when you need to display data exactly as it appears in Excel. Supported formats include currency, SSN, percentages, decimals, dates, phone numbers, zip codes, etc.

Sample code

DataFormatter dataFormatter= new DataFormatter();

// Inside loop
String cellValueStr = dataFormatter.formatCellValue(cell);