带有Excel中外部引用的FormulaEvaluator.evaluateAll()使用Apache返回RuntimeException
带有Excel中外部引用的FormulaEvaluator.evaluateAll()使用Apache返回RuntimeException
提问于 2016-08-01 19:21:17
在过去的几天里，这让我抓狂。
下面是计算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回答 2
Stack Overflow用户
回答已采纳
发布于 2016-08-03 11:57:21
您需要使用Apache 3.15 beta 3或更高版本，或者使用从svn到svn的每晚构建/构建，或者在2016-08-04之后使用git构建/构建。作为Apache POI变更日志中涵盖的内容，这是一个特定于XSSF的bug，随后得到了修复。
使用包含修复的构建/发行版，对evaluateAll()在XSSFFormulaEvaluator上的调用现在将使用任何已经安装的参考工作簿，就像HSSF一直以来所做的那样。
Stack Overflow用户
发布于 2016-08-02 16:04:59
它将在没有HSSF (*.xls)问题的情况下工作。
但阿帕奇却一团糟。因此，XSSFFormulaEvaluator.evaluateAll()只需调用HSSFFormulaEvaluator.evaluateAllFormulaCells(_book);。但是HSSFFormulaEvaluator.evaluateAllFormulaCells(Workbook wb)将创建一个不涉及环境的新 FormulaEvaluator。
相反，它应该调用HSSFFormulaEvaluator.evaluateAllFormulaCells(Workbook wb，FormulaEvaluator评价者)并交出已经在环境中涉及的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();
}
}
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/38706562
复制相关文章
相似问题
腾讯云开发者
