getLastRowNum not returning correct number of rows

Question

I am having a small conundrum in using the getLastRowNum(). I am trying to get the number of rows with data from an Excel sheet. Supposing I have 3 rows, it should return in a print out statement stating 3 rows. My issue now is no matter how many rows I populate, it is returning me a certain fixed number. The following is excerpt of the code:

FileInputStream fis = new FileInputStream("C:\\Test Data\\Login.xlsx"); 
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sheet = wb.getSheet("Login");
System.out.println("No. of rows : " + sheet.getLastRowNum());

My Excel Sheet consist of the following

From the attached image, I should be getting row count of 4, but I am getting 6 instead.

Any advice is deeply appreciated. Thank you in advance.

Answer 1

Speaking about attached file, it should return row number of 3 because of the XSSFSheet.getLastRowNum() is zero-based. But if your receive a number of 6, it seems you have 3 additional blank rows in your sheet.

If you just open existing file and it contains blank rows, here is example how to clean it. But if you fill the table by yourself, it seems you need to check you code for places where this empty line could be created.

Answer 2

it should return 3, Check blank rows in your excel file and remove them

• click Find & Select
• click Go To Special
• Select Blanks and click OK
• Delete Sheet Rows