CODEWITHSUNDEEP
javaexcelapache-poi
tanvi
tanvi

Reputation: 997

apache poi finding number of rows in a sheet

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?

Upvotes: 10

Views: 14432

Answers (2)

thepace
thepace

Reputation: 2221

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.

Upvotes: 12

Stunner
Stunner

Reputation: 12194

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;
}

Upvotes: 3

Related Questions