apache poi finding number of rows in a sheet

Question

I have been using these methods to count the number of rows in a sheet

getLastRowNum()

and

getPhysicalNumberOfRows()

They are working fine on two sheets of my workbook. But the third sheet containing 5 rows is returning 1 extra with both of the functions.

Sheet NumberofRows getLastRowNum() getPhysicalNumberOfRows()
1     9            9               10
2     56           56              57
3     5            4               5

What exactly is the difference in the working of the two functions and what can I do so I get reliable correct results?

score 12 · Answer 1 · edited Mar 01 '19 at 12:59

12

getLastRowNum()

the number of the last row contained in this sheet, start from zero

i.e Like an array it starts from 0 to n-1 if n is the number of rows.

getPhysicalNumberOfRows()

the number of physically defined rows in this sheet. Not include the empty row

So for the number of rows 10, getLastRowNum() will be 9, as it start from 0.
This is one of the reason that getPhysicalNumberOfRows() should be used instead of getLastRowNum() because if the number of rows is 1 in the sheet, getLastRowNum() would return 0 which cannot differentiate if there is 0 row or the only row is at position 0 while getPhysicalNumberOfRows() would return 1.

edited Mar 01 '19 at 12:59

alan9uo

1,011
1
11
17

answered Jan 08 '15 at 12:20

thepace

2,221
1
13
21

But like I mentioned, for the number of rows 9, getPhysicalNumberOfRows is giving me 10. And not in all cases is it adding 1 to the number. – tanvi Jan 08 '15 at 12:32
Can you check the start of the sheet, the first row; any blank row any unused row, etc or if possible share the xls. – thepace Jan 08 '15 at 12:36
I can't share the workbook, but it is actually xlsx. Would that change things? Also, by the documentation from apache poi, and as suggested by you, for numOfRows = 9, getPhysicalNumberOfRows() should return 9. But it is giving me 10. On a sheet with 5 rows, however, it is giving me 5. – tanvi Jan 08 '15 at 12:41
Check for hidden rows in between or if there is a header or you might have entered something in the last column. Clear everything and try. Might help. – thepace Jan 08 '15 at 14:24
Couldn't find anything. I even made a new workbook. Same results! – tanvi Jan 12 '15 at 03:49
Okk. Will try to execute it in my machine and check. – thepace Jan 12 '15 at 05:21
Could you share your apache-poi version and also the xls. – thepace Jan 12 '15 at 05:31
Can't share the xls. but poi version i am using is 3.10 – tanvi Jan 12 '15 at 09:39
Not the actual but the new workbook that has the issue. – thepace Jan 12 '15 at 14:35

score 3 · Answer 2 · answered Jul 28 '17 at 04:12

I found the built-in methods to be unreliable (inconsistently off by +/-1) so I rolled my own:

// returns index of next blank line
public static int getNextRow(Sheet sheet) {
    int rowIndex = 0;
    Row row = sheet.getRow(rowIndex);
    Cell c = null;
    try {
        c = row.getCell(0);
    } catch (Exception e) {
        return rowIndex;
    }
    while (!emptyCell(c)) {
        rowIndex++;
        row = sheet.getRow(rowIndex);
        if (row != null) {
                c = row.getCell(0);
        } else {
                break;
        }
    }
    return rowIndex;
}

apache poi finding number of rows in a sheet

2 Answers2