[Bug 60811] New: Possibility to keep structured references string

[bu...@apache.org]

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: Phili0815@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-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 60811] Possibility to keep structured references string

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60811

--- Comment #3 from Phili0815@gmx.de ---
Yeah, of course there are several workarounds for me. Currently I escape the
structured references such that they are handled as a string and unescape them
after rendering the formula back. The problem is that I have multiple
templates, not all of them use structured references, not all of them have even
set a table and some use both etc. So my programm have to deal with all these
cases. Thats why it would be nice to have.

Or may be you know a better way. I noticed that Excel stores column formulas
for every column. These formulas can be found in
CTTableColumn.getCalculatedColumnFormula() and they also causes the error
message in Excel: 'This cell is inconsistent with the column formula' if you
set the manipulated formulas with the wrong translated structural reference.
Does there exists a possibility in POI to use this formula by default for a
table column? Copying in every cell is not working because then again normal
cell references are wrong.

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 60811] Possibility to keep structured references string

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60811

--- Comment #4 from Greg Woolsey <gw...@apache.org> ---
I have a rather large class that does pretty much what you are wanting, that I
use for my day job.  It digs into the XSSFTable for the related CTConnection,
uses that to query the database, and updates the table definition with the new
results, wiping out previous rows.

I combine the 

CTQueryTableFields.getQueryTableFieldList() 
and the 
CTTable.getTableColumns().getTableColumnList()

by index into a composite object that tracks the column definitions.  For
calculated columns, that don't come from the query results, I just set the cell
formula to:

CTTableColumn.getCalculatedColumnFormula().getStringValue()

which works for me just fine in all my tests so far.  I've not had to do any
manual adjusting of formulas, but I don't know that I've tried any with
references that weren't already in structured query format.

If the table isn't an actual table, but just a formatted range of cells, then
you will have to write your own handling.  But this works for me for QueryTable
updates, which is a key part of my company's product.

I've thought about making this part of POI, but haven't had the time or
inclination to figure out what a reasonable API would be.  Currently I use
ResultSet.next() and ResultSet.getObject(CTQueryTableField.getName()) to match
things up.  I suppose I could write an Interface that encapsulated them, with
some checked exceptions for missing/invalid column names in the input vs. the
table definition.

Doesn't do anything for reversing formula evaluation, but does handle
QueryTable updates.

That should be enough details to point you in the right direction if you want
to take a crack at it.

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 60811] Possibility to keep structured references string

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60811

--- Comment #2 from Greg Woolsey <gw...@apache.org> ---
When I implemented structured reference formula parsing last year, I was
focused on evaluation, not reversibility, so I didn't go to the work of
creating a new Ptg type, I just converted the reference to an area Ptg as part
of parsing the formula.  So I see the work you would like to have here.

However, why do you need to parse the formula into a Ptg[] when adding new
rows?  Why can't you convert all the normal cell references to structured
row-based column references?  Then you don't have to manipulate the formula
string at all, just copy it to new rows.

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 60811] Possibility to keep structured references string

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60811

--- Comment #1 from Javen O'Neal <on...@apache.org> ---
This seems doable. I'm assuming a structures reference is its own Ptg that can
be eval'd to an AreaReference.

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org

[Bug 60811] Possibility to keep structured references string

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=60811

Phili0815@gmx.de changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 OS|                            |All
           Severity|normal                      |enhancement

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org