带有辅助 y 轴的 Python openpyxl 散点图

Question

有没有人有使用散点图在 Excel 中创建辅助 y 轴的 Python 代码示例？即使只是一些简单的事情，它也会对我有很大的帮助。我正在使用 openpyxl 在 Excel 中绘制多个系列，并希望其中一些与左轴相关联，一些与右轴相关联。我尝试使用 openpyxl Aliens/Humans 示例，但它使用条形图和折线图，我似乎无法让它适应散点图。
先感谢您!
梅丽莎

Answer 1

散点图看起来很像 openpyxl 中的折线图，因为散点图只是一个点列表，中间有线。要删除线条，请将线条样式设置为 noFill。此外 - 对于散点图，您需要 X 坐标的附加数据系列(excel 中的第 1 行)。
试试这个代码:

from openpyxl import Workbook
from openpyxl.chart import (
    LineChart,
    BarChart,
    ScatterChart,
    Reference,
    Series,
)

wb = Workbook()
ws = wb.active

rows = [
    ['Index', 1,2,3,4,5,6],  # needed as X coordinates for scatter chart
    ['Aliens', 2, 3, 4, 5, 6, 7],  # aliens Y coordinates
    ['Humans', 10, 40, 50, 20, 10, 50]  # humans Y coordinates
]

for row in rows:
    ws.append(row)


# Aliens
c1 = ScatterChart()

xvalues = Reference(ws, min_col=1, min_row=1, max_col=7)
values = Reference(ws, min_col=1, min_row=2, max_col=7)
series = Series(values, xvalues, title_from_data=True)
c1.series.append(series)

s1 = c1.series[0]
s1.marker.symbol = "triangle"
s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling
s1.marker.graphicalProperties.line.solidFill = "FF0000" # Marker outline
s1.graphicalProperties.line.noFill = True  # hide lines

c1.style = 13
c1.x_axis.title = 'Days'
c1.y_axis.title = 'Aliens'
c1.y_axis.majorGridlines = None
c1.x_axis.majorGridlines = None
c1.title = 'Survey results'


# Humans
c2 = ScatterChart()

xvalues = Reference(ws, min_col=1, min_row=1, max_col=7)
values = Reference(ws, min_col=1, min_row=3, max_col=7)
series = Series(values, xvalues, title_from_data=True)
c2.series.append(series)

s2 = c2.series[0]
s2.marker.symbol = "diamond"
s2.marker.graphicalProperties.solidFill = "0000FF" # Marker filling
s2.marker.graphicalProperties.line.solidFill = "0000FF" # Marker outline
s2.graphicalProperties.line.noFill = True  # hide lines

c2.style = 13
c2.x_axis.title = 'Days'
c2.y_axis.title = 'Humans'
c2.y_axis.axId = 200
c2.y_axis.majorGridlines = None
c2.x_axis.majorGridlines = None


# Display y-axis of the second chart on the right by setting it to cross the x-axis at its maximum
c1.y_axis.crosses = "max"
c1 += c2

ws.add_chart(c1, "D4")

wb.save("secondary.xlsx")

输出(在 Excel 中)