Java POI XSSF VLookup公式

Question

我试图使用Java和Apache POI将简单的VLookup公式放入我的“.xlsx”文件中。
该公式有外部引用，但对我不起作用。

因此，为了给您提供更多细节，我使用了poi和POI-OOXML3.13版以及Excel2007。
我以这种方式将公式放入单元格（其中单元格是单元格）：

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

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文件，然后在计算时抛出错误：

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

它从来没有给我getCachedFormulareSultType作为cell_type_string，它总是返回cell_type_numeric。它应该返回字符串值。我将删除URL和其他值（由“”-“Catdogbird”分隔的单词）。

我很感激任何帮助/建议。

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();