CODEWITHSUNDEEP
javaexcelapache-poipoi-hssf
Ahmad
Ahmad

Reputation: 13426

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

I'm using the Apache 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.

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

Upvotes: 57

Views: 190517

Answers (8)

Bharati Mane
Bharati Mane

Reputation: 1

Same issue i had where i used getPhysicalNumberOfRows() method to get actual data. but my uploading sheet was containing blank rows too then it causing problem and missing last some rows.

I have used sheet.getLastRowNum() +1 for outer loop for rows count and inner i used getPhysicalNumberOfRows().

It red whole and working fine.

Upvotes: 0

Steven Shiqi Wang
Steven Shiqi Wang

Reputation: 31

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.

Upvotes: 0

Shadab Khan
Shadab Khan

Reputation: 49

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;

Upvotes: 1

Singh Ss
Singh Ss

Reputation: 11

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

Upvotes: -1

MatthijsM
MatthijsM

Reputation: 91

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.

Upvotes: 9

Abhishek Singh
Abhishek Singh

Reputation: 9765

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

  1. 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)

  2. Option 2 just gives you the index of last column. Hence done 'getLastCellNum()'

Upvotes: 5

JavaGeek
JavaGeek

Reputation: 21

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

Upvotes: 2

makasprzak
makasprzak

Reputation: 5220

Try Sheet.getPhysicalNumberOfRows()

Upvotes: 106

Related Questions