xlsxwriter组合图表次级Y轴格式异常及主Y轴百万单位显示求助

解决方案 #

你需要修改两处配置即可实现需求：

• 修正主Y轴数值格式，添加M后缀实现百万单位显示
• 修正次级Y轴的参数拼写错误，让间隔配置生效

具体修改点 #

1. 主Y轴的num_format参数修改为'#,, "M"'，两个逗号会自动把数值除以1,000,000，后面跟的"M"会作为固定后缀显示
2. 次级Y轴配置的major_units拼写错误，正确参数名为major_unit，去掉末尾多余的s即可让0.5的间隔设置生效

修改后完整代码 #

import xlsxwriter

book = xlsxwriter.Workbook("chartbug.xlsx")

money_fmt = book.add_format({"num_format": "#,##0"})
pct_fmt = book.add_format({"num_format": "0.00%"})

sheet = book.add_worksheet("Data")

sheet.write_column(1, 0, ["John", "Paul", "George", "Ringo"])
sheet.write_column(1, 1, [7800000, 3600000, 14500000, 9500000], money_fmt)
sheet.write_column(1, 2, [8000000, 8000000, 8000000, 8000000], money_fmt)
sheet.write_column(1, 3, [0.25, -0.05, 1.17, 0.15], pct_fmt)
sheet.write_row(0, 0, ["Name", "Last Year", "This Year", "Delta"])

chart = book.add_chart({"type": "column"})

chart.add_series(
    {"name": "Last Year", "categories": "Data!A2:A5", "values": "Data!B2:B5"}
)
chart.add_series(
    {"name": "This Year", "categories": "Data!A2:A5", "values": "Data!C2:C5"}
)

# 修改主Y轴格式，添加M后缀
chart.set_y_axis(
    {
        "major_unit": 2000000,
        "num_format": '#,, "M"',
        "major_gridlines": {"visible": False},
    }
)

line_chart = book.add_chart({"type": "line"})

line_chart.add_series(
    {
        "name": "Delta",
        "categories": "Data!A2:A5",
        "values": "Data!D2:D5",
        "y2_axis": True,
    }
)

# 修正参数名，major_unit去掉多余的s
chart.set_y2_axis({"major_unit": 0.5, "num_format": "0%"})
chart.combine(line_chart)

sheet.insert_chart("A8", chart)

book.close()

运行上述代码生成的Excel文件，主Y轴会按2M、4M的规则显示，次级Y轴也会按0.5间隔显示百分比格式，符合预期效果。

内容的提问来源于stack exchange，提问作者Josh English