Receiving "Book not linked" when evaluating formular which references another workbook

Question

I have two woorkbooks:

1. map.xlsx
2. req.xlsx

map.xlsx has a sheet Mapping, req.xlsx has a sheet ABC Request.

I want to insert and evaluate a formula in map.xlsx which references req.xlsx. According to POI Documentation I use the following code.

FormulaEvaluator mapWbEvaluator = mapWb.getCreationHelper().createFormulaEvaluator();
FormulaEvaluator reqWbEvaluator = reqWb.getCreationHelper().createFormulaEvaluator();

Map<String, FormulaEvaluator> evalMap = new HashMap<>();
evalMap.put("map.xlsx", mapWbEvaluator);
evalMap.put("req.xlsx", reqWbEvaluator);

mapWbEvaluator.setupReferencedWorkbooks(evalMap);

Sheet mapSheet = mapWb.getSheet("Mapping");

CellReference tgtRef = new CellReference("A25");
Cell tgtCell = mapSheet.getRow(tgtRef.getRow()).getCell(tgtRef.getCol());
tgtCell.setCellFormula("IF('[req.xlsx]ABC Request'!R28,1,2)");
CellValue cellVal = mapWbEvaluator.evaluate(tgtCell);

When the last line is executed I receive the following exception:

Exception in thread "main" java.lang.RuntimeException: Book not linked for filename req.xlsx at org.apache.poi.xssf.usermodel.BaseXSSFEvaluationWorkbook.resolveBookIndex(BaseXSSFEvaluationWorkbook.java:135)

Any idea what's going wrong? Do I miss anything?

Answer 1

FormulaEvaluator.setupReferencedWorkbooks only tells the evaluator where the linked workbooks are. It does not link them. They must be linked already in the main workbook (the workbook where main evaluator mapWbEvaluator comes from).

Also tgtCell.setCellFormula("IF('[req.xlsx]ABC Request'!R28,1,2)"); will not link the workbook. It only sets that formula into the cell. But the req.xlsx would must be linked within the map.xlsx using a external links table. In that external links table the link gets an index, 1 for example, and the internally stored formula then would be IF('[1]ABC Request'!R28,1,2). There the 1 points to the external links table which links to the req.xlsx then.

Apache poi does not provide creating or changing external links table. There is ExternalLinksTable but that only gets read while creating the apache poi XSSFWorkbook. There is nothing to create a new external links table for the workbook. And ExternalLinksTable does not provide methods to add new external links.

In Why can't I link one workbook to another in Apache POI? I have shown an example for using low level methods to create an external links table to link two *.xlsx workbooks together. In that case to get chart data from a linked workbook. But the problem is the same for to get cell data from a linked workbook.

So either you will bothering yourself with that code and understand what needs to be done to create external links between two workbooks or you will wait until apache poi provides that feature in it's high level classes.