Thanks for the detailed info. That helped me find the issue - the calling library (Vaadin Spreadsheet) was assuming a saved formula string was an area reference, rather than a formula, and trying to parse it only as a simple sheet range. Overriding to use full formula parsing handled the workbook reference formatted named ranges properly. I don't see anything I need to add/change in POI at this point.
On Fri, Jun 9, 2017 at 2:21 AM Nick Burch <[email protected]> wrote: > On Fri, 9 Jun 2017, Greg Woolsey wrote: > > However, for unknown reasons not documented in Excel help as far as I can > > see, the named range reference has to be specified as: > > > > 'workbook file name.xlsx'!named_range > > > > however, when saving, the XML doesn't store the file name, but rather: > > > > [0]!named_range > > > > which of course is not recognized by POI's formula parsing. > > I've come across this before. There's a little bit of support for these in > XSSF, if you search the XSSF unit tests for "]!" you'll see some > > > My question is, does it seem reasonable, when the expression fails to > parse > > otherwise, to check if it starts with > > > > [0]! > > It can parse, but you have to supply the referenced workbooks for 1+. It's > a bit messy though - some things expose the [#] form, some things can work > with the [name.xlsx] form. Ideally we'd make it possible/easy to replicate > the Excel behaviour > > #56737 and #56752 are two bugs that spring to mind, > TestXSSFFormulaParser#formulaReferencesSameWorkbook() and > TestXSSFFormulaEvaluation#testReferencesToOtherWorkbooks() are two unit > tests to start with > > Nick > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [email protected] > For additional commands, e-mail: [email protected] > >
