xssf How to get anything as String

Question

I try to parse an excel file into XML using apache poi xssf. Now having a cell and not knowing what is in it I just want to get a String out of it. But when I use

cell.getStringCellValue()

it throws an exception, what is not very suprising since it is documented this way. So I build my way around that by checking weather it is a numeric or a text cell. But what to do with formula cells. They may contain numbers like

= A2 + B2

What gives me the sum (e.g. 4) or a reference to another text

= C2

what might refer to a text like "Hans".

How can I know what is really in my cell and how do I get a String out of it?

Answer 1

Excel stores some cells as strings, but most as numbers with special formatting rules applied to them. If you want to get the raw values, use a switch statement based on cell.getCellType() as some of the other answers have shown.

However, if what you want is a string of the cell, showing the same as what Excel would show, based on applying all the formatting rules on the cell + cell types, then 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, and giving you back a nicely formatted string at the end.