Count zero line in Excel file using Apache POI

Question

Does Apache POI provide any function for us to count the number of non-zero rows in an Excel file sheet?
The first time I have an Excel worksheet with 10 rows of data, the worksheet.getPhysicalNumberOfRows () function returns the exact number (10). But after that I delete 3 lines, then this function still gets 10 lines. Perhaps the total number of lines was cached anywhere by the POI. What does getPhysicalNumberOfRows () mean? As the API described it: "Returns the number of physically defined rows (NOT the number of rows per sheet)", but I do not understand what “physically defined” means. Can you help me in this matter?
Thank you very much!

Answer 1

If you delete rows using the .removeRow worksheet (row row), then the physical number of rows should be 7.

POI uses a map to store sheet lines. This card is a physical part. See http://www.google.com/codesearch/p?hl=de#WXzbfAF-tQc/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java

Regarding logically null strings, try

int notNullCount = 0; Sheet sheet = wb.getSheetAt(0); for (Row row : sheet) { for (Cell cell : row) { if (cell.getCellType() != Cell.CELL_TYPE_BLANK) { if (cell.getCellType() != Cell.CELL_TYPE_STRING || cell.getStringCellValue().length > 0) { notNullCount++; break; } } } } 

Answer 2

If you look at the POI 3.8 beta 3 code, you will see that deleting a row should also delete its entry. Thus, the number of physical lines in the file should also decrease.

I suggest you try a newer version of POI

To count the number of non-empty lines in a file, do something like Amadeus and loop over the lines in the sheet and check if they have cells.

Answer 3

If you delete manually, make sure that you use the delete row, and not just delete the data in the cells, then it will return the correct value.

Answer 4

I had the same problem. If I delete the rows manually, the number of rows will not decrease anyway when I checked the use of sheet.getPhysicalNumberOfRows ().

When I deeply dealt with this problem, I found the exact problem. I had an email column in my row, and when I entered an email address, MS Office automatically detects this as an email address. And when I delete this entire line manually, the cell on which the email address was saved still has a value of "(it will not be visible, but I found that the value is initialized when I read it through java). Since this the cell has a non-empty value (""), this entire line is declared (sort of), and the number of lines increases.

The funny thing is that when I do not enter the email address and just enter some line and then delete the line, the cell does not receive initialization and actually ROWCOUNT GOT DECREASED . This is what I found as a result of my problem.

Finally, I decided that not only adding a zero check for the cells, but also

if(cell != "")

Hope this can be helpful for you.

Answer 5

We can write our own method that will ignore empty lines to give the number of lines. We can probably make some assumptions based on the requirements. For example, in my case, a row can be considered empty if its first column value is empty and the counter is needed only until the first empty row.

Therefore, the following snippet may be useful:

  public int getNonBlankRowCount(String sheetName){ int rowCount = 0; int index = workbook.getSheetIndex(sheetName); if(index==-1){ rowCount = -1; return rowCount; }else{ sheet = workbook.getSheetAt(index); Iterator<Row> rowIterator = sheet.rowIterator(); rowCount = 0; while (rowIterator.hasNext()) { Row row = (Row) rowIterator.next(); cell = (HSSFCell) row.getCell(0); String cellValue = cell.getStringCellValue(); if (cellValue.isEmpty()) { break; } rowCount++; } return rowCount; } }