Java Spring Boot Read Excel with Apache POI — 2 Things to Know to Save Hours of Debugging

Issue 1 : Get Cell Value as String

Some solution on the internet will suggest these two method

cell.getStringCellValue()
cell.getNumericCellValue()

Then you might got the error of

Cannot get a STRING value from a NUMERIC cell

Then you might try to cast everything to String with setCellType or goes back to Excel and set every cell to ‘Text’.

Setting everything to ‘Text’ in Excel is not practical if your app is used by the public.

Unfortunately, the setCellType method is now deprecated.

setCellType(CellType) from the type Cell is deprecatedJava

Solution of Issue 1

Now read it all as string and then cast it later

DataFormatter formatter = new DataFormatter();
String currentValue = formatter.formatCellValue(cell);

// Then you can cast to anything you want later
Integer.parseInt(currentValue)
Float.parseFloat(currentValue)

Issue 2 : Iterator will skip blank cell

This can be bad when your data read in is mismatch with the index you expect.

Iterator<Row> rows = sheet.iterator();
Iterator<Cell> cellsInRow = currentRow.iterator();
while (cell.hasNext()) {}

This is also iterator implicitly

This will also SKIP THE BLANK CELL

for (Sheet sheet : wb ) {
    for (Row row : sheet) {
        for (Cell cell : row) {
            // Do something here
        }
    }
}

And this is NOT iterator

The simple double for loop we all familiar with

Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheet(SHEET);
int rowEnd = sheet.getLastRowNum();
for (int rowIndex = 1; rowIndex < rowEnd; rowIndex++) {
    Row row = sheet.getRow(rowIndex);
    for (int cellIndex = 0; cellIndex < lastColumn; cellIndex++) {
        Cell cell = row.getCell(cellIndex, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
    }
}

// Remark: inputStream come from file.getInputStream()

This one will go through every single cell in your work sheet.