Bug 43175 - EDITING: Sheet references not correctly updated in charts when copying multiple sheets in Calc
Summary: EDITING: Sheet references not correctly updated in charts when copying multip...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Chart (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: Other All
: medium major
Assignee: Andreas Heinisch
URL:
Whiteboard: target:7.2.0 target:7.1.4
Keywords:
: 51702 102910 139190 (view as bug list)
Depends on:
Blocks: Chart
  Show dependency treegraph
 
Reported: 2011-11-22 12:32 UTC by Stephan van den Akker
Modified: 2021-05-10 21:04 UTC (History)
13 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Speadsheet with two sheets that both contain a graph (17.03 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-11-22 12:32 UTC, Stephan van den Akker
Details
The problem also happens when copying a single sheet with graphs. (34.00 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-03-30 12:40 UTC, snowboard975
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Stephan van den Akker 2011-11-22 12:32:21 UTC
Created attachment 53787 [details]
Speadsheet with two sheets that both contain a graph

How to reproduce:

1) Create a spreadsheet with two sheets
2) Put graphs on both sheets, referencing data from the same sheet
or:
1+2) Get the spreadsheet in the attachment
and then:
3) Select both sheets
4) Copy the sheets

Expected result:
The copied graphs reference data on the corresponding copied sheet.
This is the result for the active sheet (the one with the tab "on top")

Actual result:
Some of the copied graphs reference data on the original sheets.
This is the result for the non-active sheets.

Problem observed in:

LibreOffice Portable 3.4.3 - OOO340m1 (Build:302)
LibreOffice 3.4.2 - OOO340m1 (Build:1206) on openSuSE 11.4 (32-bit)
Comment 1 Stephan van den Akker 2011-12-21 04:00:55 UTC
Problem getting worse (!) in:

LOdev 3.5.0 
Build ID: 7362ca8-b5a8e65-af86909-d471f98-61464c4
tested on openSuSE 11.4 (32-bit)

Now both copies contain graphs that reference the original sheets.

Copying one sheet with graph at a time works as expected (copied graph references copied sheet).
Comment 2 sasha.libreoffice 2012-04-26 05:27:44 UTC
Thanks for bugreport
When I copy sheet1, appears sheet1_1 where diagram points to sheet1, but when I delete sheet1, it points to sheet1_1. Strange.
Comment 3 Cor Nouws 2012-05-10 06:11:35 UTC
(In reply to comment #1)

> Copying one sheet with graph at a time works as expected (copied graph
> references copied sheet).

Ah well, in daily version 3.6.0alpha0+ Build ID: 5c2c03e this results in a diagram with a separate data table, and not a reference to the data on the sheet..
Comment 4 Cor Nouws 2012-10-22 09:08:16 UTC
@stephan: this was correct in version 3.3.x ?
Then we should set as regression.
And also add it to a MAB, in my opinion.
OK?
Comment 5 Stephan van den Akker 2012-10-22 10:24:40 UTC
Very relieved to see the extremely serious problem in comment 1 and 3 tackled.

The problem in the original description never worked properly in OOo and LibO, so it can't be considered a regression.

In OOo 3.3.0 it started working correctly when copying 1 sheet at a time.

In my opinion it definitely is Most Annoying, so it qualifies for the MAB list.
Comment 6 sasha.libreoffice 2012-10-22 10:36:46 UTC
Reproduced in LO 3.4.0, 3.3.2, OO 3.2.1 on Mandriva 2010 64 bit
Steps to reproduce:
open attachment, select both sheet, copy, change cell B5 on First sheet for testing to which sheet points chart.
What is interesting:
delete first two sheet (two copied remains)
undo deletion
Now all 4 sheets point to first sheet (change cell B5 on first sheet to verify)

So, no regression
Comment 7 Cor Nouws 2012-10-22 11:15:45 UTC Comment hidden (obsolete)
Comment 8 Stephan van den Akker 2012-10-22 11:31:05 UTC
On your question of related Issues:

Although not involving charts, very probably technically related is:

https://bugs.freedesktop.org/show_bug.cgi?id=47662
EDITING: Inconsistent / unintuitive reference link handling for check boxes
and other form fields when Copying Sheets

IMHO The whole issue of how Calc deals with charts, form fields etc. on
sheets needs some serious attention. I imagine that the dev's time will be
spend most effectively when they deal with these issues simultaneously.

On your question of the reasonable work around.

Yes, this work around is do-able. Still annoying: Yet another work around
when doing work with LO.
Comment 9 Cor Nouws 2012-10-22 12:14:47 UTC Comment hidden (obsolete)
Comment 10 Stephan van den Akker 2013-05-31 19:37:22 UTC Comment hidden (obsolete)
Comment 11 Stephan van den Akker 2013-11-14 20:01:17 UTC
*** Bug 51702 has been marked as a duplicate of this bug. ***
Comment 12 Stephan van den Akker 2014-07-12 08:24:02 UTC Comment hidden (obsolete)
Comment 13 QA Administrators 2015-07-18 17:43:23 UTC Comment hidden (obsolete)
Comment 14 Stephan van den Akker 2015-07-18 18:57:36 UTC Comment hidden (obsolete)
Comment 15 ryecomp 2016-06-16 02:03:45 UTC
When copying sheet containing a chart, for example, if i copied sheet 'A' to sheet 'B', all data reference about A must be replaced with B in new sheet.

But chart still refers to 'A' sheet data.

Is there any way to fix this libreoffice bug ?

I am using libreoffice 4.2.8.2 under Ubuntu 14.04.4 LTS
Comment 16 m.a.riosv 2016-10-01 21:31:50 UTC
*** Bug 102910 has been marked as a duplicate of this bug. ***
Comment 17 m.a.riosv 2016-10-01 21:33:38 UTC
*** Bug 102057 has been marked as a duplicate of this bug. ***
Comment 18 snowboard975 2017-03-30 08:54:29 UTC
Reproducible on Windows 7 (64bit)

Version: 5.2.6.2 (x64)
Build ID: a3100ed2409ebf1c212f5048fbe377c281438fdc
Comment 19 snowboard975 2017-03-30 09:07:09 UTC Comment hidden (no-value)
Comment 20 Stephan van den Akker 2017-03-30 10:03:16 UTC
(In reply to snowboard975 from comment #19)
> This bug looks like a quite fundamental problem in the internal process of
> references of LiberOffice Calc. 
> It degrades the reputation of LibreOffice Calc severely in my opinion. 
> This bug has been reported six years ago, and still its state is 'not
> assigned'. 
> I see the importance of this bug is marked as medium (major). 
> Shouldn't its priority be changed to a higher one?
Thank you for this endorsement.

Note however that:
MS Excel 2007 and 2010 exhibit the exact same behavior;
Gnumeric 1.12.23 seems to lack the ability to copy more than 1 sheet at the time.

So, saying it degrades the reputation of Calc may be a bit too strong.... 

It may help though if you add yourself to the cc list. This may influence the priorities of the devs.
Comment 21 snowboard975 2017-03-30 11:14:53 UTC
(In reply to Stephan van den Akker from comment #20)
> (In reply to snowboard975 from comment #19)
> > This bug looks like a quite fundamental problem in the internal process of
> > references of LiberOffice Calc. 
> > It degrades the reputation of LibreOffice Calc severely in my opinion. 
> > This bug has been reported six years ago, and still its state is 'not
> > assigned'. 
> > I see the importance of this bug is marked as medium (major). 
> > Shouldn't its priority be changed to a higher one?
> Thank you for this endorsement.
> 
> Note however that:
> MS Excel 2007 and 2010 exhibit the exact same behavior;
> Gnumeric 1.12.23 seems to lack the ability to copy more than 1 sheet at the
> time.
> 
> So, saying it degrades the reputation of Calc may be a bit too strong.... 
> 
> It may help though if you add yourself to the cc list. This may influence
> the priorities of the devs.

This bug not only happens when copying two sheets together, but also happens when copying one sheet only. 
You marked it as duplicate bug as in comment #17.
I tested your example worksheet with Excel 2017, but the same problem does not occur in Excel 2017.
Comment 22 snowboard975 2017-03-30 11:18:13 UTC Comment hidden (obsolete)
Comment 23 snowboard975 2017-03-30 12:40:54 UTC Comment hidden (obsolete)
Comment 24 Stephan van den Akker 2017-03-30 13:14:27 UTC Comment hidden (obsolete)
Comment 25 snowboard975 2017-03-30 15:21:49 UTC Comment hidden (obsolete)
Comment 26 QA Administrators 2018-05-11 02:32:14 UTC Comment hidden (obsolete)
Comment 27 Stephan van den Akker 2018-05-11 17:41:27 UTC
The worse behavior mentioned in comment 1 is still present in:

Version: 6.0.4.1
Build ID: 00m0(Build:1)
CPU threads: 8; OS: Linux 4.16; UI render: default; VCL: gtk3; 
Locale: nl-NL (en_GB.UTF-8); Calc: group

Master is an improvement, but the OP is still present in master from the first week of May 2018:

Version: 6.1.0.0.alpha1+
Build ID: 25c0988b87b71f93577837f4a300f5f17366d145
CPU threads: 8; OS: Linux 4.16; UI render: default; VCL: gtk3; 
Locale: en-GB (en_GB.UTF-8); Calc: CL

On a side note: Both of the tested versions make it much harder to copy multiple sheets. Right-click of one of the selected sheets now un-selects it. Copying in 6.0.4.1 and in master only works with Ctrl-drag-and-drop.
Comment 28 QA Administrators 2019-05-12 02:59:21 UTC Comment hidden (obsolete)
Comment 29 Stephan van den Akker 2020-01-10 14:24:50 UTC Comment hidden (obsolete)
Comment 30 m.a.riosv 2020-12-23 22:37:41 UTC
*** Bug 139190 has been marked as a duplicate of this bug. ***
Comment 31 Commit Notification 2021-03-02 06:21:54 UTC
Ouyang Leyan committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/3078044653f1e1b0a055c47eeb8d27834c07268a

tdf#43175: Adapt ranges when copying or moving several sheets

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 32 Stephan van den Akker 2021-03-02 12:28:39 UTC
I can confirm this is fixed in:

Version: 7.2.0.0.alpha0+ / LibreOffice Community
Build ID: 3fe274dcfeca17ee7713b306fcd36183f6cf4689
CPU threads: 16; OS: Linux 5.10; UI render: default; VCL: kf5
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Calc: threaded

While still present in:

Version: 7.1.0.3 / LibreOffice Community
Build ID: 10(Build:3)
CPU threads: 16; OS: Linux 5.10; UI render: default; VCL: kf5
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Calc: threaded

Good work, Ouyang Leyan!
Comment 33 Commit Notification 2021-03-02 14:37:58 UTC
Noel committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/a2d5b5224aff4358865e73c3c0f67d47ce4b9b99

Revert "tdf#43175: Adapt ranges when copying or moving several sheets"

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 34 Stephan van den Akker 2021-03-04 18:18:34 UTC
I can confirm the problem is back in:

Version: 7.2.0.0.alpha0+ / LibreOffice Community
Build ID: a9f82c7c6ac645dd9263b435dbc7695951a70098
CPU threads: 16; OS: Linux 5.10; UI render: default; VCL: kf5
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Calc: threaded

Not sure I feel the need to thank anyone now....
Comment 35 Xisco Faulí 2021-05-03 07:24:09 UTC
@Andreas Heinisch, I thought you might be interested in this issue...
Comment 36 Andreas Heinisch 2021-05-03 15:53:27 UTC
I can reproduce the error in comment 1, but copying a single sheet works for me in:
Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: ff673ce838a5538b1432daf9007c047f6455a5ba
CPU threads: 6; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: de-DE (de_DE); UI: en-US
Calc: CL

Do I miss any step in order to reproduce the error with a single sheet?
For the other problem, I will have a look.
Comment 37 Andreas Heinisch 2021-05-03 15:58:58 UTC
A note on comment 27: if you select both of the sheets and make a right click anywhere besides the selected sheets, you can simply choose your preferred action.
Comment 38 Commit Notification 2021-05-09 07:52:28 UTC
Andreas Heinisch committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/d55bf8aab008d84fac673f3cd907f5199347ce77

tdf#43175 - Adjust chart references on every copied sheet

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 39 Commit Notification 2021-05-09 13:24:44 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/55fc9ea0f40fced82d71379b535fc0c4a24061b0

tdf#43175: sc: Add UItest

It will be available in 7.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 40 Commit Notification 2021-05-10 07:25:56 UTC
Andreas Heinisch committed a patch related to this issue.
It has been pushed to "libreoffice-7-1":

https://git.libreoffice.org/core/commit/b2fc048cb2d5f5bd1095a8110fa4a16a305a8acc

tdf#43175 - Adjust chart references on every copied sheet

It will be available in 7.1.4.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 41 Stephan van den Akker 2021-05-10 19:19:19 UTC
(In reply to Commit Notification from comment #38)

Confirmed fixed in:
Version: 7.2.0.0.alpha0+ / LibreOffice Community
Build ID: 5295bc11e41166258fde5dc57b60f9677b5956e1
CPU threads: 8; OS: Linux 5.12; UI render: default; VCL: kf5 (cairo+xcb)
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Calc: CL
Comment 42 Stephan van den Akker 2021-05-10 21:04:47 UTC
(In reply to Commit Notification from comment #40)

Problem observed in:
Version: 7.1.3.2 / LibreOffice Community
Build ID: 10(Build:2)
CPU threads: 8; OS: Linux 5.12; UI render: default; VCL: kf5
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Calc: threaded

Confirmed fixed in:
Version: 7.1.4.0.0+ / LibreOffice Community
Build ID: a67a30227ef32ab9fc3026bb19e48d4447df25d1
CPU threads: 8; OS: Linux 5.12; UI render: default; VCL: kf5
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Calc: CL

The commit in comment #40 indeed fixes the problem in the 7.1 branch.