CODEWITHSUNDEEP
pythonexcelxlwings
Jim Rutter
Jim Rutter

Reputation: 45

How do I programmatically (via Python/xlWings) add axes to an existing chart?

I have a very odd problem.

I have a python script that adds data every week to an existing Excel spreadsheet to track a weekly metric I'm interested in. When it pastes the new data in the existing worksheet, the chart that tracks the data loses its horizontal and vertical axes (probably some Excel or Pandas.ExcelWriter issue; would love to solve that, but that's not the purpose of this post).

I'm trying to have the python script re-add the missing axes, but can't find anything in the xlwings .API that will read it. I can add titles, text, etc.. but not sure how to actually add the axes back.

I thought maybe tickling the axes would cause them to reappear, such as:

sheet = book.sheets['Criticality_Over_Time']
chart = sheet.charts[0]
chart.api[1].Axes(xl.constants.AxisType.xlCategory, xl.constants.AxisGroup.xlPrimary).MajorUnitIsAuto = True
chart.api[1].Axes(xl.constants.AxisType.xlCategory, xl.constants.AxisGroup.xlPrimary).MinorUnitIsAuto = True

Is there a way to re-add the axes? I've searched everywhere and can't find a good example.

Upvotes: 0

Views: 98

Answers (2)

moken
moken

Reputation: 6620

It may depend on exactly what happened in your chart.
However if you had Axis on your chart and they were deleted the following code should re-enable.

Example if the following chart which has a Primary (Series Batch 1 and Batch 2) and Secondary (Batch 3) Y (Verticle) Axis and Primary X (Horizontal) Axis that have all been deleted Before Chart

The code checks each data series and enables the (Primary or Secondary) X and Y Axis for the series.
The same as checking the boxes in the Axis Elements in the chart 'Format Axis' enter image description here

After Chart

import xlwings as xw

### Constants
msoElementPrimaryValueAxisShow = 353
msoElementSecondaryValueAxisShow = 363
msoElementPrimaryCategoryAxisShow = 349
msoElementSecondaryCategoryAxisShow = 359

filename = 'chart.xlsx'
sheet = 'Sheet1'

with xw.App(visible=True) as xl:
    wb = xl.books.open(filename)
    ws = wb.sheets[sheet]

    for chart in ws.charts:
        for series in chart.api[1].SeriesCollection():
            axisgroup = series.AxisGroup
            if axisgroup == 1:  # primary axis
                chart.api[1].SetElement(msoElementPrimaryValueAxisShow)
                chart.api[1].SetElement(msoElementPrimaryCategoryAxisShow)
            elif axisgroup == 2:  # secondary axis
                chart.api[1].SetElement(msoElementSecondaryValueAxisShow)
                # chart.api[1].SetElement(msoElementSecondaryCategoryAxisShow)  # Probably not used

    wb.save(f"out_{filename}")

Upvotes: 0

Jesebarb
Jesebarb

Reputation: 1

You can add axes to an existing chart in Python using xlwings with chart.api.Axes(1) for the primary X-axis and chart.api.Axes(2) for the primary Y-axis.

Upvotes: 0

Related Questions