Mailing-List: contact dev-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Developers List" <dev@poi.apache.org>
MIME-Version: 1.0
From: Greg Woolsey <greg.woolsey@gmail.com>
Date: Fri, 09 Jun 2017 00:18:10 +0000
Message-ID: <CAEwphwrjvjcAS24vrKrHJ1LFAJjX=H9Ug=WYYbomA2Dm07juTQ@mail.gmail.com>
Subject: Excel formula saving oddity
To: POI Developers List <dev@poi.apache.org>
Content-Type: multipart/alternative; boundary="001a1140573c22942d05517be840"
archived-at: Fri, 09 Jun 2017 00:18:25 -0000

--001a1140573c22942d05517be840
Content-Type: text/plain; charset="UTF-8"

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

--001a1140573c22942d05517be840--