How to get row count in an Excel file using POI library?
JavaExcelApache PoiPoi HssfJava Problem Overview
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 ?
Java Solutions
Solution 1 - Java
Solution 2 - Java
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.
Solution 3 - Java
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;
}
Solution 4 - Java
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()'
Solution 5 - Java
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;
Solution 6 - Java
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.
Solution 7 - Java
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++;
}