Summary: | Possibility to keep structured references string | ||
---|---|---|---|
Product: | POI | Reporter: | Phili0815 |
Component: | XSSF | Assignee: | POI Developers List <dev> |
Status: | NEW --- | ||
Severity: | enhancement | ||
Priority: | P2 | ||
Version: | 3.15-FINAL | ||
Target Milestone: | --- | ||
Hardware: | PC | ||
OS: | All |
Description
Phili0815
2017-03-03 11:42:23 UTC
This seems doable. I'm assuming a structures reference is its own Ptg that can be eval'd to an AreaReference. 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. 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. 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. |