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)