Apache POI. Using row.getLastCellNum() to retrieve number of non blank cells

Question

I'm trying to get non-blank number of cells in a row.

| 1  | 2  | 3 |  |  |  |
| 55 | 33 |   |  |  |  |

with (int) row.getLastCellNum();

But for both rows I'm getting an int that is bigger than the number of cells that actually have values in my sheet. What could be the reason for this? Can cell formatting and/or styling be somehow responsible?

I've also noticed that if I delete values from excel sheet (manually, not from code), poi still considers them as non-blank(null?) and includes them in total count.

UPD: after a bit more research I've found that if I add value to a cell and then delete it, then POI still considers it non-empty. Is there any way to properly clear cell contents in excel sheet without deleting entire row/column?

Did you read the Apache POI docs on iterating over rows and cells, especially the bits on blank vs null cells? — Gagravarr
Yes, I have. Although the explanation about blank vs. null cell is very vague there (at least for me it is), hence came the question. — SergioLeone
It just reflects the way the Excel file format works! Null cells have never been used or styled. Blank cells may have styling, formatting, borders etc, or may have used to and Excel was just too lazy to remove them later... — Gagravarr
Yes, I can see that now. Thanks for the explanation @Gagravarr — SergioLeone

SergioLeone SergioLeone · Accepted Answer · 2017-09-05T08:31:51

For now I've solved it like this:

Iterator<Cell> cells = row.cellIterator();
                while (cells.hasNext()) {
                    if (formatCellToString(cells.next()).equals("")) {
                        lastCellNumber = lastCellNumber - 1;
                    }
                }

Would be nice if anyone has any better solution.

Apache POI. Using row.getLastCellNum() to retrieve number of non blank cells

1 Answers