使用Apache POI设置Excel德语公式报错的技术问询

问题场景 #

用Apache POI生成包含公式的Excel表格，其中一个单元格使用德语统计函数ZÄHLENWENN，但生成文件后该单元格显示报错。编辑栏里的公式显示正确（分隔符已自动转换），手动点击编辑栏按回车后，报错消失且公式能正常计算，但需要通过代码层面解决这个问题。

代码示例 #

public static void main(String[] args) throws FileNotFoundException, IOException {

    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("Test");
    Row row = sheet.createRow(2);
    Cell cell = row.createCell(0);

    cell.setCellFormula("ZÄHLENWENN(A1:A2, \"X\")");

    workbook.write(new FileOutputStream(new File("d:\\tmp\\test.xlsx")));
}

环境限制 #

• Java 8
• Apache POI 3.17（遗留系统无法升级）
• Microsoft Excel 2016（德语本地化版本）

解决方案 #

方案1：用英文函数名替代德语函数 #

Apache POI底层默认以英文函数名处理公式，旧版本对非英文函数的兼容性较差。直接使用对应英文函数COUNTIF设置公式，Excel德语版会自动将其转换为ZÄHLENWENN，且不会触发报错：

public static void main(String[] args) throws FileNotFoundException, IOException {

    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("Test");
    Row row = sheet.createRow(2);
    Cell cell = row.createCell(0);

    // 用英文函数COUNTIF，Excel德语版自动转为ZÄHLENWENN
    cell.setCellFormula("COUNTIF(A1:A2, \"X\")");

    workbook.write(new FileOutputStream(new File("d:\\tmp\\test.xlsx")));
}

方案2：强制设置工作簿自动重算 #

在写入文件前，开启工作簿的强制公式重算属性，让Excel打开文件时自动重新计算所有公式，修复报错：

public static void main(String[] args) throws FileNotFoundException, IOException {

    XSSFWorkbook workbook = new XSSFWorkbook();
    // 开启强制重算，忽略缺失的外部工作簿（可选）
    workbook.getCreationHelper().createFormulaEvaluator().setIgnoreMissingWorkbooks(true);
    workbook.setForceFormulaRecalculation(true);

    Sheet sheet = workbook.createSheet("Test");
    Row row = sheet.createRow(2);
    Cell cell = row.createCell(0);

    cell.setCellFormula("ZÄHLENWENN(A1:A2, \"X\")");

    workbook.write(new FileOutputStream(new File("d:\\tmp\\test.xlsx")));
}

注：方案2受POI版本限制可能存在兼容性问题，优先推荐方案1。

内容的提问来源于stack exchange，提问作者dur