以辅助 y 轴和 x 轴作为日期的图表 [英] Chart with secondary y-axis and x-axis as dates

问题描述

我正在尝试在 openpyxl 中创建一个图表，其中包含一个辅助 y 轴和一个用于 x 值的 DateAxis.

I'm trying to create a chart in openpyxl with a secondary y-axis and an DateAxis for the x-values.

对于这个 MWE，我调整了 次轴示例 使用 DateAxis 示例.

For this MWE, I've adapted the secondary axis example with the DateAxis example.

from datetime import datetime
from openpyxl import Workbook, chart

# set to True to fail/create an invalid document
# set to False to generate a valid, but ugly/useless chart
DATES_ON_2ND = True

wb = Workbook()
ws = wb.active

xvals = ['date', *[datetime(2018, 11, d, d+12) for d in range(1, 7)]]
avals = ['aliens', 6, 3, 4, 3, 6, 7]
hvals = ['humans', 10, 40, 50, 20, 10, 50]

for row in zip(xvals, avals, hvals):
    ws.append(row)

dates = chart.Reference(ws, min_row=2, max_row=7, min_col=1, max_col=1)
aliens = chart.Reference(ws, min_row=1, max_row=7, min_col=2, max_col=2)
humans = chart.Reference(ws, min_row=1, max_row=7, min_col=3, max_col=3)

c1 = chart.LineChart()
c1.x_axis = chart.axis.DateAxis(crossAx=100)
c1.x_axis.title = "Date"
c1.x_axis.crosses = "min"
c1.x_axis.majorTickMark = "out"
c1.x_axis.number_format = "yyyy-mmm-dd"

c1.add_data(aliens, titles_from_data=True)
c1.set_categories(dates)
c1.y_axis.title = 'Aliens'

# Create a second chart
c2 = chart.LineChart()
if DATES_ON_2ND:
    c2.x_axis = chart.axis.DateAxis(crossAx=100)
    c2.x_axis.number_format = "yyyy-mmm-dd"

c2.x_axis.crosses = "min"
c2.add_data(humans, titles_from_data=True)
c2.set_categories(dates)

# c2.y_axis.axId = 200
c2.y_axis.title = "Humans"

# 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, "E4")
wb.save("secondary.xlsx")

当我将辅助 x 轴保留为分类轴时，会创建一个有效的 Excel 文档，即使图表不是我想要的.但是将辅助轴设置为 DateAxis 与主轴相同的方式会生成一个无效的损坏文件，无法显示任何图表.

When I leave the secondary x axis as a categorical axis, a valid Excel document is created, even if the chart isn't what I want. But setting the secondary axis as a DateAxis the same way as the primary axis generates an invalid corrupted file that fails to show any chart.

是否有我遗漏的技巧?

推荐答案

所以，正如我在评论中所指出的，DateAxes 并没有太大的好处，但是如果你使用它们，那么它们就有了默认 id 为 500.这很重要，因为它是 y 轴需要交叉的值.CrossAx 用于类别/日期轴并不重要.以下对我有用:

So, as noted in my comments, there isn't really much benefit in DateAxes but if you use them then they have a default id of 500. This is important because it is the value that the y-axes need to cross. CrossAx for the category/date axis doesn't seen to matter. The following works for me:

from datetime import datetime
from openpyxl import Workbook, chart

wb = Workbook()
ws = wb.active

xvals = ['date', *[datetime(2018, 11, d, d+12) for d in range(1, 7)]]
avals = ['aliens', 6, 3, 4, 3, 6, 7]
hvals = ['humans', 10, 40, 50, 20, 10, 50]

for row in zip(xvals, avals, hvals):
    ws.append(row)

dates = chart.Reference(ws, min_row=2, max_row=7, min_col=1, max_col=1)
aliens = chart.Reference(ws, min_row=1, max_row=7, min_col=2, max_col=2)
humans = chart.Reference(ws, min_row=1, max_row=7, min_col=3, max_col=3)

c1 = chart.LineChart()
c1.x_axis = chart.axis.DateAxis() # axId defaults to 500
c1.x_axis.title = "Date"
c1.x_axis.crosses = "min"
c1.x_axis.majorTickMark = "out"
c1.x_axis.number_format = "yyyy-mmm-dd"

c1.add_data(aliens, titles_from_data=True)
c1.set_categories(dates)
c1.y_axis.title = 'Aliens'
c1.y_axis.crossAx = 500
c1.y_axis.majorGridlines = None

# Create a second chart
c2 = chart.LineChart()
c2.x_axis.axId = 500 # same as c1

c2.x_axis.crosses = "min"
c2.add_data(humans, titles_from_data=True)
c2.set_categories(dates)

c2.y_axis.axId = 20
c2.y_axis.title = "Humans"
c2.y_axis.crossAx = 500

# 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, "E4")
wb.save("secondary.xlsx")

这篇关于以辅助 y 轴和 x 轴作为日期的图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！