Unable to save wb with 3D bar chart from openpyxl example

Issue #1237 resolved
jeremy turner created an issue

Hello,

I have been using openpyxl to edit xlsx files that have charts. I recently came across a file that has a 3D Bar Chart in it. When i tried to save the workbook, openpyxl gave me an error:

expected <class 'openpyxl.chart._3d.View3D'>

Looking into this i noticed that chart.view3D was of type:

openpyxl.descriptors.base.Typed

I thought maybe this was an issue with the file i was working on, so i looked up how to use 3d bar charts in the openpyxl docs. I followed the instructions on how to build a simple 3D bar chart and, when trying to save that test workbook, i got the same error:

expected <class 'openpyxl.chart._3d.View3D'>

is there a solution for this issue or a ticket already submitted?

My current working solution is to copy the series, category, title,legend, etc from the 3D bar chart and copy them into 2d bar chart object that i use to replace the 3d bar chart.

I have attached the test 3d bar chart file that simply load and then try to save. Here is the code for the test 3d bar chart that produces the error:

from openpyxl import Workbook
from openpyxl.chart import (
    Reference,
    Series,
    BarChart3D,
)

wb = Workbook()
ws = wb.active

rows = [
    (None, 2013, 2014),
    ("Apples", 5, 4),
    ("Oranges", 6, 2),
    ("Pears", 8, 3)
]

for row in rows:
    ws.append(row)

data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=4)
titles = Reference(ws, min_col=1, min_row=2, max_row=4)
chart = BarChart3D()
chart.title = "3D Bar Chart"
chart.add_data(data=data, titles_from_data=True)
chart.set_categories(titles)

ws.add_chart(chart, "E5")
wb.save("bar3d.xlsx")

Comments (7)

  1. jeremy turner reporter

    sorry, it doesnt seem that my copy and paste of the code pasted correctly, but i did hyperlink to the correct code i used.

  2. CharlieC

    Thanks for the report and the sample file. I suspect openpyxl is having trouble reading the source of the chart and mapping it to the correct openpyxl class.

  3. Aldo Polli

    same issue here

    I add this to the code and now it works

    from openpyxl.chart._3d import (
        View3D,
        Surface,
    )    
    ...
    
        chart.view3D = View3D()
        chart.floor = Surface()
        chart.sideWall = Surface()
        chart.backWall = Surface()
    ...
    
  4. Mitch LeBlanc

    Confirmed the bug (3D sample chart in the docs won’t save) and @Aldo Polli 's fix (thank you!) worked for me. Python 3.7.2 and openpyxl 2.6.2.

  5. CharlieC

    I’ve fixed the basic problem with 3D charts and added a test which should pick it up. But there’s still a problem with read/writing 3D charts due to Excel objecting a series axis.

  6. Log in to comment