pythonexcelxlwings

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 (probalby 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 readd 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 serached everywhere and can't find a good example.

Solution

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