Apache OpenOffice (AOO) Bugzilla – Issue 110588
Sort fails to move cells (in other columns) which contain references to another file.
Last modified: 2017-05-20 10:30:44 UTC
Sorting by a chosen column (via Data|Sort) fails to move the cells in another column if those cells reference another ODS file. These cells are left in their original positions. Example Enter data in columns. One of the columns (eg column B) has cells with formulae which reference cells in another ODS file on the same computer. eg ='file:///C:/test/data.ods'#$Sheet1.A1 Select the data to be sorted. Choose Data|Sort. Choose to sort by a column which contains just numbers (eg column A). The columns in the selected area are resequenced together as they should be, except that the cells of the column containing the references to another file (colmun B) are left in their original positions.
Created attachment 68723 [details] ODS File 1 containing simple OpenOffice.org 3.2 spreadsheet to sort
Created attachment 68724 [details] ODS File 1 containing simple OpenOffice.org 3.2 spreadsheet to sort
Created attachment 68725 [details] Sorry, 'data.ods' contains the reference data that file 1 points to.
I should explain these attachments. The first file is 'test.ods' which contains 3 columns to sort. Cells in column B of 'test.ods' reference the second file, 'data.ods'. The third file is a duplicate of 'data.ods' - sorry.
These files have to be in directory C:\test for the references to work.
Reproducible with "Ooo 3.1.1 WIN XP DE[OOO310m19 (Build 9420)]" and "Ooo-Dev 3.2.1 multilingual version English UI WIN XP: [OOO300m14 (Build 9489)]"! Works for me with "2.4.1 Multilingual version German UI WIN XP: [680m17(Build9310)]" and "1.1.4 (German) WIN XP: [645m52 (Build 8824)]" (not for DDE-Links, only for ='file:///.... Always works for data from links to cells in the same document.
I don't know whether this is a bug or more an enhancement, but I think, that the behavior should be changed. Reasoning: If the referenced data are on another sheet in the same file as the reference, then during sorting the references are treated as if they where absolute. To be consistent the same should be done, if the referenced data are in another file. @matt_uk: You can use absolute references to get them sorted correctly.
OOps, that changes were not intended, setting back.
Thanks for the tip to use absolute references as a workaround, though absolute references can be a problem to use sometimes. As rainerbielefeld has identified, this problem arises for all non-absolute links to cells, even within the same sheet. It cannot be right for a sort to leave some cells on the wrong rows.
I can see now that the issue is more complex, because of the behaviour of non-absolute links. Another file, example2.ods is attached below. Select A3:D10, then choose Data|Sort, choose Column A. This has links to other cells in the same sheet. These links are in column B. The consequence of the sort algorithm is to change the links in this case, because they maintain a fixed offset to the cell containing the link. (Sort by column C to reset this sheet). The sort routine seems to have different rules for movings different types of links, but it is unpredictable for the user. As originally identified, non-absolute links to another file do not get moved at all by the sort routine when their row is moved. Perhaps the behaviours can be looked at. There is no warning to users of what rule is being applied, and it can even vary within a single sort event. The most obvious way a sort should work is to move whole rows together, not altering the source references of any links, just moving the cells containing the links with the row they are in. This is what a user would naturally expect to happen. However, are non-absolute links stored as offsets? If so, perhaps the offsets should be recalculated by the sort routine when moving rows?
Created attachment 68732 [details] example2.ods - an example of a sort where the rows contain non-absolute links within the same sheet
If I sort test.ods or example2.ods manually using drag-and-drop (with the ALT key) to move a row of selected of cells (eg move row of cells A5:D5 up or down), then the whole row of selected cells remains intact, and links in all cells continue to point to the originally referenced cells, regardless of whether those referenced cells are in the same sheet or a different ODS document, and regardless of whether the links are absolute or non-absolute. This is exactly the functionality the user would expect, and is surely the way Data|Sort should behave.
Could the Sort routine move rows (or columns) using the same routines used by Drag-and-Drop(with 'ALT' key). Or the routines to move rows/columns could be based on the routines used by Drag-and-Drop('ALT' key).
Grabbing issue. Treat external references the same as references to other sheets.
Code change reviewed.
changeset 55737a716866 http://hg.services.openoffice.org/cws/calc66/changeset/55737a716866 M sc/source/core/tool/interpr1.cxx You can observe the progress and possible integration date of CWS calc66 at http://tools.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fcalc66
Reassigning to QA for verification.
Seen ok in cws calc66 -> verified