Apache OpenOffice (AOO) Bugzilla – Issue 21773
Unwanted cut and paste of hidden rows and columns
Last modified: 2013-08-07 15:15:02 UTC
If you select some rows or columns aroud hidden rows or colums and paste the selected data into an other spredsheet document then the hidden data are pasted as well. For example: - open a sdc document (table) with N rows - select rows 2 to N-2 - click on the left and "Hide the rows" - select the remaining rows - paste them to a different document -> all rows are pasted, not only the selected (even the hidden) I can not compare this behaviour with MS Office or other spreadsheets just now but I think that this behaviour is broken and should be changed. It is something similar to the Issue #2977 but this particular problem is less destructive.
Hi, this functionality never existed in OOo, so this can not be broken. So this is an enhancement and I assign it to Bettina who handles such requests. As we should mostly act as Excel do, this should be addressed for OOo2.0 Frank
I'm copying this info into issue 2977 issue 21773 as 2977 seems to be focussed on PASTING and 21773 seems to be related to COPY'ing. Does someone want to decide whether to include copy AND paste operations in issue 2977 (the older, more discussed, voted on etc issue), or keep the issues separate. Due to the complication of COPY CUT PASTE and DELETE (see below) I suggest a single issue. When looking in 1.1 Help to check terminology I found a page titled 'Only Copy Visible Cells' in the index under 'hidden cells / do not copy'. This page indicates there are 3 behaviours when COPYING: a) copy of autofilter - only visible cells copied b) move of autofilter (cut/paste or drag) - visible and hidden cells moved c) copy OR move of explicit hide or Outline Group hidden - all cells copied or moved. I confirmed by testing that the above help info does indeed matches current 1.1 Calc behaviour for COPY, provided that 'visible cells' is defined as the visible result of the autofilter WITHOUT any cells hidden by formatting or Outline Group / Hide Group. MS Excel exhibits the following behaviour (using the example from issue 2977) - sorry this is long: 1) Create a blank spreadsheet with row 1 containing 2 column headers, e.g. A1='Item' and B1='What' 2) Fill columns A2 to A16 with numbers 1 to 15 3) Add a text 'XX' to columns B5, B10 and B14 only 4a) Turn on AutoFilter: -standard-what=-empty- This should hide rows 5, 10 and 14. 4b) Select A1:B16, COPY and paste to a new worksheet. Result: Only visible cells are copied (12 rows total=15 rows excluding B5,B10,B14) 5a) Keep the above and hide row 7. 5b) Select A1:B16, COPY and paste to a new worksheet. Result: only visible cells are copied (11 rows total=15 rows excluding 5, 10, 14 and 7) 6a) Keep above and use the PivotTable/Outline function to group row 12 and hide it. 6b) Select A1:B16, COPY and paste to a new worksheet. Result: only visible cells are copied (10 rows copied=15 rows excluding 5, 10, 14, 7 and 12) 7a) Keep the above but remove the AutoFilter 7b) Select A1:B16, COPY and paste to a new worksheet. Result: all rows are copied. 8a) Return to step 4a (unhide hidden rows and Outline Group, apply filter) 8b) Select A1:B16, CUT and paste to a new worksheet. Result: ALL rows are moved. 9a) Keep above (undo CUT or move data back) and hide row 7. 9b) Select A1:B16, CUT and paste to a new worksheet. Result: ALL rows are moved. 10a) Keep above (undo CUT or move data back) and use the PivotTable/Outline function to group row 12 and hide it. 10b) Select A1:B16, CUT and paste to a new worksheet. Result: ALL rows are moved. 11a) Return to step 4a (unhide hidden rows and Outline Group, apply filter). 11b) In empty cells in column B enter O (enter individually - don't use paste!) 11c) Select A5:B8, DELETE, then remove filter to see effect of DELETE. Result: Only selected visible cells (rows 5, 7 and 8) were DELETE'd 12a) Return test data to state after 11b) 12b) Hide row 7 12c) Select A5:B8, DELETE, then remove filter to see effect of DELETE. Result: Only selected visible cells (row 5 and 8) were deleted. 13a) Return test data to state after 11b) 13b) use the PivotTable/Outline function to group row 7 and hide it. 13c) Select A5:B8, DELETE, then remove filter to see effect of DELETE. Result: Only selected visible cells (row 5 and 8) were deleted. 14a) Return test data to state after 11b) 14b) Remove AutoFilter 14c) Hide row 7 14d) Select A5:B8, DELETE, then remove filter to see effect of DELETE. Result: Rows 5-8 deleted. So the outcome of this test is that in Excel 97: i) COPY only operates on visible cells whenever an AutoFilter is active (different to OOo, as it copies only visible cells not hidden by the autofilter, hidden cells OR hidden Groups). ii) COPY operates on all cells (visible and hidden) if no AutoFilter is active (same as OOo) iii) CUT/move operates on all cells at all times (same as OOo) iv) DELETE operates similarly to COPY (OOo DELETEs all cells at all times) PASTE is the subject of issue 2977 and well described there. Comparing Excel to OOo: i) When AutoFilter on, OOo copies all cells not hidden by AutoFilter, Excel copies all VISIBLE cells ii) Excel and OOo same. iii) Excel and OOo same. iv) OOo deletes all cells at all times, Excel's DELETE operates similarly to COPY: if AutoFilter on only DELETEs VISIBLE; if AutoFilter off, DELETEs all cells. Although the above Excel behaviour is not intuitive to me, it does follow the 'do not destroy' behaviour if AutoFilter is on. This is the same 'do not destroy' request of issue 2977 for pasting. And although not intuitive it IS what MS Office users would expect. Does anyone want to confirm the Excel behaviour in 2000 or XP versions? Are there any properties that allow the behaviours to be modified/customised? Based on the above, and the expectation that we want to be compatible with MS Office users, should the Excel 97 behaviour be what is required in this issue? Is it worthwhile including a preference to allow this behaviour to be explicitly defined? Spreadsheet / General has Input settings that it could be related to.
Covered by 2977 *** This issue has been marked as a duplicate of 2977 ***
Closed on agreement with owner
I had exactly the same problem, and before posting a bug, I seartched the database and came across this one. Why is this issue closed? Issue 2977 is not the same, neither is it a superset of this issue. Indeed, as stated earlier, these bugs describe two opposite ends of the cut-and-paste operation. (This bug should be closed ONLY IF they have a common remedy.) Since I am not the originator, I cannot reopen the issue. In my case, there was a list, which I subtotalled. Then I clicked on Consolidation level-2 button, and then hid the some entries. Now this is ewxactly what I want to paste in a new place. But when I copy-and-paste, all the rows are pasted, including the row data. So my concern is NOT about the hidden part overwriting something. But it IS about not being able to get rid of hidden rows. See sample. Try to copy the range and paste somewhere. (You can delete the subtotals to see the original raw data.)
Created attachment 36146 [details] Sample shows inability to ignore hidden rows
It seems that the issue #2977 has been closed and the issue #33851 was opened instead. The new issue seems to cover also this one. If you have a new information, could you please add it to the new issue?