2.2 openpyxl 3D图表

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

# wb = Workbook(write_only=True)
wb = openpyxl.load_workbook(filename=r'./办公自动化/files/test.xlsx')

# 获取当前所有的sheet名称
sheet_names = wb.sheetnames
print(f"Original sheet names: {sheet_names}")

# 假设我们要修改第4个sheet的名称为"areaChart3D"
ws = wb[sheet_names[3]]

ws.title = 'areaChart3D'

rows = [
    ['团队名称', 'Q1', 'Q2', 'Q3'],
    ['精英队', 1200, 1800, 2200],
    ['王者队', 1500, 2000, 2500],
    ['野战队', 1000, 2200, 3000],
    ['虎狼队', 1100, 1650, 2550],
    ['战狼队', 1150, 1700, 2650],
    ['金牌队', 1200, 1950, 3150],
    ['无敌队', 1050, 1700, 2730]
]

ws.delete_rows(1, ws.max_row)

for row in rows:
    ws.append(row)

chart = AreaChart3D()
chart.title = '各团队每季度销售业绩3D对比图（单位：万元）'
chart.style = 10
chart.x_axis.title = 'Team name'  # 团队名称
chart.y_axis.title = 'Sales volume'  # 销售业绩
# chart.z_axis.title = 'quarter'  # 季度
# chart.y_axis.scaling.min = 0  # y轴最小值
# chart.y_axis.majorUnit = 500  # 间距
# chart.y_axis.scaling.max = 3500  # y轴最大值
chart.width = 20  # 默认15
chart.height = 13  # 默认7.5
chart.legend = None  # 颜色区域说明

cats = Reference(ws, min_col=1, min_row=1, max_row=8)
data = Reference(ws, min_col=2, max_col=4, min_row=1, max_row=8)
chart.set_categories(cats)
chart.add_data(data, titles_from_data=True)
ws.add_chart(chart, 'F1')

# 保存工作簿
wb.save('./办公自动化/files/test.xlsx')