java - Excel 单元格中具有外部引用的 FormulaEvaluator.evaluateAll() 使用 Apache POI 返回 RuntimeException

Question

在过去的几天里，这让我发疯。

请考虑两个 Excel 文件: a.xlsx和 b.xlsx

以下是应该评估 a.xlsx 中的单元格的代码，包括对 b.xlsx 的外部引用。

import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test {

    public static void main(String[] args) {
        try {
            FileInputStream file1 = new FileInputStream("C:\\Users\\Abid\\Desktop\\a.xlsx");
            FileInputStream file2 = new FileInputStream("C:\\Users\\Abid\\Desktop\\b.xlsx");

            XSSFWorkbook workbook1 = new XSSFWorkbook(file1);
            XSSFWorkbook workbook2 = new XSSFWorkbook(file2);

            FormulaEvaluator evaluator1 = workbook1.getCreationHelper().createFormulaEvaluator();
            FormulaEvaluator evaluator2 = workbook2.getCreationHelper().createFormulaEvaluator();

            Map<String, FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();

            workbooks.put("a.xlsx", evaluator1);
            workbooks.put("b.xlsx", evaluator2);

            evaluator1.setupReferencedWorkbooks(workbooks);
            evaluator1.evaluateAll();

            file1.close();
            file2.close();

            workbook1.close();
            workbook2.close();

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

不幸的是，这是我执行后得到的结果:

    Exception in thread "main" java.lang.RuntimeException: Could not resolve external workbook name 'b.xlsx'. Workbook environment has not been set up.
    at org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:113)
    at org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:84)
    at org.apache.poi.ss.formula.OperationEvaluationContext.getRef3DEval(OperationEvaluationContext.java:313)
    at org.apache.poi.ss.formula.WorkbookEvaluator.getEvalForPtg(WorkbookEvaluator.java:634)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:505)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:263)
    at org.apache.poi.ss.formula.WorkbookEvaluator.evaluate(WorkbookEvaluator.java:205)
    at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCellValue(BaseXSSFFormulaEvaluator.java:189)
    at org.apache.poi.xssf.usermodel.BaseXSSFFormulaEvaluator.evaluateFormulaCell(BaseXSSFFormulaEvaluator.java:117)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:346)
    at org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.evaluateAllFormulaCells(HSSFFormulaEvaluator.java:337)
    at org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator.evaluateAll(XSSFFormulaEvaluator.java:105)
    at Test.main(Test.java:28)
Caused by: org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment$WorkbookNotFoundException: Could not resolve external workbook name 'b.xlsx'. Workbook environment has not been set up.
    at org.apache.poi.ss.formula.CollaboratingWorkbooksEnvironment.getWorkbookEvaluator(CollaboratingWorkbooksEnvironment.java:195)
    at org.apache.poi.ss.formula.WorkbookEvaluator.getOtherWorkbookEvaluator(WorkbookEvaluator.java:156)
    at org.apache.poi.ss.formula.OperationEvaluationContext.createExternSheetRefEvaluator(OperationEvaluationContext.java:111)
    ... 12 more

Answer 1

它可以正常使用 HSSF (*.xls)。

但是 apache poi 是一团糟。所以XSSFFormulaEvaluator.evaluateAll()只需调用 HSSFFormulaEvaluator.evaluateAllFormulaCells(_book);。但是HSSFFormulaEvaluator.evaluateAllFormulaCells(Workbook wb)将创建一个不涉及环境的新 FormulaEvaluator。

相反，它应该调用 HSSFFormulaEvaluator.evaluateAllFormulaCells(Workbook wb, FormulaEvaluator evaluator)并移交已涉及环境的FormulaEvaluator。但这个方法是私有(private)。

幸运的是它没有那么大和独立。所以我们可以在代码中使用这个方法:

import java.io.FileInputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.ss.usermodel.*;

public class TestEvaluateExtRef {

     private static void evaluateAllFormulaCells(Workbook wb, FormulaEvaluator evaluator) {
         for(int i=0; i<wb.getNumberOfSheets(); i++) {
         Sheet sheet = wb.getSheetAt(i);

             for(Row r : sheet) {
                 for (Cell c : r) {
                     if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
                         evaluator.evaluateFormulaCell(c);
                     }
                 }
             }
         }        
    }

    public static void main(String[] args) {
        try {

            Workbook workbook1 = WorkbookFactory.create(new FileInputStream("a.xlsx"));
            Workbook workbook2 = WorkbookFactory.create(new FileInputStream("b.xlsx"));

            FormulaEvaluator evaluator1 = workbook1.getCreationHelper().createFormulaEvaluator();
            FormulaEvaluator evaluator2 = workbook2.getCreationHelper().createFormulaEvaluator();

            Map<String, FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();

            workbooks.put("a.xlsx", evaluator1);
            workbooks.put("b.xlsx", evaluator2);

            workbook2.getSheetAt(0).getRow(0).getCell(0).setCellValue(new java.util.Random().nextDouble());

            evaluator1.setupReferencedWorkbooks(workbooks);

            //evaluator1.evaluateAll();
            evaluateAllFormulaCells(workbook1, evaluator1);

            System.out.println(workbook1.getSheetAt(0).getRow(0).getCell(0));
            System.out.println(workbook1.getSheetAt(0).getRow(0).getCell(0).getNumericCellValue());

            workbook1.close();
            workbook2.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}