Apache POI读取Excel时清空内容单元格为null的场景区分问题

解决方案 #

根本原因说明 #

Microsoft Excel的存储逻辑本身不会保留「曾经有内容后被清空」的单元格定义，只要用户清空了单元格的内容和样式，保存文件时Excel会直接移除该单元格的底层存储节点，因此Apache POI读取时会和从未创建过的单元格一样返回null，这是Excel的存储机制决定的，没有直接的API可以100%区分两种场景，但可以通过业务逻辑优化解决问题。

方案1：调整校验逻辑（落地成本最低，适配绝大多数场景） #

绝大多数业务场景下，用户不会主动清空一行中的部分单元格，只会出现两种情况：

• 整行所有单元格都清空：属于主动删除该行数据
• 行内存在至少一个非空单元格：该行是有效数据行，其余null单元格属于漏填
  可以基于这个逻辑调整校验规则，同时配合MissingCellPolicy避免空指针判断：

if (sheet == null) {
    log.error("Sheet is null!");
    return false;
}
var valid = true;
for (Row row : sheet) {
    int rowNum = row.getRowNum();
    // 先判断当前行是否为全空行
    boolean isRowAllBlank = true;
    // 先遍历一遍所有物理单元格判断行是否为空
    for (Cell cell : row) {
        if (cell.getCellType() != CellType.BLANK) {
            isRowAllBlank = false;
            break;
        }
    }
    // 全空行直接跳过校验，判定为用户主动清空的行
    if (isRowAllBlank) {
        continue;
    }
    // 非全空行校验所有必填列
    for (var i = 0; i < numColumns; i++) {
        // 用CREATE_NULL_AS_BLANK策略，未定义的单元格也会返回BLANK类型的Cell对象，不会返回null
        final var cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        if (cell.getCellType() == CellType.BLANK) {
            log.error("Cell {}{} is null or empty!",  Character.toString('A' + i), rowNum + 1);
            valid = false;
        }
    }
}

方案2：自定义空值约定（100%精准区分两种场景） #

如果业务确实需要支持单行中部分单元格主动清空的场景，可以和用户约定统一的空值占位符：

• 用户需要主动清空某个单元格时，填写-/NULL等约定好的特殊标记
• 代码逻辑中：
  • 单元格为null：判定为漏填
  • 单元格值为约定的占位符：判定为主动清空，跳过校验
  • 其余情况正常读取内容
    该方案完全适配所有业务场景，用户操作成本极低，只需要记住一个简单的填写规则即可。

方案3：读取Excel修订记录（适合有审计需求的场景） #

如果要求不能修改用户操作习惯，可以要求用户开启Excel的「修订」功能，Apache POI可以读取文件的修订历史，判断目标单元格是否有过内容被删除的记录，以此区分两种场景。该方案缺点是需要引导用户开启修订功能，适合内部系统等可控场景使用。

内容的提问来源于stack exchange，提问作者Eric van Coffee