poi-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Greg Woolsey <greg.wool...@gmail.com>
Subject Re: Excel formula saving oddity
Date Fri, 09 Jun 2017 21:56:39 GMT
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 <apache@gagravarr.org> 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: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message