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?

Accepted Answer

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.

xssf How to get anything as String

Solution

Problem