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
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
>
>
