Excel formula saving oddity

[Greg Woolsey <gr...@gmail.com>]

I ran across this from a user file today, and hadn't seen it before.

Chart series definitions default to just cell address ranges, but can be
set to named ranges instead with some clicking and typing in the UI, which
makes charts more dynamic.

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.


When opening the file back up in Excel, it replaces [0] with the file name
again in the formulas.

My question is, does it seem reasonable, when the expression fails to parse
otherwise, to check if it starts with

[0]!

and if so, strip that off the input and try again?  Could do this up front
and not do a second pass, but I don't know if there is valid syntax that
could start with that string somehow.

This would just be for parsing, it wouldn't update the stored value, as
that's needed for the next time Excel opens the file.

Not quite sure how to support creating charts using this syntax in POI - I
don't have to solve that problem for my current task, but it's an
interesting one to consider.  I suppose a user could just set the saved
formula above and it would work.  That would need documentation I suppose,
but this Excel functionality is only documented in non-MS places.  I
suspect it is a side effect they didn't plan for, but have to support
because it's being widely used.

Greg

Re: Excel formula saving oddity

[Greg Woolsey <gr...@gmail.com>]

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 <ap...@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
>
>

Re: Excel formula saving oddity

[Nick Burch <ap...@gagravarr.org>]

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