Bug 94502 - FILESAVE: Distorted and unusable export of stacked bar chart to Excel .XLSX
Summary: FILESAVE: Distorted and unusable export of stacked bar chart to Excel .XLSX
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Chart (show other bugs)
Version:
(earliest affected)
4.0.0.3 release
Hardware: Other All
: medium normal
Assignee: Balázs Varga
URL:
Whiteboard: target:6.2.0 target:6.1.1
Keywords:
Depends on:
Blocks: OOXML-Chart
  Show dependency treegraph
 
Reported: 2015-09-25 06:11 UTC by Gerry
Modified: 2018-09-11 08:42 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Calc's original file in .ods format. No problem here. (15.10 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-09-25 06:12 UTC, Gerry
Details
Exported file to .xlsx is distorted. Opened in Excel 2010 (9.43 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-09-25 06:13 UTC, Gerry
Details
Export to .xls is correct, but gives an error message afterwards (12.50 KB, application/vnd.ms-excel)
2015-09-25 06:13 UTC, Gerry
Details
Screenshot-Calc_Stacked-bar-chart_Everything-okay-here.png (117.86 KB, image/png)
2015-09-25 06:17 UTC, Gerry
Details
Screenshot-Excel_Stacked-bar-chart_xlsx-Distorted.png (123.55 KB, image/png)
2015-09-25 06:18 UTC, Gerry
Details
Distorted-chart-in-Excel-2016.png (63.75 KB, image/png)
2018-05-11 19:26 UTC, Gerry
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gerry 2015-09-25 06:11:09 UTC
I created a simple stacked bar chart in Calc. The export to .XLSX is distorted and not usable. See attached screenshots of Calc and Excel.

If I export to .xls, Excel 2010 imports it correctly, but gives an error message afterwards. If I simply open the .ods in Excel, it is imported absolutely correctly. Hence, there must be an issue in the .xlsx filter. All three files are attached.
Comment 1 Gerry 2015-09-25 06:12:19 UTC
Created attachment 119008 [details]
Calc's original file in .ods format. No problem here.
Comment 2 Gerry 2015-09-25 06:13:01 UTC
Created attachment 119009 [details]
Exported file to .xlsx is distorted. Opened in Excel 2010
Comment 3 Gerry 2015-09-25 06:13:47 UTC
Created attachment 119010 [details]
Export to .xls is correct, but gives an error message afterwards
Comment 4 Gerry 2015-09-25 06:17:26 UTC
Created attachment 119011 [details]
Screenshot-Calc_Stacked-bar-chart_Everything-okay-here.png
Comment 5 Gerry 2015-09-25 06:18:03 UTC
Created attachment 119012 [details]
Screenshot-Excel_Stacked-bar-chart_xlsx-Distorted.png
Comment 6 raal 2015-09-25 07:45:12 UTC
I can confirm with LO 5.0.1.2, win7 and Version 4.0.0.3 (Build ID: 7545bee9c2a0782548772a21bc84a9dcc583b89)
Comment 7 Katarina Behrens (Inactive) 2016-01-11 10:16:11 UTC
Workaround:

If you know how to unzip, edit and re-zip OOXML files, open xl/charts/chart1.xml file in OOXML zip archive and find the following tag:

<c:overlap val="0"/>

Replace 0 with 100, save and re-zip.

If you don't, go to Format data series > Options tab > Settings and change overlap value from 0% to 100% and save. Due to bug 96959, it will have no effect on rendering the chart in LO, but it'll workaround xlsx export issue so that the chart is rendered correctly (albeit not perfect) in Excel
Comment 8 QA Administrators 2017-03-06 13:46:50 UTC Comment hidden (obsolete)
Comment 9 Gerry 2017-03-06 17:47:03 UTC Comment hidden (obsolete)
Comment 10 QA Administrators 2018-05-11 02:32:28 UTC Comment hidden (obsolete)
Comment 11 Gerry 2018-05-11 19:26:33 UTC
Created attachment 142038 [details]
Distorted-chart-in-Excel-2016.png

Bug still exists in LibreOffice Version: 6.0.4.2. 
Attachment shows the distorted chart when I open it in Excel 2016


System:
Version: 6.0.4.2
Build-ID: 1:6.0.4~rc2-0ubuntu0.16.04.1
CPU-Threads: 4; BS: Linux 4.4; UI-Render: Standard; VCL: gtk2; 
Gebietsschema: en-GB (de_DE.UTF-8); Calc: group
Comment 12 Gabor Kelemen (allotropia) 2018-08-09 15:48:32 UTC
@Balazs, maybe one for you? We seem to know what needs to be set here, see comment #7
Comment 13 Commit Notification 2018-08-23 11:46:21 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=17c2b0af97f73b9103be982b15d7111b3980e027

tdf#94502 Fix overlap export of Stacked Bar Chart to *.xlsx

It will be available in 6.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 14 Commit Notification 2018-08-23 14:40:51 UTC
Balazs Varga committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8a7062c02c54330c3b6be0471dbc8a74e60aa479&h=libreoffice-6-1

tdf#94502 Fix overlap export of Stacked Bar Chart to *.xlsx

It will be available in 6.1.1.

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

Affected users are encouraged to test the fix and report feedback.