How to get row count in an Excel file using POI library?

Question

Guys I'm currently using the POI 3.9 library to work with excel files. I know of the getLastRowNum() function, which returns a number of rows in an Excel file.

The only problem is getLastRowNum() returns a number with the count starting from 0.

So if an Excel file uses the first 3 rows, getLastRowNum() returns 2. If an Excel file has just 1 row, getLastRowNum() returns 0.

The problem occurs when the Excel file is completely empty. getLastRowNum() still returns 0, so I cannot determine if the Excel file has 1 row of data, or if its empty.

So how can I detect if an Excel file is empty or not ?

score 103 · Accepted Answer · answered Apr 26 '13 at 11:28

103

Try Sheet.getPhysicalNumberOfRows()

answered Apr 26 '13 at 11:28

makasprzak

5,082
3
30
49

6

+1 Marcias. Just that if one deletes cells by just pressing Delete, then seems like rows still exists physically and old count is returned. – anujin Sep 18 '13 at 15:03
2

Well, I think that if you'd like to count rows that actually have some non empty value, you'll need to iterate over it by yourself. I'm not sure, but I understand that this method returns number of rows that have ever been kind of "declared". – makasprzak Sep 19 '13 at 09:20
2

WARNING: If the sheet has blank rows then getPhysicalNumberOfRows does not include them in the count it returns. However, getLastRowNum also does exactly what it says on the tin. So to iterate over all the rows you need to use getLastRowNum+1 as the upper bound in your iteration loop - taking care to handle rows which may not exist. – Tony Eastwood Jul 15 '16 at 13:04

score 8 · Answer 2 · edited Nov 23 '17 at 19:44

Since Sheet.getPhysicalNumberOfRows() does not count empty rows and Sheet.getLastRowNum() returns 0 both if there is one row or no rows, I use a combination of the two methods to accurately calculate the total number of rows.

int rowTotal = sheet.getLastRowNum();

if ((rowTotal > 0) || (sheet.getPhysicalNumberOfRows() > 0)) {
    rowTotal++;
}

Note: This will treat a spreadsheet with one empty row as having none but for most purposes this is probably okay.

score 3 · Answer 3 · answered Aug 28 '15 at 10:36

There are two Things you can do

use

int noOfColumns = sh.getRow(0).getPhysicalNumberOfCells();

or

int noOfColumns = sh.getRow(0).getLastCellNum();

There is a fine difference between them

Option 1 gives the no of columns which are actually filled with contents(If the 2nd column of 10 columns is not filled you will get 9)
Option 2 just gives you the index of last column. Hence done 'getLastCellNum()'

score 2 · Answer 4 · edited Sep 24 '13 at 10:41

If you do a check

if
(getLastRowNum()<1){
 res="Sheet Cannot be empty";
return
}

This will make sure you have at least one row with data except header. Below is my program which works fine. Excel file has three columns ie. ID, NAME , LASTNAME

XSSFWorkbook workbook = new XSSFWorkbook(inputstream);
        XSSFSheet sheet = workbook.getSheetAt(0);
        Row header = sheet.getRow(0);
        int n = header.getLastCellNum();
        String header1 = header.getCell(0).getStringCellValue();
        String header2 = header.getCell(1).getStringCellValue();
        String header3 = header.getCell(2).getStringCellValue();
        if (header1.equals("ID") && header2.equals("NAME")
                && header3.equals("LASTNAME")) {
            if(sheet.getLastRowNum()<1){
                System.out.println("Sheet empty");
                         return;
            }   
                        iterate over sheet to get cell values
        }else{
                          SOP("invalid format");
                          return;
                          }

The [docs](https://poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html#getLastRowNum) note that it's zero based. So `getLastRowNum()` would return 0 for when there's no columns or when there's only 1 (header in your case). So for checking if there's any rows `getPhysicalNumberOfRows()` should also be used. — jl., Mar 10 '16 at 09:09
also the [HSSF sheet docs](https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#getLastRowNum()) have some more content regarding this subject. — jl., Mar 10 '16 at 09:17

score 0 · Answer 5 · answered Mar 05 '20 at 07:58

0

getLastRowNum() return index of last row.

So if you wants to know total number of row = getLastRowNum() +1.

I hope this will work.

int rowTotal = sheet.getLastRowNum() +1;

answered Mar 05 '20 at 07:58

Shadab Khan

41
3
5

score 0 · Answer 6 · edited Aug 06 '20 at 16:18

0

Sheet.getPhysicalNumberOfRows() does not involve some empty rows. If you want to loop for all rows, do not use this to know the loop size.

edited Aug 06 '20 at 16:18

Shalu T D

3,921
2
26
37

answered Aug 06 '20 at 15:52

Steven Shiqi Wang

11
1

score -1 · Answer 7 · edited Jan 31 '19 at 07:30

To find last data row, in case you created table template in excel where it is filled partially or in between rows are empty. Logic:

int count = 0;
int emptyrow=0;
int irow=0;
while (rowIterator.hasNext()) {
    row = (Row) rowIterator.next();
    if (count != 0 && !checkIfRowIsEmpty(row)) { }
    else{
        if(count!=0 && emptyrow==irow){
            emptyrow++;
        }else{
            emptyrow=0;
            irow=0;
        }
    }
    if(emptyrow>0){
        irow++;
    }
    if(emptyrow>3){
        break;
    }
    count++;
}

How to get row count in an Excel file using POI library?

7 Answers7

Linked

Related