We now have some support for External References (Workbook + Name or Workbook + Sheet + Cell) in the formula parser. This bug is to track what work is still required for a full(ish) implementation: * When evaluating XSSF external references, the last seen values can be cached in the link table. These should be used when evaluating formulas, if the external workbook wasn't attached to the evaluation context. See #56752 * When fetching the formula string in HSSF, the workbook name or id should be included in the string for external references. (XSSF returns the id) * There should be an easy way to get the XSSF external workbook ID replaced with the filename, for XSSF formulas with external references, to give a "what excel shows" form of the formula string * It should be possible to create HSSF formulas which refer to a new external workbook, with the link table being set up for it (failing, commented out unit test available) * It should be possible to create XSSF formulas which refer to a new external workbook, with the link table and relations being set up for it (failing, commented out unit test available)
https://bz.apache.org/bugzilla/show_bug.cgi?id=60219 Patch contains implementation of requirement: * It should be possible to create HSSF formulas which refer to a new external workbook, with the link table being set up for it (failing, commented out unit test available)
The following unit-tests at https://github.com/ZuInnoTe/hadoopoffice.git can be incorporated to verify some of the things discussed in this enhancement: * fileformat/src/test/java/org/zuinnote/hadoop/office/format/mapred/OfficeFormatHadoopExcelTest.java * fileformat/src/test/java/org/zuinnote/hadoop/office/format/mapreduce/OfficeFormatHadoopExcelTest.java
see https://github.com/apache/poi/pull/254