java - FormulaEvaluator.evaluateAll() with external references in Excel cells returns RuntimeException using Apache POI -

for past couple of days, has been driving me crazy.

please consider 2 excel files: a.xlsx , b.xlsx

here codes supposed evaluate cells in a.xlsx including external reference 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();         }     }  } 

unfortunately upon execution:

    exception in thread "main" java.lang.runtimeexception: not resolve external workbook name 'b.xlsx'. workbook environment has not been set up.     @ org.apache.poi.ss.formula.operationevaluationcontext.createexternsheetrefevaluator(operationevaluationcontext.java:113)     @ org.apache.poi.ss.formula.operationevaluationcontext.createexternsheetrefevaluator(operationevaluationcontext.java:84)     @ org.apache.poi.ss.formula.operationevaluationcontext.getref3deval(operationevaluationcontext.java:313)     @ org.apache.poi.ss.formula.workbookevaluator.getevalforptg(workbookevaluator.java:634)     @ org.apache.poi.ss.formula.workbookevaluator.evaluateformula(workbookevaluator.java:505)     @ org.apache.poi.ss.formula.workbookevaluator.evaluateany(workbookevaluator.java:263)     @ org.apache.poi.ss.formula.workbookevaluator.evaluate(workbookevaluator.java:205)     @ org.apache.poi.xssf.usermodel.basexssfformulaevaluator.evaluateformulacellvalue(basexssfformulaevaluator.java:189)     @ org.apache.poi.xssf.usermodel.basexssfformulaevaluator.evaluateformulacell(basexssfformulaevaluator.java:117)     @ org.apache.poi.hssf.usermodel.hssfformulaevaluator.evaluateallformulacells(hssfformulaevaluator.java:346)     @ org.apache.poi.hssf.usermodel.hssfformulaevaluator.evaluateallformulacells(hssfformulaevaluator.java:337)     @ org.apache.poi.xssf.usermodel.xssfformulaevaluator.evaluateall(xssfformulaevaluator.java:105)     @ test.main(test.java:28) caused by: org.apache.poi.ss.formula.collaboratingworkbooksenvironment$workbooknotfoundexception: not resolve external workbook name 'b.xlsx'. workbook environment has not been set up.     @ org.apache.poi.ss.formula.collaboratingworkbooksenvironment.getworkbookevaluator(collaboratingworkbooksenvironment.java:195)     @ org.apache.poi.ss.formula.workbookevaluator.getotherworkbookevaluator(workbookevaluator.java:156)     @ org.apache.poi.ss.formula.operationevaluationcontext.createexternsheetrefevaluator(operationevaluationcontext.java:111)     ... 12 more 

you need use apache poi 3.15 beta 3 or newer, or nightly build / build svn / build git on / after 2016-08-04. covered in apache poi changelog, xssf-specific bug has been subsequently fixed

using build/release fix in it, call evaluateall() on xssfformulaevaluator use referenced workbooks setup, hssf did along.