Bug 137734 - FILEOPEN: Bar Chart in xlsx document created by openpyxl (python) is displayed wrong
Summary: FILEOPEN: Bar Chart in xlsx document created by openpyxl (python) is displaye...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.1.5.2 release
Hardware: All All
: medium normal
Assignee: Tünde Tóth
URL:
Whiteboard: target:7.1.0
Keywords:
Depends on:
Blocks: OOXML-Chart
  Show dependency treegraph
 
Reported: 2020-10-25 14:26 UTC by h5sk2n
Modified: 2020-11-11 16:45 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Microsoft vs OpenOffce vs LibreOffice (64.26 KB, image/png)
2020-10-25 14:42 UTC, h5sk2n
Details
Barchart.xlsx (6.35 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-10-26 13:45 UTC, Kevin Suo
Details
Barchart_Version7.1.0.0.alpha1 (35.40 KB, image/png)
2020-11-04 19:56 UTC, h5sk2n
Details

Note You need to log in before you can comment on or make changes to this bug.
Description h5sk2n 2020-10-25 14:26:55 UTC
Description:
Charts in .xlsx files created with python via openpyxl (https://openpyxl.readthedocs.io/en/stable/) are displayed wrong.

Steps to Reproduce:
1. Create .xlsx file using python. for example:

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
for i in range(10):
    ws.append([i])

# drawing a graph
values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()
ws.add_chart(chart, "A15")
chart.title = "Chart"
chart.y_axis.title = 'Size'
chart.x_axis.title = 'Test Number'
chart.add_data(values)

wb.save("Barchart.xlsx")

2. open "Barchart.xlsx" with Microsoft Excel
3. open "Barchart.xlsx" with LibreOffice Calc and see what's wrong

Actual Results:
The representation of the chart is wrong in LO Calc

Expected Results:
It should look like opened with MS Excel


Reproducible: Always


User Profile Reset: No



Additional Info:
I tested this with different Versions up to 7.0.2
Comment 1 h5sk2n 2020-10-25 14:42:54 UTC
Created attachment 166699 [details]
Microsoft vs OpenOffce vs LibreOffice

The representation of the chart in different Office Suites:
MS Excel (left) - OpenOffice Calc (middle) - LibreOffice Calc (right)
MS and OO can show the chart properly, LO can't
Comment 2 Kevin Suo 2020-10-26 13:44:06 UTC
Reproduced with
Version: 7.0.3.0.0+
Build ID: a9bad3ef281c825bf57437a1b5154e413d7f18cd
CPU threads: 4; OS: Linux 5.8; UI render: default; VCL: gtk3
Locale: zh-CN (zh_CN.UTF-8); 界面: zh-CN
Calc: threaded

Set to NEW.
Comment 3 Kevin Suo 2020-10-26 13:45:21 UTC
Created attachment 166737 [details]
Barchart.xlsx

This is the bar chart created with the python script as suggested by the bug reporter.
Comment 4 NISZ LibreOffice Team 2020-10-26 15:23:56 UTC
The differently colored data points happen because of the c:varyColors property is missing from the generated file.

Opening this in Excel 2013 and saving it saves the tag as <c:varyColors val="0"/>
so the default value - if omitted - must be 0.

Calc opens the Excel-saved version without differently colored data points.

So the default value - if omitted - must be changed to 0, since currently Calc assumes a value of 1.

Another problem with the example file is the horizontal (category) axis label being incorrectly rotated; we already have bug #126133 about that.
Comment 5 Commit Notification 2020-10-30 14:41:17 UTC
Tünde Tóth committed a patch related to this issue.
It has been pushed to "master":

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

tdf#137734 Chart OOXML import: fix variable color charts

It will be available in 7.1.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 6 h5sk2n 2020-11-04 19:56:10 UTC
Created attachment 167005 [details]
Barchart_Version7.1.0.0.alpha1

Seems to work now.
Comment 7 NISZ LibreOffice Team 2020-11-11 16:45:38 UTC
Verified in:

Version: 7.1.0.0.alpha1+ (x64)
Build ID: a689cf1672e89cb78fbcfa14c505850927e71f58
CPU threads: 4; OS: Windows 10.0 Build 17134; UI render: Skia/Raster; VCL: win
Locale: hu-HU (hu_HU); UI: hu-HU
Calc: threaded

Thanks for fixing!