使用Java Apache POI删除Excel工作表后文件进入保护模式问题排查

问题背景 #

我需要删除Excel文件中的部分工作表，使用了以下Java Apache POI代码来删除不必要的工作表：

public static boolean deleteUnnecessarySheets(Workbook workbook, boolean authorize) { 
    if(workbook != null) { 
        int noOfSheets = workbook.getNumberOfSheets(); 
        for( int sheetNo = noOfSheets - 1; sheetNo >= 0; sheetNo-- ) { 
            Sheet sheet = workbook.getSheetAt(sheetNo); 
            String sheetName = sheet.getSheetName(); 
            if(sheetName.equalsIgnoreCase("User Defined Actions") || sheetName.equalsIgnoreCase("AUTHORIZE")) { 
                if(!authorize && sheetName.equalsIgnoreCase("AUTHORIZE")) { 
                    workbook.removeSheetAt(sheetNo); 
                } 
            } else { 
                workbook.removeSheetAt(sheetNo); 
            } 
        } 
        return true; 
    } 
    return false; 
}

代码已按预期成功删除工作表，但手动打开该Excel文件时会出现报错并进入保护模式。点击“查看详细信息”后显示对应详情截图。请问该如何避免此问题？或者我哪里操作有误？

问题分析与解决方案 #

这种报错我之前处理过不少，大多是因为删除工作表后，Excel内部的关联引用（比如命名区域、单元格公式、VBA宏或者其他依赖对象）没被清理干净，POI只是单纯删掉了工作表，但这些残留的无效引用会让Excel打开时校验失败，直接进入保护模式。下面给你一步步排查和修复的方法：

1. 清理残留的命名区域 #

Excel里的命名区域经常会引用工作表，你删掉表之后这些引用就失效了。可以在删除工作表的代码后面加一段逻辑，把指向已删除工作表的命名区域删掉：

// 删除工作表后，清理无效的命名区域
for (int i = workbook.getNumberOfNames() - 1; i >= 0; i--) {
    Name name = workbook.getNameAt(i);
    String formulaRef = name.getRefersToFormula();
    boolean isRefInvalid = true;
    // 检查这个引用是否指向当前还存在的工作表
    for (int sheetIdx = 0; sheetIdx < workbook.getNumberOfSheets(); sheetIdx++) {
        Sheet existingSheet = workbook.getSheetAt(sheetIdx);
        if (formulaRef.contains(existingSheet.getSheetName() + "!")) {
            isRefInvalid = false;
            break;
        }
    }
    if (isRefInvalid) {
        workbook.removeName(i);
    }
}

2. 检查并修复单元格公式 #

如果保留的工作表里有公式引用了已删除的表，Excel打开肯定会报错。你可以遍历所有保留的工作表，把这些无效公式处理掉：

// 遍历所有保留的工作表，清理无效公式
for (int sheetIdx = 0; sheetIdx < workbook.getNumberOfSheets(); sheetIdx++) {
    Sheet sheet = workbook.getSheetAt(sheetIdx);
    for (Row row : sheet) {
        for (Cell cell : row) {
            if (cell.getCellType() == CellType.FORMULA) {
                String formula = cell.getCellFormula();
                // 判断公式是否引用了已删除的工作表
                boolean refersToDeleted = formula.contains("User Defined Actions!") 
                        || (!authorize && formula.contains("AUTHORIZE!"));
                if (refersToDeleted) {
                    // 这里可以选择清空单元格，或者替换成默认值，根据你的需求来
                    cell.setCellValue("");
                }
            }
        }
    }
}

3. 处理VBA宏或其他依赖对象（如果文件有这些内容） #

如果你的Excel文件带VBA宏、图表或者其他绑定工作表的对象，这些东西也可能引用已删除的表。如果不需要宏的话，可以直接移除VBA模块：

// 如果是XLSX文件，检查并移除VBA模块（如果存在）
if (workbook instanceof XSSFWorkbook) {
    XSSFWorkbook xssfWorkbook = (XSSFWorkbook) workbook;
    POIXMLDocumentPart vbaPart = xssfWorkbook.getPackagePartByName("/xl/vbaProject.bin");
    if (vbaPart != null) {
        xssfWorkbook.removePart(vbaPart);
    }
}

4. 确保工作簿正确保存 #

有时候文件损坏是因为保存方式不对，一定要用try-with-resources来确保输出流正确关闭，避免文件写入不完整：

// 正确的保存示例，记得用try-with-resources自动关闭流
try (FileOutputStream fos = new FileOutputStream("processed_excel.xlsx")) {
    workbook.write(fos);
} catch (IOException e) {
    e.printStackTrace();
}

5. 优化删除逻辑，确保存在可见工作表 #

另外，你的代码里如果authorize为true时会保留"AUTHORIZE"表，但如果最后所有表都被隐藏了（比如只剩一个隐藏表），Excel也可能报错。可以加个检查，确保至少有一个可见工作表：

// 删除完成后，检查是否有可见工作表，没有的话设置第一个为可见
boolean hasVisibleSheet = false;
for (int sheetIdx = 0; sheetIdx < workbook.getNumberOfSheets(); sheetIdx++) {
    Sheet sheet = workbook.getSheetAt(sheetIdx);
    if (sheet.getSheetState() != SheetState.HIDDEN && sheet.getSheetState() != SheetState.VERY_HIDDEN) {
        hasVisibleSheet = true;
        break;
    }
}
if (!hasVisibleSheet && workbook.getNumberOfSheets() > 0) {
    workbook.getSheetAt(0).setSheetState(SheetState.VISIBLE);
}

内容的提问来源于stack exchange，提问作者Sai Manoj Kadiyala