openpyxl怎么修改excel图表的数据源

openpyxl怎么修改excel图表的值，openpyxl怎么修改excel图表的数据源，openpyxl怎么修改excel chart的数据源。
python怎么修改excel图表的值，python怎么修改excel图表的数据源，python怎么修改excel chart的数据源。

下面我将为大家提供一种解决方案：

from openpyxl import load_workbook
from openpyxl.chart import Reference
from openpyxl.chart.data_source import NumRef, NumDataSource
from openpyxl.chart.series import XYSeries

def change_chart_data_source(workbook,sheet_name,cat,val,chart_index):
    '''
    workbook:使用openpyxl load_workbook 打开的Excel文件
    sheet_name:需要修改的sheet的名字
    cat:新的类别源
    val:新的数据源
    '''

    cn_kpi_sheet = workbook[sheet_name]
    chart = cn_kpi_sheet._charts[chart_index]
    # 设置新的类别源
    chart.set_categories(cat)
    series = chart.series[0]
    # 设置新的数据源
    new_reference = val
    # Create a new NumRef object for the new reference
    new_num_ref = NumRef(f=new_reference)
    # Create a new NumDataSource object for the new NumRef
    new_data_source = NumDataSource(numRef=new_num_ref)
    # Update the series' value source
    if isinstance(series, XYSeries):
        series.yVal = new_data_source
    else:
        series.val = new_data_source


sheet_name = "your_sheet_name"
cat_range_string = f"'{sheet_name}'!$A$905:$A$935"
cat = Reference(range_string=cat_range_string )
val_range_string = f"'{sheet_name}'!$B$905:$A$935"
val = Reference(range_string=val_range_string )

workbook = load_workbook("your excel.xlsx")
change_chart_data_source(workbook,sheet_name,cat,val)