Python批量创建AreaChart3D图表及动态布局、深度设置问题

需求与问题 #

• 需要根据工具输出的可变数量表格（1-100个，包含Location、Speed和时间维度），在Excel的第二个工作表（ws2_obj）中生成对应数量的AreaChart3D图表
• 当前代码仅能生成单个图表，批量生成时遇到两个核心问题：
  • 动态指定图表放置的单元格位置时，直接使用cell对象无效，用正则提取地址的方式不够简洁可靠
  • 不知道如何通过Python设置**Depth (% of base)**参数

现有代码及尝试过程 #

初始单图表代码 #

from openpyxl.chart import (
    AreaChart3D,
    Reference,
)
import openpyxl as xl

wb_obj = xl.load_workbook('Plots.xlsx')
ws_obj = wb_obj.active
ws2_obj = wb_obj.create_sheet("Graphs")
c1 = AreaChart3D()
c1.legend = None
c1.style = 15    
cats = Reference(ws_obj, min_col=1, min_row=7, max_row=200)
data = Reference(ws_obj, min_col=2, min_row=6, max_col=8, max_row=200)
c1.add_data(data, titles_from_data=True)
c1.set_categories(cats)
ws2_obj.add_chart(c1, "A1")
wb_obj.save("Plots.xlsx")

批量生成的第一版尝试（固定位置导致重叠） #

step = 8  # 需根据实际数据列间隔定义
for i in range(1, 4):
    c1 = AreaChart3D()
    cats = Reference(ws_obj, min_col=1, min_row=7, max_row=200)
    data = Reference(ws_obj, min_col=2, min_row=6, max_col=i * int(step), max_row=200)
    c1.title = ws_obj.cell(row=1, column=i * int(step)).value
    c1.legend = None
    c1.style = 15
    c1.y_axis.title = 'Fire Time'
    c1.x_axis.title = 'Temperature'
    c1.z_axis.title = "Velocity"
    c1.add_data(data, titles_from_data=True)
    c1.set_categories(cats)
    ws2_obj.add_chart(c1, "A2")  # 固定位置导致图表重叠覆盖

正则提取单元格地址的尝试 #

import re
step = 8  # 需定义step值
my_cells = []
for i in range(1, 4):
    my_cell = ws2_obj.cell(row=1, column=i * int(step) - (int(step) - 1))
    my_cells.append(my_cell)
print("My_Cell:", my_cells)
new_cells = []
for i in my_cells:
    new_cells.append(re.findall("\W\w\d", str(i)))
new_new_cells = []
for i in new_cells:
    new_new_cells.append(i[0])
print("new_new_cells:", new_new_cells)
final_list = [re.sub('[^a-zA-Z0-9]+', '', _) for _ in new_new_cells]
print("final list:", final_list)

结合地址列表的循环代码 #

step = 8  # 需定义step值
for i in range(1, 4):
    c1 = AreaChart3D()
    cats = Reference(ws_obj, min_col=1, min_row=7, max_row=255)
    data = Reference(ws_obj, min_col=2, min_row=6, max_col=i * int(step), max_row=255)
    c1.title = ws_obj.cell(row=1, column=i * int(step)).value
    c1.legend = None
    c1.style = 20
    c1.y_axis.title = 'Time'
    c1.x_axis.title = 'Location'
    c1.z_axis.title = "Velocity"
    c1.add_data(data, titles_from_data=True)
    c1.set_categories(cats)
    c1.x_axis.scaling.max = 75
    c1.y_axis.scaling.max = 50
    c1.z_axis.scaling.max = 25
    ws2_obj.add_chart(c1, str(final_list[i - 1]))

优化解决方案 #

1. 简化图表位置的动态计算 #

不用正则提取单元格地址，直接利用openpyxl内置工具转换列号为Excel列字母，再拼接行号即可，逻辑更清晰可靠：

from openpyxl.utils import get_column_letter

def get_cell_address(row, col):
    return f"{get_column_letter(col)}{row}"

# 示例：按每行3个图表的规则排列，超出自动换行
chart_count = 10  # 实际数量根据表格总数确定
row = 1
col = 1
charts_per_row = 3
chart_width_cols = 8  # 每个图表占用的列宽度
chart_height_rows = 15  # 每个图表占用的行高度

for chart_idx in range(chart_count):
    # 计算当前图表的放置位置
    cell_address = get_cell_address(row, col)
    
    # 创建并配置图表
    c = AreaChart3D()
    c.legend = None
    c.style = 20
    # 补充数据引用、坐标轴标题等配置逻辑
    # ...
    
    ws2_obj.add_chart(c, cell_address)
    
    # 更新下一个图表的位置
    col += chart_width_cols
    if (chart_idx + 1) % charts_per_row == 0:
        col = 1
        row += chart_height_rows

2. 设置Depth (% of base)参数 #

在openpyxl中，3D图表的深度百分比可以直接通过chart.depthPercent属性设置，取值范围为0-200（对应0%到200%）：

c = AreaChart3D()
# 设置深度为基准高度的50%
c.depthPercent = 50

内容的提问来源于stack exchange，提问作者Iuli