Java – xssf how to get any strings

I try to use Apache poi xssf to parse Excel files into XML

cell.getStringCellValue()

It throws an exception because it records in this way, so it's not surprising So I build my way by checking the weather, which is a number or text cell But how to deal with formula cells They may contain numbers

= A2 + B2

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

= C2

What might mean words like "Hans"

How do I know what's really in my cell and how do I get strings from it?

Solution

Excel stores some cells as strings, but most are applied to them as numbers with special formatting rules If you want to get the original value, use cell. Based The switch statement of getcelltype (), as shown in other answers

However, if what you want is a string of a cell, which will be displayed the same as what excel will display. Based on applying all formatting rules to the cell type, Apache POI has a class that can do this - dataformatter

What you need to do is:

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);

No matter what the cell type is, dataformatter will format it for you as much as possible, use the rules applied in Excel, and provide you with a well formatted string at the end