Apache OpenOffice (AOO) Bugzilla – Issue 118023
Calc: Cut-and-paste between spreadsheets causes incorrect cell reference changes
Last modified: 2023-01-17 04:07:37 UTC
OOo Calc incorrectly changes cell references when you cut-and-paste cells between two spreadsheets. If the position of the cell you cut (for example, C2) is referenced in the spreadsheet you are pasting the cell into, and the source and destination worksheets have the same name, then all references (in the destination spreadsheet) to that position are changed to the pasted cell's position. Here's a very simple test: 1. Open a new spreadsheet. On Sheet1, enter "abc" in cell A1 and "=a1" in cell B1. 2. Open a second new spreadsheet. On Sheet1, enter "xyz" in cell A1. 3. Cut cell A1 from spreadsheet 2 Sheet1. 4. Paste the cell into spreadsheet 1 Sheet1 at cell 5D (the position at which you paste it doesn't really matter). Now look at cell B1 -- it has changed from "abc" to "xyz" because OOo changed the reference from "a1" (the cell's position before it was cut) to "d5" (the cell's position after it was pasted). This should not have happened because B1 was referencing its own spreadsheet, not the spreadsheet from which the cell was cut. This behavior only occurs if the name of the worksheet you cut from and the name of the worksheet you paste into are the same. For example, if you cut from a worksheet named "whatever" and paste into a worksheet named "whatever", then this behavior will occur. If you cut from a worksheet named "Sheet2" and paste into a worksheet named "whatever", then this behavior will not occur. That behavior occurs when you cut-and-paste a single cell or a range of cells. This behavior existed in OOo version 3.2 as well as version 3.3.
,
getting rid of value "enhancement" for field "severity". For enhancement the field "issue type" shall be used.
As given in description Rev. 1503704 Win 7
(In reply to cmclout from comment #0) > This behavior only occurs if the name of the worksheet you cut from and the > name of the worksheet you paste into are the same. For example, if you cut > from a worksheet named "whatever" and paste into a worksheet named > "whatever", then this behavior will occur. If you cut from a worksheet > named "Sheet2" and paste into a worksheet named "whatever", then this > behavior will not occur. This is not true. It appears to work based on indexes of sheets rather than their names: you probably had "whatever" as the first sheet in one document and "Sheet2" as the second sheet in another document, which is why you didn't see it. The bug can be reproduced with sheets of different names in the same position in different documents.
Top of a rough stack trace, with some of my notes: #0 ScFormulaCell::UpdateReference (this=0x82973c2d0, eUpdateRefMode=URM_MOVE, r=@0x7fffffff9448, nDx=0, nDy=3, nDz=0, pUndoDoc=0x82dd5b030, pUndoCellPos=0x7fffffff93f0) at cell2.cxx:743 #1 0x0000000822505435 in ScColumn::UpdateReference (this=0x821ad7010, eUpdateRefMode=URM_MOVE, nCol1=0, nRow1=3, nTab1=0, nCol2=0, nRow2=3, nTab2=0, nDx=0, nDy=3, nDz=0, pUndoDoc=0x82dd5b030) at column.cxx:1665 #2 0x00000008226cd49c in ScTable::UpdateReference (this=0x821ad7010, eUpdateRefMode=URM_MOVE, nCol1=0, nRow1=3, nTab1=0, nCol2=0, nRow2=3, nTab2=0, nDx=0, nDy=3, nDz=0, pUndoDoc=0x82dd5b030, bIncludeDraw=0 '\0', bUpdateNoteCaptionPos=true) at table1.cxx:1221 #3 0x0000000822554f6a in ScDocument::UpdateReference (this=0x815dcec90, eUpdateRefMode=URM_MOVE, nCol1=0, nRow1=3, nTab1=0, nCol2=0, nRow2=3, nTab2=0, nDx=0, nDy=3, nDz=0, pUndoDoc=0x82dd5b030, bIncludeDraw=0 '\0', bUpdateNoteCaptionPos=true) at documen3.cxx:929 Formula does change after ScTable::UpdateReference() #4 0x000000082257e369 in ScDocument::CopyBlockFromClip (this=0x815dcec90, nCol1=0, nRow1=3, nCol2=0, nRow2=3, rMark=@0x7fffffffaac8, nDx=0, nDy=3, pCBFCP=0x7fffffff9dc8) at document.cxx:1991 Function does not pass on pCBFCP->pClipDoc further Formula does change after ScDocument::UpdateReference() #5 0x000000082257fa56 in ScDocument::CopyFromClip (this=0x815dcec90, rDestRange=@0x7fffffffa710, rMark=@0x7fffffffaac8, nInsFlag=127, pRefUndoDoc=0x82dd5b030, pClipDoc=0x82dd58c30, bResetCut=1 '\001', bAsLink=0 '\0', bIncludeFiltered=1 '\001', bSkipAttrForEmpty=0 '\0', pDestRanges=0x7fffffff9d88) at document.cxx:2225 Formula does change after ScDocument::CopyBlockFromClip() #6 0x0000000821fc9367 in ScViewFunc::PasteFromClip (this=0x80af74890, nFlags=255, pClipDoc=0x82dd58c30, nFunction=0, bSkipEmpty=0 '\0', bTranspose=0 '\0', bAsLink=0 '\0', eMoveMode=INS_NONE, nUndoExtraFlags=0, bAllowDialogs=1 '\001') at viewfun3.cxx:1380 Function is 600 lines!!! 2 ScDocuments: pClipDoc with data being pasted into pDoc Calls pDoc->CopyFromClip() passing pClipDoc #7 0x00000008220f7d64 in ScCellShell::PasteFromClipboard (pViewData=0x80af74898, pTabViewShell=0x80af74810, bShowDialog=true) at cellsh1.cxx:2193 System vs own clipboard. Forcing use of the system clipboard doesn't by itself help #8 0x00000008220f20c8 in ScCellShell::ExecuteEdit (this=0x82a1a6c20, rReq=@0x7fffffffd310) at cellsh1.cxx:1179 ScDocument::CopyBlockFromClip() does not pass the source document any further up the stack, making it impossible for functions it calls to determine whether it's an intra-document or inter-document paste. This function also seems critical to the paste: it has a for loop transferring data, followed by an if statement updating references.
Created attachment 85284 [details] Hack: skip reference updates for inter-document clipboard paste This hack fixes the bug for me. By not performing reference updates when it's an inter-document paste (the "pCBFCP->pClipDoc->GetClipParam().getSourceDocID() == GetDocumentID()" is false), the reference stays correct instead of referencing the pasted cell. However I am not sure how reference updates work in general - the ScDocument::UpdateReference() function seems to do a lot, affecting chart references, area links, validation lists, etc. - so I am not sure what else could break... Can somebody more familiar with Calc's internals please weigh in on whether this approach is right?
Linux-32 Hi. The patch seems to implement the desired fix of leaving each sheet as its own universe as it were. Changed last verification to 4.1.2 (at which point the problem still happened.) So, I would say, please feel free to commit your patch, and mark as RESOLVED.
"damjan" committed SVN revision 1730772 into trunk: #i118023# Calc: Cut-and-paste between spreadsheets causes incorrect cell refe...
Thank you Kay. With further reading up and testing I am also happy. Patch committed, resolving fixed. Adding data_loss keyword. Latest confirmation in 4.2.0-dev and target milestone 4.2.0.
It is possible to create a reference to a cell in another sheet, but cut and paste apparently is not meant to do what is wanted. Pasting from outside the current workbook should paste values at most. That or it should fail loudly, methinks.
Cherry-picked for AOO41X with: https://github.com/apache/openoffice/commit/466742980fd82414a3ec22b5eb8300a7dd8b3976
*** Issue 127624 has been marked as a duplicate of this issue. ***