Apache POI rows number

问题:

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);

回答1:

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

回答2:

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.

回答3:

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

回答4:

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

回答5:

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.