I recently got this working, but noticed these points of fragility in doing so with the current released code:
1. If the file names had spaces in them, I had to replace them with "%20" in the map keys passed to setupReferencedWorkbooks(), as that's the value used in the stored formula expressions in my end-user produced workbooks. 2. I had to be very careful to map the exact FormulaEvaluator instances used with each workbook. If the map contained an evaluator instance different than the one doing the evaluating for any expression, I would get the same error you did. 3. I had to call formulaEvaluator.setupReferencedWorkbooks(evaluatorMap) for _every_ evaluator in the map, and again, the map had to reference the exact same evaluator instance as the one having its setup method called. It turns out at first I had several places where new FormulaEvaluator instances were being created, often via workbook.getCreationHelper().createFormulaEvaluator() behind the scenes. This caused no end of grief until I could ensure the same instance was used for a given workbook in a given thread every time. I hope this helps. Any thoughts or patches that might help facilitate documenting how to set it up and perhaps validating setup when calling would be appreciated. I agree this is an area that is lightly documented and easy to encounter problems with complex causes that are hard to trace. On Wed, Sep 26, 2018 at 5:26 PM wimgoe...@gmail.com <wimgoe...@gmail.com> wrote: > Hello, > > I need to evaluate all the formulas in a workbook which has references to > other workbooks. Based on my findings I found that I had to use > FormulaEvaluator#setupReferencedWorkbooks in order to link all the > workbooks, but that did not work, and still resulted in a RuntimeException > saying "Could not resolve external workbook name 'file.xlsx'. Workbook > environment has not been set up." > > I managed to work around the problem through some hacks with reflection. > > BaseFormulaEvaluator seems to use static members of > CollaboratingWorkbooksEnvironment, which creates instances of > CollaboratingWorkbooksEnvironment. However, these instances seem to be > constructed but not used (see CollaboratingWorkbooksEnvironment.java:75 -- > [new CollaboratingWorkbooksEnvironment(evaluatorsByName, evaluators);]) > > I copied the contents of those methods to create a > CollaboratingWorkbooksEnvironment, and forced it into the > _collaboratingWorkbookEnvironment field of my WorkbookEvaluator instance. > This solved my problem entirely. > > Since this solved my issue I suspect this is a bug, so decided to share it > here in the hopes it may get fixed in the official repository. > > Best regards, > Wim > > --------------------------------------------------------------------- > To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org > For additional commands, e-mail: dev-h...@poi.apache.org > >
