使用Apache POI在Excel文件中计算非空行

Does Apache POI provide any function for us to count the number of "not-null" rows in a sheet of an Excel file?
At the first time, I have an Excel sheet with 10 data rows, the function worksheet.getPhysicalNumberOfRows() returns the exact number (10). But after that, I delete 3 rows, then that function still gets 10 rows. Maybe the total number of rows was cached anywhere by POI. What does getPhysicalNumberOfRows() mean? As its API described: "Returns the number of physically defined rows (NOT the number of rows in the sheet)", but I do not understand what "physically defined" mean. Can you help me on this issue?
Thank you so much!

Apache POI是否为我们提供了计算Excel文件中“非空”行数的任何功能？第一次，我有一个包含10个数据行的Excel工作表，函数worksheet.getPhysicalNumberOfRows（）返回确切的数字（10）。但在那之后，我删除3行，然后该功能仍然有10行。也许POI总是在任何地方缓存行总数。 getPhysicalNumberOfRows（）是什么意思？正如其描述的API：“返回物理定义的行数（不是表单中的行数）”，但我不明白“物理定义”的含义。你能帮我解决这个问题吗？非常感谢！

5 个解决方案

#1

If you delete the rows via worksheet.removeRow(Row row), then the physical row count should be 7.

如果通过worksheet.removeRow（行行）删除行，则物理行计数应为7。

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

POI使用地图存储工作表的行。这张地图是物理部分。请参阅http://www.google.com/codesearch/p?hl=de#WXzbfAF-tQc/trunk/src/java/org/apache/poi/hssf/usermodel/HSSFSheet.java

As to the logically null rows, 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;
            }
        }
    }
}

#2

If you look at the code of the POI 3.8 beta 3, you'll see that removing a row should also remove its record. As such, the number of physical rows in the file should go down too

如果你看一下POI 3.8 beta 3的代码，你会发现删除一行也应该删除它的记录。因此，文件中的物理行数也应该减少

I'd suggest you try with a newer version of POI

我建议您尝试更新版本的POI

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

要计算文件中非空行的数量，请执行Amadeus建议并在工作表上的行上循环并检查它们是否有单元格。

#3

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

如果要手动删除，请确保使用删除行而不只是删除单元格中的数据，然后它将返回正确的值。

#4

I had the same problem. If I delete the rows manually, still the rowcount did not decrease, when I checked using sheet.getPhysicalNumberOfRows().

我有同样的问题。如果我手动删除行，当我使用sheet.getPhysicalNumberOfRows（）检查时，仍然没有减少rowcount。

When I deeply went into that issue I found the exact problem. I had an email column in my row, and when I entered an email address MS Office automatically detects that as an email address. And when I delete that entire row manually, the cell that carried the email address still had "" as its value(it will not be visible, but I found that value getting initialised when I read it through java). So since this cell has a not null value(""), this entire row is getting declared(sort of) and the row count is increased.

当我深入研究这个问题时，我发现了确切的问题。我的行中有一个电子邮件列，当我输入电子邮件地址时，MS Office会自动将其检测为电子邮件地址。当我手动删除整行时，携带电子邮件地址的单元格仍然具有“”作为其值（它将不可见，但我发现当我通过java读取它时，该值会被初始化）。因此，由于此单元格具有非空值（“”），因此整个行被声明（排序）并且行计数增加。

The funny part is that when I dont enter an email address, and just enter some string and then delete the row, the cell is not getting initialised and the ROWCOUNT GOT DECREASED actually. This is what I found as a result of my problem.

有趣的是，当我不输入电子邮件地址，只输入一些字符串然后删除该行时，单元格没有初始化，实际上ROWCOUNT GOT减少了。这是我发现问题的结果。

Finally I solved that, by not only addding null check for the cells, but also

最后我解决了这个问题，不仅要为细胞添加空检查，还要

if(cell != "")

if（cell！=“”）

Hope this might be useful for you

希望这可能对你有用

#5

We can write a custom method that will ignore blank rows to give row count. Probably we can make some assumptions based on out requirement. For example in my case, a row can be considered blank if its first column value is empty and count is needed only till first blank row.

我们可以编写一个自定义方法来忽略空行以提供行数。可能我们可以根据需求做出一些假设。例如，在我的情况下，如果第一列值为空，则可以将行视为空白，并且仅在第一个空白行之前需要计数。

So following snippet could 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;
    }
}

#1