Dynamically add External (Cross-Workbook) referenc

Question

In our project, we have different versions of excelsheets which reference each other:

C:\V1\Sample.xls //no references

C:\V2\Sample.xls //references V1

C:\V3\Sample.xls //references V2

Example of a cell value:

=MID('C:\V1\[Sample.xls]Sheet1'!$AB2;21;1)

Now I want to evaluate formulas of V3 using apache POI, I found the following example here

// Create a FormulaEvaluator to use
FormulaEvaluator mainWorkbookEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

// Track the workbook references
Map<String,FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();
// Add this workbook
workbooks.put("report.xlsx", mainWorkbookEvaluator);
// Add two others
workbooks.put("input.xls", WorkbookFactory.create("c:\temp\input22.xls").getCreationHelper().createFormulaEvaluator());
workbooks.put("lookups.xlsx", WorkbookFactory.create("/home/poi/data/tmp-lookups.xlsx").getCreationHelper().createFormulaEvaluator());

// Attach them
mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);

// Evaluate
mainWorkbookEvaluator.evaluateAll();

Now my problem: I do not know the locations of the files, I therefore need to get all references from the mainworkbook and then automatically (and probably recursively) add them, not static like in the example above. Is there a function to get the references or does anyone know a way to achieve this?

Additionally, I am wondering if I have to add all FormulaEvaluator to V3 or do I have to add V2 to V3 and V1 to V2 for this to work?

I currently have setIgnoreMissingWorkbooks(true) implemented, but as the values will change and we do not want to open each excel file manually to update the references I want to implement this solution. Any help is appreciated

Answer 1

To get all external references use following method:

private static Set<String> getReferencedWorkbooks(Workbook workbook) {
    Set<String> workbookNames = new HashSet<>();
    final EvaluationWorkbook evalWorkbook;
    if (workbook instanceof HSSFWorkbook) {
        evalWorkbook = HSSFEvaluationWorkbook.create((HSSFWorkbook) workbook);
    } else if (workbook instanceof XSSFWorkbook) {
        evalWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook);
    } else {
        throw new IllegalStateException();
    }
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        Sheet sheet = workbook.getSheetAt(i);
        final EvaluationSheet evalSheet = evalWorkbook.getSheet(i);
        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    final EvaluationCell cell = evalSheet.getCell(c.getRowIndex(), c.getColumnIndex());
                    final Ptg[] formulaTokens = evalWorkbook.getFormulaTokens(cell);
                    for (Ptg formulaToken : formulaTokens) {
                        final int externalSheetIndex;
                        if (formulaToken instanceof Ref3DPtg) {
                            Ref3DPtg refToken = (Ref3DPtg) formulaToken;
                            externalSheetIndex = refToken.getExternSheetIndex();
                        } else if (formulaToken instanceof Ref3DPxg) {
                            Ref3DPxg refToken = (Ref3DPxg) formulaToken;
                            externalSheetIndex = refToken.getExternalWorkbookNumber();
                        } else {
                            externalSheetIndex = -1;
                        }

                        if (externalSheetIndex >= 0) {
                            final ExternalSheet externalSheet = evalWorkbook.getExternalSheet(externalSheetIndex);
                            workbookNames.add(externalSheet.getWorkbookName());
                        }
                    }
                }
            }
        }
    }
    return workbookNames;
}

If your all of your workbooks are XLSX/XLSM you can use following code:

private static Set<String> getReferencedWorkbooksXssf(XSSFWorkbook workbook) {
    Set<String> workbookNames = new HashSet<>();
    final List<ExternalLinksTable> externalLinksTable = workbook.getExternalLinksTable();
    for (ExternalLinksTable linksTable : externalLinksTable) {
        final String linkedFileName = linksTable.getLinkedFileName();
        workbookNames.add(linkedFileName);
    }

    return workbookNames;
}