使用openpyxl无法修改百分比堆积柱状图样式，如何解决？

当前样式 #

目标样式 #

使用openpyxl生成百分比堆积柱状图时，实际得到当前样式，希望达到目标样式。尝试修改chart.style数值无效，环境：Windows 10 + Python 3.10（VS 2017）+ Excel 365

原代码：

def chart_add_A(filename):
    workbook = load_workbook(filename)
    worksheet = workbook['A']
    
    chart_data = Reference(worksheet, min_row = 2, max_row = worksheet.max_row, min_col = 2, max_col = 3)
    chart_series = Reference(worksheet, min_row = 2, max_row = worksheet.max_row, min_col = 1, max_col = 1)
    chart_A = BarChart()
    chart_A.type = 'col'
    chart_A.style = 3
    chart_A.grouping = "percentStacked"
    chart_A.title = 'A'
    chart_A.x_axis.title = 'Month'
    chart_A.y_axis.title = 'Count'
    chart_A.legend = None
    chart_A.dLbls=label.DataLabelList()
    chart_A.dLbls.showVal=True
    chart_A.showVal = True
    chart_A.width = 24
    chart_A.height = 12
    chart_A.add_data(chart_data)
    chart_A.set_categories(chart_series)
    worksheet.add_chart(chart_A, 'A0')
    workbook.save(filename)

修改后的代码及关键调整点 #

from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.chart.label import DataLabelList

def chart_add_A(filename):
    workbook = load_workbook(filename)
    worksheet = workbook['A']
    
    chart_data = Reference(worksheet, min_row=2, max_row=worksheet.max_row, min_col=2, max_col=3)
    chart_series = Reference(worksheet, min_row=2, max_row=worksheet.max_row, min_col=1, max_col=1)
    chart_A = BarChart()
    chart_A.type = 'col'
    chart_A.style = 3
    chart_A.grouping = "percentStacked"
    chart_A.title = 'A'
    chart_A.x_axis.title = 'Month'
    # 1. 调整Y轴标题为百分比，设置格式为百分比显示
    chart_A.y_axis.title = 'Percentage'
    chart_A.y_axis.number_format = "0%"
    chart_A.legend = None
    
    # 2. 设置数据标签在柱子内部居中
    chart_A.dLbls = DataLabelList()
    chart_A.dLbls.showVal = True
    chart_A.dLbls.position = "ctr"
    
    chart_A.width = 24
    chart_A.height = 12
    chart_A.add_data(chart_data)
    chart_A.set_categories(chart_series)
    
    # 3. 为每个系列设置目标样式的填充色（可根据实际需求调整RGB值）
    series1 = chart_A.series[0]
    series1.graphicalProperties.solidFill = "FF638EC6"
    series2 = chart_A.series[1]
    series2.graphicalProperties.solidFill = "FFE67E22"
    
    # 4. 修正单元格位置：Excel无A0单元格，改为A1
    worksheet.add_chart(chart_A, 'A1')
    workbook.save(filename)

核心调整说明 #

1. Y轴百分比格式：通过chart_A.y_axis.number_format = "0%"将数值转换为百分比显示，匹配目标样式的Y轴逻辑。
2. 数据标签位置：dLbls.position = "ctr"让标签固定在柱子内部居中，替代默认的外部显示。
3. 系列颜色定制：直接为每个系列指定RGB填充色，可通过取色器提取目标样式的精确颜色值。
4. 单元格位置修正：Excel不存在A0单元格，改为A1避免保存报错。

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