CODEWITHSUNDEEP
pythonxlsxwriter
Josh English
Josh English

Reputation: 532

xlsxwriter y2 axis not formatting correctly

I'm trying to build a report with a combined graph in xlsxwriter 3.0.1 in Python 3.7. I want the left-hand axis to display like this:

enter image description here

But I get this:

enter image description here

Here is the code:

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"}
)

chart.set_y_axis(
    {
        "major_unit": 2000000,
        "num_format": "#,,",
        "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,
    }
)

chart.set_y2_axis({"major_units": 0.5, "num_format": "0%"})
chart.combine(line_chart)

sheet.insert_chart("A8", chart)

book.close()

Bonus problem on this project: I'd really like the primary y axis to display with a Millions unit (2M, 4M, etc) but I can't seem to get that work either.

Any ideas how I can format the second axis correctly?

Upvotes: 1

Views: 698

Answers (1)

jmcnamara
jmcnamara

Reputation: 41644

That is a good bug report.

The issue is here:

chart.set_y2_axis({"major_units": 0.5, "num_format": "0%"})

That method should be called on line_chart:

line_chart.set_y2_axis({"major_units": 0.5, "num_format": "0%"})

After that you should get the expected result:

enter image description here

Bonus problem on this project: I'd really like the primary y axis to display with a Millions unit (2M, 4M, etc) but I can't seem to get that work either.

It depends on how you want to do that in Excel. There is an Excel chart axis "display_units" option which can be set to "millions". That is partially supported in xlsxwriter via the major_unit_type but it currently is only implemented for X axes and dates. It may also not be quite what you want. If that is what you are looking for they you can open a feature request on GitHub and I will look into it. However, you can get similar results using a chart axis title.

Upvotes: 1

Related Questions