Bug 121260

Summary:	FILESAVE: Using "Excel A1" formula syntax in LibO Calc options results in charts losing their link to source data if the worksheet is saved in .xlsx format
Product:	LibreOffice	Reporter:	Massimo Mula <mulamax>
Component:	Calc	Assignee:	Eike Rathke <erack>
Status:	RESOLVED FIXED
Severity:	normal	CC:	himajin100000, mulamax
Priority:	medium
Version:	Master old -3.6
Hardware:	All
OS:	All
Whiteboard:	target:6.2.0 target:6.1.4 target:7.3.0
Crash report or crash signature:		Regression By:

Description Massimo Mula 2018-11-07 23:09:53 UTC

If the formula syntax is set to "Excel A1" and the worksheet is saved as "Microsoft Excel 2007-2013 XML (*.xlsx)", all charts contained in the spreadsheet lose their link to the source data: a data table is created for them instead, preventing the user to dynamically update the chart by updating the originally pointed cells.


Reproducibility: always

STEPS TO REPRODUCE

 - Open an empty spreadsheet and create a new chart of any type (i.e. a Line chart). Make sure that options concerning Data Range and data series are properly set (Series Name, Y Values, Categories, etc.)

 - Open Tools -> Options -> LibreOffice Calc -> Formula menu; In Formula Options, set the "Formula syntax" option to "Excel A1"

 - Save the spreadsheet as "Microsoft Excel 2007-2013 XML (*.xlsx);

 - Close LibreOffice Calc;

 - Reopen in LibreOffice Calc the document just saved;

 - Right-click the chart and select "Edit" from the context menu;

 - Right click the chart again. 

Notice that "Data range" option is not present; "Data table" option is displayed instead. The chart is not linked to the original cells anymore.

The issue has also been discussed here: https://ask.libreoffice.org/en/question/89063/calc-replacing-all-chart-data-ranges-with-data-tables/

Kind regards,
Massimo

Comment 1 Eike Rathke 2018-11-09 17:21:29 UTC

Could reproduce also on Linux.
Culprit seems to be a wrong sheet reference in
<c:chart>...<c:val><c:numRef> e.g.

  <c:f>sheet1 $A$1:$A$3</c:f>

instead of

  <c:f>Sheet1!$A$1:$A$3</c:f>

Comment 2 Eike Rathke 2018-11-14 17:40:31 UTC

Taking.

Comment 3 Commit Notification 2018-11-15 15:07:01 UTC

Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/7997c12d01e03a719d9aa262cc55bc415b592ff7%5E%21

Resolves: tdf#121260 do not force AddressConvention::OOO on parseFormula()

It will be available in 6.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 4 Eike Rathke 2018-11-15 15:09:00 UTC

Pending review https://gerrit.libreoffice.org/63428 for 6-1

Comment 5 Commit Notification 2018-11-16 12:20:23 UTC

Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

https://git.libreoffice.org/core/+/277e9c5159635d131502372ff4f9cf719e7d3845%5E%21

Resolves: tdf#121260 do not force AddressConvention::OOO on parseFormula()

It will be available in 6.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 6 Commit Notification 2021-11-05 20:03:23 UTC

Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/7f5348e47d56a16c2691d0dffa45bbaf970092ca

tdf#121260: sc_subsequent_export_test2: Add unittest

It will be available in 7.3.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.