Python实现Excel散点图：绘制与定制方法详解

作者：袖梨 2026-06-02

散点图作为数据可视化的重要工具，能够直观展示变量间的相关性和分布模式。本文将通过Python代码示例，详细介绍如何在Excel中创建专业的散点图，包括数据准备、图表生成、趋势线添加等完整流程。

环境准备

首先需要安装Spire.XLS for Python库：

pip install Spire.XLS

该库提供了完整的Excel文件操作API，支持创建、读取和修改Excel文档，包括各种图表类型的生成和定制。

创建基础散点图

以下是创建薪资与汽车价格关系散点图的完整代码：

from spire.xls import *
from spire.xls.common import *

# 创建工作簿
workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.Name = "Scatter Chart"

# 准备示例数据
sheet.Range["A1"].Value = "Y(薪资)"
sheet.Range["A2"].Value = "42763"
sheet.Range["A3"].Value = "195387"
sheet.Range["A4"].Value = "35672"
sheet.Range["A5"].Value = "217637"
sheet.Range["A6"].Value = "74734"
sheet.Range["A7"].Value = "130550"
sheet.Range["A8"].Value = "42976"
sheet.Range["A9"].Value = "15132"
sheet.Range["A10"].Value = "54936"

sheet.Range["B1"].Value = "X(汽车价格)"
sheet.Range["B2"].Value = "19455"
sheet.Range["B3"].Value = "93965"
sheet.Range["B4"].Value = "20858"
sheet.Range["B5"].Value = "107164"
sheet.Range["B6"].Value = "34036"
sheet.Range["B7"].Value = "87806"
sheet.Range["B8"].Value = "17927"
sheet.Range["B9"].Value = "61518"
sheet.Range["B10"].Value = "29479"

# 创建散点图
chart = sheet.Charts.Add(ExcelChartType.ScatterMarkers)
chart.DataRange = sheet.Range["B2:B10"]
chart.SeriesDataFromRange = False

# 设置图表位置
chart.LeftColumn = 1
chart.TopRow = 11
chart.RightColumn = 10
chart.BottomRow = 28

# 设置图表标题
chart.ChartTitle = "薪资与汽车价格关系分析"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 12

# 配置数据系列
chart.Series[0].CategoryLabels = sheet.Range["A2:A10"]
chart.Series[0].Values = sheet.Range["B2:B10"]

# 保存文件
workbook.SaveToFile("ScatterChart.xlsx", FileFormat.Version2010)
workbook.Dispose()

添加趋势线

以下代码演示如何添加指数趋势线：

from spire.xls import *
from spire.xls.common import *

# 加载包含散点图的Excel文件
workbook = Workbook()
workbook.LoadFromFile("ScatterChart.xlsx")
sheet = workbook.Worksheets[0]

# 获取图表并添加趋势线
chart = sheet.Charts[0]
trendline = chart.Series[0].TrendLines.Add(TrendLineType.Exponential)

# 保存文件
workbook.SaveToFile("ScatterChartWithTrendline.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

设置坐标轴标签

以下是设置坐标轴标签的代码示例：

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("ScatterChart.xlsx")
sheet = workbook.Worksheets[0]
chart = sheet.Charts[0]

# 设置坐标轴标题
chart.PrimaryValueAxis.Title = "薪资（元）"
chart.PrimaryCategoryAxis.Title = "汽车价格（元）"

# 设置坐标轴标题字体样式
chart.PrimaryValueAxis.TitleArea.IsBold = True
chart.PrimaryValueAxis.TitleArea.Size = 10
chart.PrimaryCategoryAxis.TitleArea.IsBold = True
chart.PrimaryCategoryAxis.TitleArea.Size = 10

workbook.SaveToFile("ScatterChartWithAxisLabels.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

自定义数据标签

以下是添加和格式化数据标签的代码：

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.CreateEmptySheets(1)
sheet = workbook.Worksheets[0]
sheet.Name = "Demo"

# 准备数据
sheet.Range["A1"].Value = "月份"
sheet.Range["A2"].Value = "1月"
sheet.Range["A3"].Value = "2月"
sheet.Range["A4"].Value = "3月"
sheet.Range["A5"].Value = "4月"
sheet.Range["A6"].Value = "5月"
sheet.Range["A7"].Value = "6月"

sheet.Range["B1"].Value = "销售额"
sheet.Range["B2"].NumberValue = 25
sheet.Range["B3"].NumberValue = 18
sheet.Range["B4"].NumberValue = 8
sheet.Range["B5"].NumberValue = 13
sheet.Range["B6"].NumberValue = 22
sheet.Range["B7"].NumberValue = 28

# 创建带标记的折线图
chart = sheet.Charts.Add(ExcelChartType.LineMarkers)
chart.DataRange = sheet.Range["B1:B7"]
chart.SeriesDataFromRange = False

# 设置数据标签
cs1 = chart.Series[0]
cs1.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
cs1.DataPoints.DefaultDataPoint.DataLabels.HasLegendKey = False
cs1.DataPoints.DefaultDataPoint.DataLabels.HasPercentage = False
cs1.DataPoints.DefaultDataPoint.DataLabels.HasSeriesName = True
cs1.DataPoints.DefaultDataPoint.DataLabels.HasCategoryName = True
cs1.DataPoints.DefaultDataPoint.DataLabels.Delimiter = ". "
cs1.DataPoints.DefaultDataPoint.DataLabels.Size = 9
cs1.DataPoints.DefaultDataPoint.DataLabels.Color = Color.get_Red()
cs1.DataPoints.DefaultDataPoint.DataLabels.FontName = "Calibri"
cs1.DataPoints.DefaultDataPoint.DataLabels.Position = DataLabelPositionType.Center

workbook.SaveToFile("ChartWithDataLabels.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

综合应用示例

以下是完整的散点图应用示例：

from spire.xls import *
from spire.xls.common import *

# 创建工作簿
workbook = Workbook()
sheet = workbook.Worksheets[0]
sheet.Name = "数据分析"

# 准备数据
data = [
    ("产品A", 100, 200),
    ("产品B", 150, 180),
    ("产品C", 200, 250),
    ("产品D", 120, 160),
    ("产品E", 180, 220),
    ("产品F", 90, 140),
    ("产品G", 250, 300),
    ("产品H", 160, 190),
]

sheet.Range["A1"].Value = "产品名称"
sheet.Range["B1"].Value = "广告投入（万元）"
sheet.Range["C1"].Value = "销售额（万元）"

for i, (name, ads, sales) in enumerate(data, start=2):
    sheet.Range[f"A{i}"].Value = name
    sheet.Range[f"B{i}"].NumberValue = ads
    sheet.Range[f"C{i}"].NumberValue = sales

# 创建散点图
chart = sheet.Charts.Add(ExcelChartType.ScatterMarkers)
chart.DataRange = sheet.Range["B2:C9"]
chart.SeriesDataFromRange = False

# 设置图表标题
chart.ChartTitle = "广告投入与销售额关系分析"
chart.ChartTitleArea.IsBold = True
chart.ChartTitleArea.Size = 14
chart.ChartTitleArea.FontName = "微软雅黑"

# 添加趋势线
chart.Series[0].TrendLines.Add(TrendLineType.Linear)

# 设置坐标轴标题
chart.PrimaryValueAxis.Title = "销售额（万元）"
chart.PrimaryCategoryAxis.Title = "广告投入（万元）"

workbook.SaveToFile("ComprehensiveScatterChart.xlsx", ExcelVersion.Version2013)
workbook.Dispose()