Apache POI Line Number

Question

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

Suggestions are welcome :)

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

Yes, I tried this code, but got 20 in response .... which is impossible, I have only 5 lines

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

Answer 1

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

Answer 2

The problem is that the POI treats empty lines as physical lines. This happens sometimes in Excel, and although they are not visible to the eye, lines certainly exist.

If you need to open an Excel worksheet and select everything below your data and then delete it (I know that it is empty, but still), the POI will return the correct number.

Answer 3

You might need getPhysicalNumberOfRows () other than getLastRowNum ()?

Answer 4

You can iterate over strings that are not empty using this:

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

thanks

Answer 5

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

This method will give you the number of the last line where you could fill in the line, even if you filled in 5 lines, there may be times when you could fill in the blanks in the remaining 15 lines or in the 21st line, because of which it gives the number of the last lines like 20.

There may be times when you enter data in every fifth row (starting from 1), then your fifth record will be in the 21st row, so if you use this method, you will get 20 results.