如何使用Apache POI检查excel单元格是否为空？

I am taking input from an excel sheet using Poi.jar and wanted to know how to check if a cell is empty or not.

我正在使用Poi.jar从excel表中获取输入，并想知道如何检查单元格是否为空。

Right now I m using the below code.

现在我使用下面的代码。

cell = myRow.getCell(3);
if (cell != null) {
    cell.setCellType(Cell.CELL_TYPE_STRING);

    //System.out.print(cell.getStringCellValue() + "\t\t");
    if (cell.getStringCellValue() != "")
        depend[p] = Integer.parseInt(cell.getStringCellValue());

    }
}

7 个解决方案

#1

How about:

怎么样：

 Cell c = row.getCell(3);
 if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) {
    // This cell is empty
 }

#2

Gagravarr's answer is quite good!

Gagravarr的答案非常好！

---

Check if an excel cell is empty

But if you assume that a cell is also empty if it contains an empty String (""), you need some additional code. This can happen, if a cell wasn't cleared properly (for how to do that, see further below).

但是如果你假设一个单元格也是空的，如果它包含一个空字符串（“”），你需要一些额外的代码。如果没有正确清除单元格，可能会发生这种情况（有关如何执行此操作，请参见下文）。

I wrote myself a helper to check if an XSSFCell is empty (including an empty String).

我给自己写了一个帮助来检查XSSFCell是否为空（包括一个空字符串）。

 /**
 * Checks if the value of a given {@link XSSFCell} is empty.
 * 
 * @param cell
 *            The {@link XSSFCell}.
 * @return {@code true} if the {@link XSSFCell} is empty. {@code false}
 *         otherwise.
 */
public static boolean isCellEmpty(final XSSFCell cell) {
    if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
        return true;
    }

    if (cell.getCellType() == Cell.CELL_TYPE_STRING && cell.getStringCellValue().isEmpty()) {
        return true;
    }

    return false;
}

---

Example

This JUnit test shows the case in which the additional empty check is needed.

此JUnit测试显示需要额外空检查的情况。

Scenario: the content of a cell is changed within a Java program. Later on, in the same Java program, the cell is checked for emptiness. The test will fail if the isCellEmpty(XSSFCell cell) function doesn't check for empty Strings.

场景：在Java程序中更改单元格的内容。稍后，在同一Java程序中，检查单元格是否空白。如果isCellEmpty（XSSFCell单元格）函数未检查空字符串，则测试将失败。

@Test
public void testIsCellEmpty_CellHasEmptyString_ReturnTrue() {
    // Arrange
    XSSFCell cell = new XSSFWorkbook().createSheet().createRow(0).createCell(0);

    boolean expectedValue = true;
    boolean actualValue;

    // Act
    cell.setCellValue("foo");
    cell.setCellValue("bar");
    cell.setCellValue("");
    actualValue = isCellEmpty(cell);

    // Assert
    Assert.assertEquals(expectedValue, actualValue);
}

---

In addition: Clear a cell properly

Just in case if someone wants to know, how to clear the content of a cell properly. There are two ways to archive that (I would recommend way 1).

以防万一有人想知道，如何正确清除单元格的内容。存档有两种方法（我建议方法1）。

// way 1
public static void emptyCell(final XSSFCell cell) {
    cell.setCellType(Cell.CELL_TYPE_BLANK);
}

// way 2
public static void emptyCell(final XSSFCell cell) {
    String nullString = null;
    cell.setCellValue(nullString);
}

Regards winklerrr

问候winklerrr

#3

There is one other option also .

还有另一种选择。

row=(Row) sheet.getRow(i);
        if (row == null || isEmptyRow(row)) {
            return;
        }
Iterator<Cell> cells = row.cellIterator();
    while (cells.hasNext())
     {}

#4

.getCellType() != Cell.CELL_TYPE_BLANK

#5

Cell cell = row.getCell(x, Row.CREATE_NULL_AS_BLANK);

This trick helped me a lot, see if it's useful for you

这个技巧对我有很大帮助，看看它对你有用

#6

As of Apache POI 3.17 you will have to check if the cell is empty using enumerations:

从Apache POI 3.17开始，您必须使用枚举检查单元格是否为空：

import org.apache.poi.ss.usermodel.CellType;

if(cell == null || cell.getCellTypeEnum() == CellType.BLANK) { ... }

#7

First to avoid NullPointerException you have to add this Row.MissingCellPolicy.CREATE_NULL_AS_BLANK this will create a blank cell instead of giving you NPE then you can check to make sure nothing went wrong just like what @Gagravarr have said.

首先要避免NullPointerException，你必须添加这个Row.MissingCellPolicy.CREATE_NULL_AS_BLANK这将创建一个空白单元而不是给你NPE然后你可以检查以确保没有任何问题，就像@Gagravarr所说的那样。

Cell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
    if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) 
        // do what you want