Calc/Drafts/Issue 33851

Before 2008-03 this used to be a specification draft for issue 33851, which is about how filtered rows should be treated during cut/copy/paste/fill cells operations, and due to its length and controversial discussion is unlikely to be read and understood, so presenting the outcome in a wiki page and editing that instead is much more appropriate. Please bear in mind that this page is geared towards developing a specification (see also Category:Specification), don't discuss here, use the Talk page (discussion link above) instead, or even better chime in to the thread on the discuss@ux mailing list. Thanks.

With fixing the most critical issues of not overwriting data in CWS filteredrows   as discussed in the UX thread mentioned, behavior changed. For details please see the announcement mail. Sections in this page are marked with CWS filteredrows to line out the changes.

Due to time restrictions there is no implementation for cases where a fill operation starts on a single cell with no selection crossing filtered rows. These need still to be implemented and we have Issue 89232 for this.

General overview

There's consensus that operations modifying the document's content should not modify content of rows that are not displayed due to an applied filter setting. Some simply argue do it like Excel, however, that's not what Excel does, see single topics below.

Copy cells

This sounds easy: just copy the cells that are visible (not filtered out).

Note that the current Calc implementation does not allow a multi-selection to be copied to the clipboard. It reached consensus that the current Calc behavior, to treat the visible rows as one area, is sufficient.

• For what to do during paste if the cells contain formulas with relative references see below under #Paste cells.

CWS filteredrows: As an implementation detail always the entire selection is copied to the internal clipboard, differentiation is done during paste or when creating the transfer object for other applications.

Cut cells

Sounds the same as copy cells, just cut the cells that are visible. Though the paste case may be easier, probably the references should be adjusted the same way as usual in a cut/paste scenario.

• NOTE: Excel 2003 in this case cuts all cells, including filtered. Do it like Excel probably does not apply here.

Note that current Calc implementation does not allow a multi-selection to be copied to the clipboard.

• For what to do during paste if the cells contain formulas with relative references see below under #Paste cells.

Please note, that cut could break formulas inside the hidden cells, so cutting only the visible portion is quite dangerous. I have added additional comments on this issue in the section on Paste Cut Cells (also note, that this comment applies only to Cut, NOT to Copy) - Discoleo 20:16, 16 January 2007 (CET)

CWS filteredrows: Cut is disabled if the selection crosses filtered rows.

Paste cells

Do not touch rows that are filtered-out.

• How to handle the case when the area in clipboard is larger than a selected area to be pasted to?
  • Excel 2003 ignores the selection.
  • CWS filteredrows: Asks for confirmation before using the enlarged area.
• How to handle the case when the selected area is larger than the area in clipboard?
  • Excel 2003 ignores the selection except if a multiple of the columns of the clipboard content is selected.
    • I think we should ignore the selection, EXCEPT if pasting single cell, in which case we should respect the selection. --Kpalagin 17:59, 14 January 2007 (CET)
  • CWS filteredrows: Same as in an unfiltered selection the clipboard content is repeated until the selection is filled.

• NOTE: Excel 2003 pastes also to filtered rows. Do it like Excel probably does not apply here.
  • Agreed here.--Kpalagin 17:59, 14 January 2007 (CET)
  • CWS filteredrows: Does not paste to filtered rows. The clipboard content is distributed over the visible rows.

Paste copied cells

It reached consensus that the current Calc behavior, to treat the visible rows as one area and thus paste as one area, is sufficient.

Paste copied cells with formulas

• NOTE: Excel 2003 does not paste the formulas if the cells were copied from a filtered range, only the values are pasted. Do it like Excel probably does not apply here.
  • I think we should Do it like Excel. We can have 3 cases:
    1. Identical formulas in selected cells, in which case it is sufficient to copy just one cell.
    2. Formulas are different, in which case trying to guess user's intention is fruitless.
    3. Single cell is copied. This should be allowed regardless of filtering. --Kpalagin 17:59, 14 January 2007 (CET)
  • CWS filteredrows: Pastes the formulas as usual. Pasting values only can always be achieved by using Paste Special.

Paste copied cells with formulas containing cell references

Adjust formulas, as usual in copy/paste.

CWS filteredrows: References are adapted for each slice of visible rows as if it was pasted individually.

Paste copied formula cells to filtered rows

Do not touch hidden cells. --Kpalagin 17:59, 14 January 2007 (CET)

CWS filteredrows: Filtered rows are not touched.

Paste copied formula cells to visible cells

Adjust references.--Kpalagin 17:59, 14 January 2007 (CET)

• CWS filteredrows: References are adapted for each slice of visible rows as if it was pasted individually.

Paste cut cells

Because the Cut could/would break formulas in hidden cells

• => cutting both hidden and non-hidden seems an option
• => alternative: provide user with the option to select between the 2 possibilities

Let's ask user.

NOTE:

• this is a problem specific for CUT
• it does NOT apply for Copy
  • Copy does NOT break hidden cells
  • therefore this comment applies soley to Cut

-- Discoleo 01:09, 12 January 2007 (CET)

NOTE: this section is about pasting cells that were cut, the comment belongs to #Cut cells instead.

CWS filteredrows: In general the handling is identical to pasting copied cells, with the exception that references are adapted as usual in cut/paste of course instead of copy/paste.

Paste cut cells with formulas

Paste cut cells with formulas containing cell references

Adjust references as usual in a cut/paste scenario.

CWS filteredrows: Does it.

Paste cut formula cells to filtered rows

Do not touch hidden cells. --Kpalagin 17:59, 14 January 2007 (CET)

CWS filteredrows: Filtered rows are not touched.

Paste cut formula cells to visible cells

Adjust formulas, as usual in cut/paste.

CWS filteredrows: Does it.

Fill cells

Fill only non-filtered (visible) rows.

CWS filteredrows: If a selection crosses filtered rows, Fill operations are disabled. Starting from a single row and dragging the corner currently does not take filtered rows into account. This needs to be implemented later.

Fill cells with formulas containing cell references

• How to adapt relative cell references?
  • Treat as if the visible area was a contiguous area? Probably the best.
    • The same as if one single cell was copied to the clipboard and then pasted to several locations, leaving out the hidden rows. Most logical. And is also what Excel 2003 does.

Fill series

1. Fill continuously in the non-filtered (visible) rows?
2. Or fill with gaps, leaving out the values that would go into the filtered rows?
3. Or do nothing?
   • Excel 2003 disables Fill Series on filtered ranges.
   • Probably the best, fill series on a filtered range doesn't make much sense anyway.

CWS filteredrows: If a selection crosses filtered rows, Fill operations are disabled. Starting from a single row and dragging the corner currently does not take filtered rows into account. This needs to be implemented later.

Scenario 1

If you want to get some serial numbering for visible data after filtering, Fill Series would be a good function to do that, if rows what have been filtered out will be ignored.

• Current behaviour: Fill Series will also fill invisible rows
• Desired behaviour: Fill Series should be available, but only touch visible cells. The user would probably expect the filtered rows to be treated as normal, contiguous cells.