Getting exception while trying to export and color rows in excel using Apache POI

Question

So I want to color rows when exporting to Excel but I'm getting a NullPointerException.

This is my code:

HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex())

for (int i = 3; i < hssfSheet.getLastRowNum(); i++) {
    for (int j = 0; j < hssfSheet.getRow(i).getPhysicalNumberOfCells(); j++) {
        hssfSheet.getRow(i).getCell(j).setCellStyle(cellStyle);
    }              
}

So I noticed when I put a really small numbers of rows (like 3 or 4) it works fine, as soon as I put more, it giving me the exception. I have at least 200 rows, it depends on a filter that the user selects. How to fix this?

Some cells between 0 and getPhysicalNumberOfCells could be empty — Scary Wombat, Jun 04 '21 at 07:03
@ScaryWombat okay, then how to color that row if it has empty cells? — Nem Jov, Jun 04 '21 at 07:09
A quick search finds https://stackoverflow.com/a/6680841/2310289 — Scary Wombat, Jun 04 '21 at 07:11

Matt · Answer 1 · 2021-06-04T08:23:37.630

The problem is because HSSFSheet#getRow(int) and Row#getCell(int) return null when the row/cell is undefined. Therefore, the a consecutive calls .getCell(...) or .setCellStyle(...) throw a NPE. You can insert a NULL-check to only loop over the cells if the row is defined.

Additionally, you should use getLastCellNum() instead of getPhysicalNumberOfCells() to loop over the cells. Don't forget to use <= as both sides are zero-based.

If you want to color empty cells as well, you can use Row#getCell(int, Row.MissingCellPolicy) to create the cells on the fly:

for (int i = 3; i <= hssfSheet.getLastRowNum(); i++) {
    HSSFRow row = hssfSheet.getRow(i);
    if (row != null) {
        for (int j = 0; j < row.getLastCellNum(); j++) {
            HSSFCell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            cell.setCellStyle(cellStyle);
        }
    }
}

If empty cells should not be colored, you can use the following snippet:

for (int i = 3; i <= hssfSheet.getLastRowNum(); i++) {
    HSSFRow row = hssfSheet.getRow(i);
    if (row != null) {
        for (Cell cell : row) {
            cell.setCellStyle(cellStyle);
        }
    }
}

Getting exception while trying to export and color rows in excel using Apache POI

1 Answers1