Getting NPE while parse empty cells in apache poi?

Question

protected List<List<XSSFCell>> read2DTable(XSSFSheet sheet) {
    LOGGER.debug("read2DTable START");
    List<XSSFCell> headerCells = readTableHeader(sheet);
    List<List<XSSFCell>>  allRows = IntStream.range(sheet.getFirstRowNum(), sheet.getLastRowNum()+1)
                .mapToObj(i -> sheet.getRow(i))
                .map(row -> headerCells.stream()
                                       .map(col -> row.getCell(col.getColumnIndex()))
                                       .collect(Collectors.toList()))
                .filter(c -> StringUtils.isNotEmpty(c.toString()))
                .collect(Collectors.toList());

    LOGGER.info("rows read: {}, columns read: {}", allRows, headerCells.size());
    LOGGER.info("read2DTable END");

    return allRows;
}

This is the error:

 Caused by: java.lang.NullPointerException
        at com.netcracker.solutions.gtdc.cim.importdata.processing.excel.ExcelProcessor.lambda$null$9(ExcelProcessor.java:162)
        at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
        at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1374)
        at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
        at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
        at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:708)
        at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
        at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:499)
        at com.netcracker.solutions.gtdc.cim.importdata.processing.excel.ExcelProcessor.lambda$read2DTable$10(ExcelProcessor.java:162)

that happens in here :

     .map(col -> row.getCell(col.getColumnIndex()))
    .collect(Collectors.toList()))
.filter(c ->StringUtils.isNotEmpty(c.toString()))
.collect(Collectors.toList());

Downvoting as it's part of the job to find and fix the cause. — yegodm, Jan 10 '18 at 09:48

Boris Pavlović · Accepted Answer · 2018-01-10T08:36:16.643

1

Try replacing:

StringUtils.isNotEmpty(c.toString())

with:

StringUtils.isNotEmpty(null == c ? "" : c.toString())

or avoiding using 3rd party libraries and a bit shorter (as suggested by Holger):

c!=null && !c.toString().isEmpty()

edited Jan 10 '18 at 08:36

answered Jan 09 '18 at 08:13

Boris Pavlović

63,078
28
122
148

1

You mean `StringUtils.isNotEmpty(c)` ;) – Joop Eggen Jan 09 '18 at 10:27
1

I am afraid I erred; c is not of type String - sorry; I was led astray by isNotEmpty handling null. – Joop Eggen Jan 09 '18 at 10:44
1

When you use `null == c ? "" : c.toString()`, there is no need for that 3rd party library method anymore, you could invoke `isEmpty()` on the now-non-`null` string, i.e. `! (null == c ? "" : c.toString()).isEmpty()`, but of course, it’s not necessary to substitute the string with an empty string just to test it for emptiness afterwards. So the expression simplifies to `c!=null && !c.toString().isEmpty()`. – Holger Jan 10 '18 at 08:34
2

But looking at the surrounding context, `c` is a `List`, so even if it is empty, its string representation will be `[]`, i.e. never empty. Also, it’s the result of `.collect(Collectors.toList())` of the inner stream operation in the preceding `map` step, thus it’s never `null`. So it should be `.filter(l -> !l.isEmpty())` instead and is not the actual cause of the `NullPointerException`. My best guess is, `row` is `null` two lines earlier. – Holger Jan 10 '18 at 08:41
@Holger please convert your comment to an answer. It should be accepted and eligible for upvoting – Boris Pavlović Jan 10 '18 at 08:43
@BorisPavlović I added an answer – Holger Jan 10 '18 at 09:17

score 0 · Answer 2 · answered Jan 09 '18 at 10:39

Filter all empty cells:

List<List<XSSFCell>>  allRows = IntStream.range(sheet.getFirstRowNum(), sheet.getLastRowNum()+1)
        .mapToObj(i -> sheet.getRow(i))
        .map(row -> headerCells.stream()
                .map(col -> row.getCell(col.getColumnIndex()))
                .collect(Collectors.toList()))
        .filter(Obejcts::nonNull)
        .filter(c -> StringUtils.isNotEmpty(c.toString()))
        .collect(Collectors.toList());

score 0 · Answer 3 · answered Jan 10 '18 at 09:13

One potential candidate for being null is the result of getRow, acquired in .mapToObj(i -> sheet.getRow(i)), but first dereferenced in the .map(col -> row.getCell(col.getColumnIndex())) line.

Also note that the last filter is questionable, as you are using a utility function for testing a potentially null string for emptyness, but the result of List.toString() is never null and never empty, as the representation of an empty List will be "[]".

The fixed code would look like:

List<List<XSSFCell>> allRows
  =IntStream.range(sheet.getFirstRowNum(), sheet.getLastRowNum()+1)
            .mapToObj(i -> sheet.getRow(i)) // you may also use .mapToObj(sheet::getRow)
            .filter(Objects::nonNull)
            .map(row -> headerCells.stream()
                                   .map(col -> row.getCell(col.getColumnIndex()))
                                   .collect(Collectors.toList()))
            .filter(list -> !list.isEmpty())
            .collect(Collectors.toList());

Note that the inner lists still can contain null elements, depending on the configured MissingCellPolicy. Configuring the workbook to always get blank cells, is preferable to removing null elements from the row lists, to keep the list indices of all row lists aligned.

Getting NPE while parse empty cells in apache poi?

3 Answers3