Apache POI - Handling Empty fields

Question

I am new to Apache POI, and I need to write to an excel file based on the contents of my DB. However, when there is an empty/null field in the DB, a Null Pointer Exception is thrown for that particular field. I've been reading about Missing Cell Policies, is this the best way to address this? I have omitted a lot of code for brevity, but the below is the key part. For example if actualAmountReturned is empty in the DB, a NPE is thrown.

        List<CaseData> cases = (List<CaseData>) model.get("cases");
    int rowCount = 1;
    for (CaseData pieCase : cases) {
        Row userRow = sheet.createRow(rowCount++);
        userRow.createCell(0).setCellValue(pieCase.getCaseId());
        userRow.createCell(1).setCellValue(pieCase.getAcknowledgementReceivedDate());
        userRow.createCell(2).setCellValue(pieCase.getActualAmountReturned());
}

Check if the value is defined, and only create+set the cell if it is? — Gagravarr
What data type does pieCase.getActualAmountReturned() return? — Axel Richter
@Gagravarr these are optional fields, so sometimes are present are sometimes aren't. — Coder

Axel Richter Axel Richter · Accepted Answer · 2019-11-18T17:28:34

There are only two Cell.setCellValue methods which will throw NPE when the given value is null. This are the two only ones which take primitive data types as value parameters but you put the appropriate object wrapper classes so unboxing is necessary. This are Cell.setCellValue(boolean value) and Cell.setCellValue(double value). This is because unboxing from Boolean to boolean and Double to double fails for null.

For example

Boolean value = null;
cell.setCellValue(value); //NPE because unboxing `Boolean` to `boolean` fails

and

Double value = null;
cell.setCellValue(value); //NPE because unboxing `Double` to `double` fails

All other Cell.setCellValue methods which take non primitive data classes as value parameters simple create blank cells when null is given.

So if pieCase.getActualAmountReturned() returns a Double, then

...
if (pieCase.getActualAmountReturned() == null) {
 userRow.createCell(2).setBlank();
} else {
 userRow.createCell(2).setCellValue(pieCase.getActualAmountReturned());
}
...

Apache POI - Handling Empty fields

1 Answers