Apache OpenOffice (AOO) Bugzilla – Issue 56535
queryPrecedents (XFormulaQuery) on Calc-cells don't recognize cross-table-references
Last modified: 2017-07-05 20:01:28 UTC
try the following: - create a new spreadsheet document and name the first table "table1" and the second "table" - enter a value into table1.D24 - enter formula "=table1.D24" into table2.C6 - now get the precedents cells of table2.C6 via API and you'll find some unexpected values: Sub PrintFormulaPrecedents spreadSheetDocument = ThisComponent sheet1 = spreadSheetDocument.getSheets().getByIndex(0) sheet2 = spreadSheetDocument.getSheets().getByIndex(1) ' "table2.C2" formulaCell = sheet2.getCellByPosition(2, 5) ' get the precedents of this cell (Aspect: XFormulaQuery) ranges = formulaCell.queryPrecedents(true) ' prints: table2.C6; table2.D24 (should be table1.D24 only) print ranges.getRangeAddressesAsString() e = ranges.getCells().createEnumeration() while e.hasMoreElements() cell = e.nextElement() ' prints: table2.5.2 (should be table1.23.3) print "Precedents " & cell.SpreadSheet.Name _ & "." & cell.RangeAddress.StartRow _ & "." & cell.RangeAddress.StartColumn wend End Sub
Sorry, there's a bug in my description: the beginning is meant to be: try the following: - create a new spreadsheet document and name the first table "table1" and the second "table2" ^^^^^^
Created attachment 30772 [details] File demonstrating the described behaviour
*** Issue 56534 has been marked as a duplicate of this issue. ***
sw->nn: looks like one for you :-) ... I'm not sure why the cells itself if part of the precedents but it definitely shouldn't be "table2.D24" in the given sample, but "table1.D24"
changing target
Same with queryDependents. I was about to file an issue about queryDependents and found this one. The following basic-code demonstrates both. REM run this basic code against any spreadsheet, having 2 sheets at least. REM it creates in A2:A6 simple dependents of A1, prints the dependents' addresses REM Then it moves away A2 to A1 of 2nd sheet and prints the dependetnts' addresses again. REM Then it prints the precedents of moved cell. REM the moved dependent sheet(1)A2 gets lost, while the detective indicates a dependent on another sheet. REM the moved dependent has no precedent other than itself Sub Main Dim oCellAddr as new com.sun.star.table.CellAddress Dim oRangeAddr as new com.sun.star.table.CellRangeAddress oSh = ThisComponent.getSheets.getByIndex(0) oSh.getCellByPosition(0,1).setFormula("=$A$1") oSh.showDependents(oCellAddr) sN = oSh.getName oSh.getCellRangeByPosition(0,1,0,5).fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM,1) cell_A1 = thiscomponent.sheets(0).getCellByPosition(0,0) printDependents cell_A1, TRUE," of "& sN &".A1" REM Move A2 to Sheet2.A1 oCellAddr.Sheet = 1 oRangeAddr.StartRow = 1 oRangeAddr.EndRow = 1 oSh.moveRange(oCellAddr,oRangeAddr) printDependents cell_A1, TRUE," of "& sN &".A1 after moving A2" sN = ThisComponent.getSheets.getByIndex(oCellAddr.Sheet).getName cell_Moved = thisComponent.getSheets.getByIndex(oCellAddr.Sheet).getCellByPosition(oCellAddr.Column,oCellAddr.Row) printPrecedents cell_Moved, TRUE," of "& sN &".A1" End Sub Sub printDependents(oCell,bRecursive As Boolean,sCaption$) thiscomponent.calculateAll sRangeNames = oCell.queryDependents(bRecursive).getRangeAddressesAsString aNames() = Split(sRangeNames,";") MsgBox Join(aNames(),Chr(10)),0,"DEPENDENTS"& sCaption End Sub Sub printPrecedents(oCell,bRecursive As Boolean,sCaption$) thiscomponent.calculateAll sRangeNames = oCell.queryPrecedents(bRecursive).getRangeAddressesAsString aNames() = Split(sRangeNames,";") MsgBox Join(aNames(),Chr(10)),0,"PRECEDENTS"& sCaption End Sub
Reset assigne to the default "issues@openoffice.apache.org".
I can confirm that this bug still exists. And I think I have spotted where the cause lies. Internally, XFormulaQuery is correctly finding the cell references (with the proper table id). But it is using a class called ScMarkData to keep track of the references, and this class is only storing the row and column data, not the table id. In main/sc/inc/markdata.hxx, there is a TODO comment in German: //! todo: //! Es muss auch die Moeglichkeit geben, MarkArrays pro Tabelle zu halten, //! damit "alle suchen" ueber mehrere Tabellen wieder funktioniert! Translation: "There must also be the possibility to keep MarkArrays per table, So "search all" over several tables again works!" So somebody was already aware that this needed to be fixed. Unfortunately, I don't feel qualified to actually submit a patch, but I hope my small bit of detective work will be helpful. Thanks!