Why "cellIterator.hasNext()" is getting "TRUE" even though cell has empty value

Question

consider below screenshot of my excelSheet

Now consider below code, which will iterate every row then iterate every cell and then print cell value.

public void excelData2(int sheetNo) {
    Iterator<Row> rowIterator = workbook.getSheetAt(sheetNo).rowIterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            String cellValue = dataFormatter.formatCellValue(cell);
            System.out.print(cellValue + "\t");
        }
        System.out.println();
    }
}

Actually by looking at excel sheet we can see there are 2 rows and 1 column right!!.

So, the first while loop is iterating two times as expected :) , but inner while loop which is cellIterator is looping 26 times instead of 1 time even though next cell's has no value in it. Ideally when in 2nd iteration cellIterator.hasNext() should give false right? why it is giving true value till 26 times? anyone have idea?

Note: I don't want to put condition like isEmpty() or =="" etc.

Note2: I have another solution in my mind i can go with that. But i want to know why this is happening.

I haven't worked with an `Iterator` but it seems to me that `hasNext` would return true if another cell exists because an empty cell is still a cell. — The Head Rush, May 04 '19 at 10:51
Ohh.. But is there anyway to get only count of columns having data in it instead of putting conditions like `isEmpty()` or `==""` etc — Jagadeesh, May 04 '19 at 10:55
Why you wouldn't want to use the tool provided by the API to examine the contents of the cell. — The Head Rush, May 04 '19 at 10:56
Yeah, i mean in my excel sheet in between there will be empty values, hence i don't wish to end up like this `cell` is the end, hence this many **no of columns** what we have. — Jagadeesh, May 04 '19 at 11:00
It looks like the code you wrote prints all the cells in the sheet. If you want a column count, get a single row instead of iterating them all. — The Head Rush, May 04 '19 at 11:09
Even though for single `row` it has inbuilt class like `row.getLastCellNum();` which is returning 1024 columns. Of course it is returning last column count. Instead of that i want last column having the value in it — Jagadeesh, May 04 '19 at 11:13
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/192833/discussion-between-the-head-rush-and-jagadeesh). — The Head Rush, May 04 '19 at 13:12
please check this answer https://stackoverflow.com/questions/36304092/how-to-iterate-by-all-not-empty-rows-without-using-break-or-continue-statement — L.G, Feb 16 '23 at 20:00
please check this answer https://stackoverflow.com/questions/36304092/how-to-iterate-by-all-not-empty-rows-without-using-break-or-continue-statement — L.G, Feb 16 '23 at 20:01

score 3 · Answer 1 · answered May 04 '19 at 10:51

3

According to the docs of POI (used by LibreOffice):

org.apache.poi.xssf.streaming.SXSSFRow.CellIterator

returns all cells including empty cells

answered May 04 '19 at 10:51

RajmondX

405
2
9

score -1 · Answer 2 · edited Jun 18 '21 at 18:23

-1

You can use this and can skip the blank once:

if (nextCell.getCellType() == Cell.CELL_TYPE_BLANK)

edited Jun 18 '21 at 18:23

enzo

9,861
3
15
38

answered Jun 18 '21 at 11:42

pshetty

3
2

Why "cellIterator.hasNext()" is getting "TRUE" even though cell has empty value

2 Answers2