1

I've to iterate rows and add it to a list, but I'm getting total numbers of rows as total rows present in excel sheet i.e.,1001 rows but I have only 3 rows including headers, i have to stop the loop with the size of the row number.

my code was 

     Sheet sheet = workbook.getSheetAt(0);
     int maxNumOfCells = sheet.getRow(0).getLastCellNum();
     // The the maximum number of columns
     int totalRows = sheet.getLastRowNum(); 
     // or  sheet.getPhysicalNumberOfRows();

both are returning me total rows in the sheet than returning rows that have data alone.

It should return totalRows excluding blank rows.?

Syeda Samreen
  • 99
  • 1
  • 13
  • 1
    http://stackoverflow.com/questions/12217047/how-to-determine-empty-row – Deepika Rajani Apr 17 '15 at 09:59
  • @DeepikaRajani Isn't that a brute-force solution? There should be something simpler. Because if the OP needs to use that solution, he will have to check all the cells in all the rows. Performance will hit. – Aakash Apr 17 '15 at 10:04
  • Usually, a call to `getRow(n)` will return null if the row is empty. But it is not certain how excel handles empty cells/rows that might have been blanked but not deleted. A blank cell is not neccessarily empty, so it is better to check all the cells in all the rows. – Deepika Rajani Apr 17 '15 at 10:30

2 Answers2

1

I guess you are reading from an excel with POI. There is no shortcut to know if the entire row is empty or not. You will have to run the loop row wise then check cell values (columnwise) in order to get the actual number of rows that contain meaningful data.

sheet.getLastRowNum() will give you the loop limit in this case. Don't forget to handle the null pointers while reading the cells.

Samiran Saha
  • 13
  • 5
0

From the Apache POI Documentation:

getPhysicalNumberOfRows() - Returns the number of physically defined rows (NOT the number of rows in the sheet)

Example:

InputStream fs = new FileInputStream("test.xls");
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
sheet.getPhysicalNumberOfRows());
LittlePanda
  • 2,496
  • 1
  • 21
  • 33
  • This returns also empty rows in some cases (empirically verified), so it's not the solution. – simon May 03 '18 at 07:55
  • getPhysicalNumberOfRows() does NOT guarantee exclusion of null rows, rows with empty cells. – Pawan Jan 16 '20 at 12:45