Apache POI rows number

7
votes

I am using Apache POI java and want to get the total number of rows which are not empty. I successfully processed a whole row with all its columns. Now I am assuming that I get an excel sheet with multiple rows and not a single row...so how to go about that? I was thinking of getting total number of rows (int n) and then loop until i<=n but not sure.

Suggestions are most welcome :)

Note: Apache POI version is 3.8. I am not dealing with Xlsx format...only xls.

Yes I tried this code but got 20 in return....which is not possible given I have only 5 rows 

FileInputStream fileInputStream = new FileInputStream("COD.xls");
            HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
            HSSFSheet worksheet = workbook.getSheet("COD");
            HSSFRow row1 = worksheet.getRow(3);
            Iterator rows = worksheet.rowIterator(); 
            int noOfRows = 0;
            while( rows.hasNext() ) {
                HSSFRow row = (HSSFRow) rows.next();
                noOfRows++;                
            }
            System.out.println("Number of Rows: " + noOfRows);
5
javaapache-poi
What exactly have you tried and what is not working? Can you provide some code? – nansen
It is not nice but I guess that's how it is done. You can use the HSSFSheet directly in a foreach loop since it implements Iterable<Row>. They don't call it Horrible Spread Sheet Format (HSSF) for nothing :( Maybe you are getting 20 instead of the expected 5 rows because you have rows with null values. You would have to iterate over the cells as well and skip all rows with null values only. – nansen
I think I solved it...because I have drop down menus in excel sheet so somewhere in the sheet those lists existed. Now it is working and displaying right row numbers – sys_debug

5 Answers

7
votes
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
    if ((tempRow = sheet.getRow(i)) != null) {
           //Your Code Here
    }
}
5
votes

The problem is that POI considers empty rows as physical rows. This happens at times in Excel and while they are not visible to the eye, the rows certainly exist.

If you were to open you Excel sheet and select everything below your data, then delete it (i know it is empty looking, but do it anyway), POI will return the right number.

1
votes

You may want to getPhysicalNumberOfRows() other than getLastRowNum()?

0
votes

You can iterate over the rows which are not empty using this:

Iterator<Row> rowIterator = sheet.rowIterator();
      while (rowIterator.hasNext()) {
        Row row = (Row) rowIterator.next();
        // Your code here
      }

Thanks

0
votes
worksheet.getLastRownNum() // *base index 0*

This method will give you the last row number where you might have fill the row, even if you have filled 5 rows, there can be cases that you might have filled some spaces in the remaining 15 rows or at the 21st row because of which it is giving last row number as 20.

There can be the cases that in every 5th row you enters data (starting from 1), then your 5th entry will be in 21st row, so again, if you use this method, you will get 20 in result.