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.

Solution 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.

Solution 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

Comments

[lamCM]

Hi @Konstantin, thank you for your feedback. My mistake, it should return 3. With regards to the additional blank rows, may I have your advice on how to eliminate the blank rows, and to ensure that in the future, regardless of how many rows I add, it will give me the correct row count ? Thank you again

[Andreas]

@lamCM Select rows 5-7 in Excel, delete them, and save the workbook again. However, your code will still be susceptible to such issues, so it's better to not rely on getLastRowNum(). Iterate the rows and check if they have non-blank data. You could iterate backwards from getLastRowNum() until you find a non-blank row, if you just want to find the last value-row, but you may still have blank/missing rows in-between other rows, so it all depends on what you need.

[Konstantin Labun]

Another way to get a rows count is to iterate from the end until we get first non-blank row. And if the index of this row would be X, then return X + 1 (don't forget, X is zero based)

[lamCM]

@Konstantin, followed your advise and it works well ! Thanks a lot !! Really appreciate your effort !