Bug 57474 - Copy-paste of multiline cells to external text editor inconsistent
Summary: Copy-paste of multiline cells to external text editor inconsistent
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) Master
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Calc-Cells Cut-Copy
  Show dependency treegraph
Reported: 2012-11-24 04:15 UTC by Russell Warren
Modified: 2017-12-19 14:32 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Russell Warren 2012-11-24 04:15:14 UTC
To reproduce:

1. Create a new spreadsheet
2. Make A1 a multiline cell with hard breaks (eg: 'abc<ctrl-enter>def')
3. Make A2 a single line cell (eg: 'ghi')
4. Copy A1 and A2 together
5. Paste contents across to an external text-only editor (like Geany)

Contents for the above example will paste across as:


Note that the multiline cell has been wrapped in double-quotes.

If the multiline cell contents contain a double quote, it ends up escaping by doubling up the quotes.

ie: abc"def   becomes  abc""def  on copy.

What should happen (in my opinion)

All text should paste across exactly as if the contents were copied directly, with no wrapping quotes (or double-up escaping).  ie: the example shoudl paste across as:


Previous versions of Calc did precisely this.  Excel also does this.

In case this multiline-quote-enclosing/escaping was introduced as a feature to try and make it clear in the pure-text paste where individual cells/rows are delimited... this does not work and causes more confusion than without it.  Consider the case where a single line cell has a leading double quote in it.  This double quote is NOT escaped, so you cannot parse the pasted pure text contents to determine with certainty what the cell delimiting was.

It should paste across as pure text.  No wrapping quotes or escapes.  If there is a convincing argument for some other pasting scheme, it should at least be a configurable option.

This issue exists in 3.5.4, as well as the master as of Nov 21, 2011 (1c31558e9739ea6cf0cfa58d5cbda8d808eb0527) - thanks to shm_get for confirming this.
Comment 1 Norbert Thiebaud 2012-11-24 04:52:36 UTC
reproduced on master
Comment 2 Russell Warren 2012-11-24 05:20:52 UTC
I incorrectly stated in the initial report that Excel does not do this... it does.  I just checked Excel 2003 and Excel 2007.  They both do the quote-wrapping on multiline text paste.

I now expect that consistency with Excel was why this was added to LO at some point.  I still think it is not the right way to do it, though for the same inconsistency arguments initially stated.
Comment 3 Russell Warren 2012-11-24 05:37:35 UTC
To clarify why it is self-inconsistent and ambiguous, the following cell contents:




pastes across as:


but this is also EXACTLY how the cell contents below paste across:



<C1 empty>

ie: no automation can reliably reverse this to spreadsheet cell contents.

It should either be perfectly reversible, or consistently simple.  I think the old OpenOffice way was the latter, and preferable.  Or make it a configurable option how text pasting operations work.  But what would you label the inconsistent/irreversible quoting scheme as?
Comment 4 Urmas 2012-11-24 05:52:03 UTC
If a cell text containing "'s is not enclosed in quotes, then that is a bug.
Comment 5 Markus Mohrhard 2012-11-24 17:11:36 UTC
As you mention this has been most likely added for interoperability reasons so this can't be a regression.
Comment 6 QA Administrators 2015-01-05 17:51:42 UTC Comment hidden (obsolete)
Comment 7 QA Administrators 2016-01-17 20:03:22 UTC Comment hidden (obsolete)
Comment 8 QA Administrators 2017-03-06 14:08:02 UTC Comment hidden (obsolete)
Comment 9 JMM 2017-05-06 14:09:23 UTC
(Note: I'm making this comment because this is just another of the thousands of forgotten btanb)

I don't think this is a bug, it's rather a feature that it's wanted or not. The pasted text is enclosed in double quotes if it contains a new line. It's the way Calc makes the text looks like 'it starts here' and 'it ends here'.

As for why the text can't be reconstructed back to spreadsheet uniquely, as in the example where two different cell contents paste as two equal texts, well text lines are not cells. If that feature would be needed, then every extraneous character (newline, tab, etc.) should be escaped when pasting as text e.g. with \n \t etc. and then unscaped when pasting text back into Calc. That could be a feature request, but certainly this is not a bug. Basically my opinion is that wanting that pasting cells as text should be always reversible is more complex than it seems, due to the implications of my earlier statement: text lines are not cells. Changing Calc to paste cell content being reversible might make the pasted text unusable for most uses imho.

Personally I want Calc to paste newlines as newlines in my text editor; getting rid of the double quotes is an unwanted side-effect but I understand the need of r them. A few times I wanted Calc to escape those special chars, but nothing that a little editing or formula mangling can't do.

If anyone with experience in these things shares my view, change status to resolved.
Comment 10 Buovjaga 2017-12-19 14:32:00 UTC
Yep, based on the previous comments, we should close.