当前位置：首页 > article >正文

通过openpyxl在excel中插入散点图

article 2025/9/15 7:53:01

实现代码

# -*- coding: utf-8 -*-
"""
Created on Sat May 31 23:30:12 2025@author: anyone
"""from openpyxl import load_workbook
from openpyxl.chart import ScatterChart, Reference, Series
from openpyxl.chart.series import SeriesLabel# 加载现有的 Excel 文件
file_path = "scatter_with_line_and_markers.xlsx"  # 替换为你的 Excel 文件路径
wb = load_workbook(file_path)
ws = wb.active  # 选择第一个工作表# 定义散点图对象
chart = ScatterChart()# 假设数据在 A 列和 B 列，从第 2 行开始
x_data = Reference(ws, min_col=1, min_row=1, max_row=6)  # X 轴数据（A 列）for i in range(4):cell = ws.cell(i * 6 + 1, 1)if cell.value is None:print("cell is None")break;else:print("cell is Not None")y_data = Reference(ws, min_col=2, min_row=i * 6 + 1, max_row=i * 6 + 6)  # Y 轴数据（B 列）# 创建散点图系列series = Series(y_data, x_data, title_from_data=False)series_label = SeriesLabel(v="{}_{}".format("index", i))  # 设置系列名称的值series.tx = series_label  # 将 SeriesLabel 对象赋给 series 的 tx 属性series.marker.symbol = "circle"  # 设置数据点标记为圆形series.marker.size = 6  # 设置数据点标记的大小# series1 = Series(z_data, x_data, title_from_data=False)# series1.marker.symbol = "circle"  # 设置数据点标记为圆形# series1.marker.size = 6  # 设置数据点标记的大小# 添加系列到图表chart.series.append(series)# chart.series.append(series1)# 设置图表标题和坐标轴标题
chart.title = "xxx VS YYY"
chart.x_axis.title = "xxx"
chart.y_axis.title = "yyy"# 将图表添加到工作表（例如从 D2 单元格开始）
ws.add_chart(chart, "D2")# 保存工作簿
wb.save(file_path)