https://bz.apache.org/bugzilla/show_bug.cgi?id=60811
Bug ID: 60811
Summary: Possibility to keep structured references string
Product: POI
Version: 3.15-FINAL
Hardware: PC
Status: NEW
Severity: normal
Priority: P2
Component: XSSF
Assignee: dev@poi.apache.org
Reporter: phili0...@gmx.de
Target Milestone: ---
When you parse a formula with structured references like '[columnname]', they
will be replaced with an area reference! But this results in two problems when
rendering the formula back to a string via FormulaRenderer:
- You will definetely loose the orginal reference string e.g. '[columnname]'
because it will be replaced with something like 'sheetname!A2:A10'. However it
may be desired to keep the original structured reference string because it is
more readable. Especially in large tables with many columns where you don't
know immediately what data is in column e.g. BF.
- Another problem is that when you copy the formula to different cells in the
table and then add some new rows to the table, all the copied formulas will be
invalid because the area reference does not cover the complete column anymore.
If you open such a file in Excel 2016 it will give you warnings like 'This cell
is inconsistent with the column formula'. If the structured references would be
kept, this would never be a problem because such a reference remain valid
independent of the number of rows in the table.
So for example a use case from me:
I have a template sheet that contains a table with a single row. Some of the
cells in the row contain formulas. Now I load some data from a database and
insert the data row by row into the table. Therefore the formulas have to be
copied. The formulas are mixed with structured references and normal cell
references like E2, A4,... which depend on the cell's row. Hence I have to
parse the orginal formula, update the normal cell references depending on the
current row and set the updated formula. But exactly this results now in the
described problem because the structured reference is now an area reference
that gets not updated with the increasing table. After inserting all the data I
update the table area. I can't do that before because I don't know how many
data there will be. So adding an option to keep the structural reference as a
structural reference would be realy nice.
--
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@poi.apache.org
For additional commands, e-mail: dev-h...@poi.apache.org
