Java POI XSSF VLookup 公式

Question

我正在尝试使用Java和Apache POI在“ .xlsx”文件中放置简单的VLookup公式。
这个公式具有外部引用，但对我来说不起作用。
因此，为了向您提供更多详细信息，我正在使用poi和poi-ooxml版本3.13以及Excel 2007。
我是这样将公式放入单元格中的（其中cell是一个Cell）：

cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula("StringContainingFormula");

然后对公式进行评估，我试了三种不同的方法，但都没有成功。（wb是XSSFWorkbook）。

FormulaEvaluator mainWorkbookEvaluator = wb.getCreationHelper().createFormulaEvaluator();
Map<String,FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();
workbooks.put("SpreadsheetName.xlsx", mainWorkbookEvaluator);
mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);
mainWorkbookEvaluator.evaluateAll();

2

XSSFEvaluationWorkbook.create(wb);
Workbook nwb = wb;
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for (Sheet sheet : nwb) {
    for (Row r : sheet) {
        for (Cell c : r) {
            if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                try {
                    //evaluator.evaluateFormulaCell(c);
                    evaluator.evaluate(c);
                } catch (Exception e) {
                    System.out.println("Error occured in 'EvaluateFormulas' : " + e);
                }
            }
        }
    }
}

3

XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);  

问题是，它写入Excel文件，但在评估时会抛出错误：

java.lang.IllegalArgumentException: Invalid sheetIndex: -1

现在，如果我打开Excel文件，它会给我一个警告：

已禁用链接的自动更新

如果我启用内容，则公式会正确显示结果，如果我不做任何操作，则公式会导致#N/A。
现在，如果我选择带有公式的单元格并单击公式栏，然后按Enter键，公式将显示结果。
更新：
所以，我通过进入Excel选项禁用了警告消息，这样它就开始在单元格内部提供公式。
但是，当我尝试使用以下方法从中获取结果值时：

if (cell.getCachedFormulaResultType == Cell.CELL_TYPE_STRING) {
    System.out.println("Last evaluated as \"" + cell.getRichStringCellValue() + "\"");
}

它从来没有给我返回getCachedFormulaResultType作为CELL_TYPE_STRING，它总是返回CELL_TYPE_NUMERIC。它应该返回字符串值。我期望URL和其他一些值（用"|"分隔的单词 - "cat|dog|bird"）。

我感激任何帮助/建议。

- ManishChristian

---

@Nicholas，非常感谢您的建议，但我找不到类似于wb.setForceFormulaRecalculation(true);的东西，我已经尝试了createFormulaEvaluator参考，但没有成功。 - ManishChristian

嗯，我的意思是文档上说你应该为每个电子表格都创建一个 createFormulatEvaluator，包括主要的电子表格和 VLookup 引用的电子表格。 - K.Nicholas

我尝试使用两个电子表格（将它们都添加了），但是出现了错误“尝试在名称为“电子表格一（引用自）”和“电子表格二（引用至）”的情况下注册相同的工作簿”。 - ManishChristian

如果您愿意，我可以在这里尝试一个简单的例子。您是否使用setupReferencedWorkbooks(java.util.Map<java.lang.String,FormulaEvaluator> workbooks)映射了外部引用？ - K.Nicholas

@Nicholas，那太棒了。另外，如果我使用XSSFWorkbook，则不会给我setupReferencedWorkbooks选项。我可能需要将其转换为Workbook然后再尝试。 - ManishChristian

Answer 1

好的，看起来它正在工作。您必须温柔地对待Excel；如果您先打开外部工作簿，然后再打开主要工作簿，它会更加快乐。如果不这样做，它会给出有关不安全链接的消息，但除此之外，一切似乎都没问题。评估器仅适用于单个单元格，因此如果您想重新计算整个电子表格，则必须进行循环。此外，外部引用必须已由Excel链接，POI尚未实现该功能。以下是我放在github上的内容：

    // Open workbooks
    Path pathBook1 = Paths.get("c:/users/karl/scsb/Vlookup/Book1.xlsx");
    InputStream is = Files.newInputStream(pathBook1);
    XSSFWorkbook book1 = new XSSFWorkbook(is);
    // Add Linked Cell
    // The workbook must already have been linked to Book2.xlsx by Excel
    // The linkExternalWorkbook has not yet been implemented: SEE BUG #57184
    Cell cell = book1.getSheetAt(0).createRow(2).createCell(0);
    cell.setCellFormula("A2+[Book2.xlsx]Sheet1!A1");
    // Create evaluator after the new cell has been added.
    FormulaEvaluator mainEvaluator = book1.getCreationHelper().createFormulaEvaluator();
    XSSFWorkbook book2 = new XSSFWorkbook("c:/users/karl/scsb/Vlookup/Book2.xlsx");
    Map<String, FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();
    workbooks.put("Book1.xlsx", mainEvaluator);
    workbooks.put("Book2.xlsx", book2.getCreationHelper().createFormulaEvaluator());
    mainEvaluator.setupReferencedWorkbooks(workbooks);
//  mainEvaluator.evaluateAll();                            // doesn't work.
//  XSSFFormulaEvaluator.evaluateAllFormulaCells(book1);    // doesn't work.
    mainEvaluator.evaluateFormulaCell(cell);
    System.out.println(cell.getNumericCellValue());
    book2.close();
    // Close and write workbook 1
    is.close();
    OutputStream os = Files.newOutputStream(pathBook1);
    book1.write(os);
    os.close();
    book1.close();

- K.Nicholas

---

对我来说它不起作用。如果在打开“Referenced”工作簿之前使用 setCellFormula（没有完整路径（就像您所做的那样）），它会给出错误 java.lang.RuntimeException：Book not linked for filename "ReferencedFileName.xlsx"。如果使用完整路径，它将再次给出“Invalid sheetIndex: -1”错误。 - ManishChristian

就像我之前提到的那样，在运行此代码之前，必须通过Excel将外部电子表格链接起来。在POI中有一个名为linkExternalWorkbook的方法调用可以链接电子表格，但它尚未实现。对此我无能为力。 - K.Nicholas

首先，非常感谢 @Nicholas 的帮助。那么，最简单的说法是，我们可以说目前这是不可行的吗？因为如果我们无法链接工作簿，那么引用外部工作簿就没有意义。 - ManishChristian

是的，我们可以说目前完全用Java做不到。 - K.Nicholas

Answer 2

我最近也遇到了同样的问题。问题是，使用当前的POI，无法在尚不存在的文档上编写文档、将公式添加到特定字段并执行公式评估。我用来解决它的技巧是：

1. 将所有数据和公式写入工作簿
2. 在关闭此文档之前添加 workbook.setForceFormulaRecalculation(true);
3. 编写并关闭文档
4. 再次打开相同的文档，只需执行 FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); evaluator.evaluateAll();
5. 再次编写并关闭

- Vanja Lee

Answer 3

所以，由于我没有其他选择，我不得不将引用的数据复制到主工作簿中（我知道这不是一个好主意，如果数据很大的话）以使事情正常运行。
所以我做的是：

1. 从外部工作簿中复制引用的数据。
2. 在主工作簿上创建新的工作表（需要编写公式的工作簿），并粘贴引用的数据。
3. 现在编写公式，这次不使用外部工作簿，而是使用新创建的工作表（带有粘贴的数据）。
   注意*：在评估公式之前，您需要先编写所有数据（包括公式）。
4. 然后，如果您已经关闭了工作簿，请打开它以进行读取，并像这样评估所有公式（wb是XSSFWorkbook）：

XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

- ManishChristian