java - Get the cell value as how it was presented in excel

Question

I am currently working on a project that reads an excel file using Apache POI.

My task seems to be simple, I just need to get the cell value as it was display in the excel file. I am aware of performing a switch statement based on the cell type of a cell. But if the data is something like

9,000.00

POI gives me 9000.0 when I do getNumericCellValue(). When I force the cell to be a string type and do getStringCellValue() it then gives me 9000. What I need is the data as how it was presented in excel.

I found some post telling to use DataFormat class but as per my understanding, it requires your code to be aware of the format that the cell has. In my case, I am not aware of the format that the cell might have.

So, how can I retrieve the cell value as how it was presented in excel?

Answer 1

Excel stores some cells as strings, but most as numbers with special formatting rules applied to them. What you'll need to do is have those formatting rules run against the numeric cells, to produce strings that look like they do in Excel.

Luckily, Apache POI has a class to do just that - DataFormatter

All you need to do is something like:

 Workbook wb = WorkbookFactory.create(new File("myfile.xls"));
 DataFormatter df = new DataFormatter();

 Sheet s = wb.getSheetAt(0);
 Row r1 = s.getRow(0);
 Cell cA1 = r1.getCell(0);

 String asItLooksInExcel = df.formatCellValue(cA1);

Doesn't matter what the cell type is, DataFormatter will format it as best it can for you, using the rules applied in Excel