Bug 114657 - FILESAVE: Data table (from copied chart) with empty row exported incorrectly to XLSX (Data Labels missing and X-Values wrong) -see Comment 6
Summary: FILESAVE: Data table (from copied chart) with empty row exported incorrectly ...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
5.3.1.2 release
Hardware: Other All
: medium minor
Assignee: Balázs Varga
URL:
Whiteboard: target:6.4.0 target:6.3.2
Keywords: regression
Depends on:
Blocks: OOXML-Chart
  Show dependency treegraph
 
Reported: 2017-12-22 21:50 UTC by Nathan Huebner
Modified: 2019-09-17 11:28 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
XLSX with working chart (before saving from LO) (10.68 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-12-22 21:55 UTC, Nathan Huebner
Details
XLSX after saving in LibreOffice as XLSX (breaks data series) (12.31 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-12-22 21:56 UTC, Nathan Huebner
Details
Screenshot of the exported ods and xlsx in Calc side by side (79.88 KB, image/png)
2019-07-12 12:20 UTC, Gabor Kelemen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Nathan Huebner 2017-12-22 21:50:49 UTC
Description:
LO Calc reads the chart correctly when first opening the XLSX file. But when performing "Save As XLSX", the Data Series formula references are corrupted. 

If there is a workaround, do you have any alternative ways in writing out the Data Series in a different way that may not become corrupted?

Steps to Reproduce:
1. Create an XLSX file using Microsoft Excel (2013+)

2. Create a chart using a Data Series to plot.

3. Assign the data series as similar:

  Name:        =Sheet1!$A$1
  Categories:  =Sheet1!$B$1
  X-Values:    =Sheet1!$A$2:$A$10
  Y-Values:    =Sheet1!$B$2:$B$10

4. Save the XLSX file in Microsoft Excel.

5. Open the XLSX file in LibreOffice Calc.

6. Notice that the series look correct, the chart looks normal.

7. In LibreOffice, "Save As New XLSX file".

8. Close the XLSX file.

9. Open the "New XLSX file" in Microsoft Excel.

Actual Results:  
1. Open the XLSX File saved in Libre Office, into Microsoft Excel.

2. Click the Chart.

3. Click the Filter icon to the right.

4. Mouse over the Series label in the list.

5. Click the icon to the right of the label in Series.

6. Notice the Data Series References now look like the following:

Name:        =sheet1 $A$1
Categories:  =sheet1 $B$1
X-Values:    =sheet1 $A$2:$A$10
Y-Values:    =sheet1 $B$2:$B$10


Expected Results:
The Data Series should remain unchanged.

  Name:        =Sheet1!$A$1
  Categories:  =Sheet1!$B$1
  X-Values:    =Sheet1!$A$2:$A$10
  Y-Values:    =Sheet1!$B$2:$B$10



Reproducible: Always


User Profile Reset: Yes



Additional Info:
I can provide file attachments if that helps.

Here's the output for the latest version of LibreOffice. Please note that I upgraded to LibreOffice 5.3.1, and the last version (2 months ago) had the same problem.

Upgrading did not fix the issue.

** (soffice:13694): CRITICAL **: void g_lo_menu_insert_section(GLOMenu*, gint, const gchar*, GMenuModel*): assertion 'G_IS_LO_MENU (menu)' failed

(soffice:13694): Gtk-CRITICAL **: gtk_menu_bar_new_from_model: assertion 'G_IS_MENU_MODEL (model)' failed

(soffice:13694): Gtk-CRITICAL **: gtk_widget_insert_action_group: assertion 'GTK_IS_WIDGET (widget)' failed

(soffice:13694): Gtk-CRITICAL **: gtk_widget_set_hexpand: assertion 'GTK_IS_WIDGET (widget)' failed

(soffice:13694): Gtk-CRITICAL **: gtk_grid_attach: assertion 'GTK_IS_WIDGET (child)' failed

(soffice:13694): GLib-GObject-WARNING **: invalid (NULL) pointer instance

(soffice:13694): GLib-GObject-CRITICAL **: g_signal_connect_data: assertion 'G_TYPE_CHECK_INSTANCE (instance)' failed

(soffice:13694): GLib-GObject-WARNING **: invalid (NULL) pointer instance

(soffice:13694): GLib-GObject-CRITICAL **: g_signal_connect_data: assertion 'G_TYPE_CHECK_INSTANCE (instance)' failed

** (soffice:13694): CRITICAL **: void g_lo_menu_insert_section(GLOMenu*, gint, const gchar*, GMenuModel*): assertion 'G_IS_LO_MENU (menu)' failed

(soffice:13694): Gtk-CRITICAL **: gtk_menu_bar_new_from_model: assertion 'G_IS_MENU_MODEL (model)' failed

(soffice:13694): Gtk-CRITICAL **: gtk_widget_insert_action_group: assertion 'GTK_IS_WIDGET (widget)' failed

(soffice:13694): Gtk-CRITICAL **: gtk_widget_set_hexpand: assertion 'GTK_IS_WIDGET (widget)' failed

(soffice:13694): Gtk-CRITICAL **: gtk_grid_attach: assertion 'GTK_IS_WIDGET (child)' failed

(soffice:13694): GLib-GObject-WARNING **: invalid (NULL) pointer instance

(soffice:13694): GLib-GObject-CRITICAL **: g_signal_connect_data: assertion 'G_TYPE_CHECK_INSTANCE (instance)' failed

(soffice:13694): GLib-GObject-WARNING **: invalid (NULL) pointer instance

(soffice:13694): GLib-GObject-CRITICAL **: g_signal_connect_data: assertion 'G_TYPE_CHECK_INSTANCE (instance)' failed
[13843:13843:1222/214903.577207:ERROR:zygote_host_impl_linux.cc(88)] Running as root without --no-sandbox is not supported. See https://crbug.com/638180.
[13853:13853:1222/214903.661560:ERROR:zygote_host_impl_linux.cc(88)] Running as root without --no-sandbox is not supported. See https://crbug.com/638180.
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: firefox: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: iceweasel: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: seamonkey: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: mozilla: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: epiphany: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: konqueror: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: chromium-browser: not found
[13877:13877:1222/214903.736524:ERROR:zygote_host_impl_linux.cc(88)] Running as root without --no-sandbox is not supported. See https://crbug.com/638180.
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: www-browser: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: links2: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: elinks: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: links: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: lynx: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: w3m: not found
xdg-open: no method available for opening 'http://help.libreoffice.org/swriter/sfx/ui/startcenter/open_all?Language=en-US&System=UNIX&Version=5.3'
root@secure-server:/var/www/vhosts/stage.mcedemo.com/storage/exports# [14009:14009:1222/214908.439797:ERROR:zygote_host_impl_linux.cc(88)] Running as root without --no-sandbox is not supported. See https://crbug.com/638180.
[14019:14019:1222/214908.504337:ERROR:zygote_host_impl_linux.cc(88)] Running as root without --no-sandbox is not supported. See https://crbug.com/638180.
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: firefox: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: iceweasel: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: seamonkey: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: mozilla: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: epiphany: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: konqueror: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: chromium-browser: not found
[14043:14043:1222/214908.574746:ERROR:zygote_host_impl_linux.cc(88)] Running as root without --no-sandbox is not supported. See https://crbug.com/638180.
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: www-browser: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: links2: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: elinks: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: links: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: lynx: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: w3m: not found
xdg-open: no method available for opening 'http://help.libreoffice.org/swriter/sfx/ui/startcenter/open_all?Language=en-US&System=UNIX&Version=5.3'
[14138:14138:1222/214909.290545:ERROR:zygote_host_impl_linux.cc(88)] Running as root without --no-sandbox is not supported. See https://crbug.com/638180.
[14148:14148:1222/214909.341600:ERROR:zygote_host_impl_linux.cc(88)] Running as root without --no-sandbox is not supported. See https://crbug.com/638180.
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: firefox: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: iceweasel: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: seamonkey: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: mozilla: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: epiphany: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: konqueror: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: chromium-browser: not found
[14172:14172:1222/214909.401990:ERROR:zygote_host_impl_linux.cc(88)] Running as root without --no-sandbox is not supported. See https://crbug.com/638180.
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: www-browser: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: links2: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: elinks: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: links: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: lynx: not found
/usr/bin/xdg-open: 771: /usr/bin/xdg-open: w3m: not found
xdg-open: no method available for opening 'http://help.libreoffice.org/swriter/sfx/ui/startcenter/open_all?Language=en-US&System=UNIX&Version=5.3'




User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.84 Safari/537.36
Comment 1 Nathan Huebner 2017-12-22 21:55:00 UTC
Created attachment 138602 [details]
XLSX with working chart (before saving from LO)

This is the XLSX file with the chart working in Excel and LibreOffice before it is saved in LibreOffice.
Comment 2 Nathan Huebner 2017-12-22 21:56:21 UTC
Created attachment 138603 [details]
XLSX after saving in LibreOffice as XLSX (breaks data series)

This is the XLSX file LibreOffice gave me after saving it. Opening the file again in LibreOffice shows the Data Series is broken as well, even though LibreOffice saved it.

So it saved it and became broken.
Comment 3 MM 2017-12-24 11:07:03 UTC
Unconfirmed on windows 7 x64 with
Version: 6.0.0.1
Build ID: d2bec56d7865f05a1003dc88449f2b0fdd85309a
CPU threads: 3; OS: Windows 6.1; UI render: default; 

Unconfirmed on ubuntu 16.04 x64 with

Version: 5.2.6.2
Build ID: a3100ed2409ebf1c212f5048fbe377c281438fdc
CPU Threads: 2; OS Version: Linux 4.4; UI Render: default; VCL: gtk2; 
Locale: en-US (en_US.UTF-8); Calc: single

and

Version: 6.1.0.0.alpha0+
Build ID: 99210a149c859fcd683870b280adaeeffd1250e4
CPU threads: 2; OS: Linux 4.4; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-12-17_23:57:18
Locale: en-US (en_US.UTF-8); Calc: threaded


Chart is there. Only the columns are a bit smaller after saving.
Comment 4 Nathan Huebner 2017-12-24 20:05:40 UTC Comment hidden (obsolete)
Comment 5 Buovjaga 2017-12-26 19:16:39 UTC Comment hidden (obsolete)
Comment 6 Timur 2017-12-27 18:14:20 UTC
Original attachment 138602 [details] XLSX has Data Ranges. Saving as XLSX keeps them in my test and MM noticed similar. 
Not clear how attachment 138603 [details] has Data Table. So I guess:

1. Open attachment 138602 [details] XLSX with Data Ranges
2. Note Data Range is wrong: $Summary.$A$5:$B$10;$Summary.$B$4 because A4 is missing but it doesn't affect result
3. Note Data Ranges has empty row, which will affect result
4. Copy chart to a new spreadsheet file (Bug 113572) and save as ODS and XLSX
5. Reopen new ODS and see that chart looks fine, data is in Data Table with Data Labels 1-6 and 5 X-Values and Y-values (not in 6th)
6. Reopen new XLSX and see that chart doesn't look fine because Data Table is wrong: no Data Labels and 6th X-Values is 1 and 6th Y-Values is empty

Obvious workaround is to restrict Data Range is original XLSX to avoid empty row. 
Solution is to fix xlsx export. 
So I'll confirm as I wrote and rename. I'll leave version 5.3 although there were some changes, 6th Y-Values was 0 which is also wrong. LO 5.2 was worse, didn't export Y-Values at all. So no regression, but it would be useful to see who worked on this. 
If it's not like that, please comment.
Comment 7 QA Administrators 2018-12-28 03:46:13 UTC Comment hidden (obsolete)
Comment 8 Gabor Kelemen 2019-07-12 12:20:49 UTC
Created attachment 152743 [details]
Screenshot of the exported ods and xlsx in Calc side by side

Data table contents are still messed up.

Version: 6.4.0.0.alpha0+ (x86)
Build ID: 49422a469646ad8be43ba828ca24c2484c26b9e8
CPU threads: 4; OS: Windows 6.3; UI render: GL; VCL: win; 
TinderBox: Win-x86@42, Branch:master, Time: 2019-07-08_00:50:19
Locale: hu-HU (hu_HU); UI-Language: en-US
Calc: CL
Comment 9 László Németh 2019-08-30 13:24:40 UTC
This is a regression from commit a211c754003f98bc8f7761224a0b265bd224f61f
"fdo77216-Charts-Scattered chart: Chart gets distorted on RT"
Comment 10 Commit Notification 2019-08-30 13:25:13 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/2bd8e41a0fc10974f81695c1b2e32dc07c569d97%5E%21

tdf#114657 OOXML chart export: fix broken chart with NaN X value

It will be available in 6.4.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 11 Commit Notification 2019-09-02 14:08:37 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

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

tdf#114657 OOXML chart export: fix broken chart with NaN X value

It will be available in 6.3.2.

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 12 Xisco Faulí 2019-09-17 11:28:45 UTC
Verified in

Version: 6.4.0.0.alpha0+
Build ID: c56bf1479cc71d1a2b0639f6383e90c1f7e3655b
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); UI-Language: en-US
Calc: threaded

@Balázs Varga, thanks for fixing this issue!