How to use Application.Charts in the xlwings API way

The Charts member of the Application object in Excel’s object model represents the collection of all chart sheets in a workbook. In xlwings, this is accessed via the api property, which provides direct access to the underlying Excel object model. This allows for advanced chart management, such as adding new chart sheets, modifying existing ones, or iterating through all charts in the application. Using Charts is particularly useful when you need to work with chart sheets specifically, as opposed to embedded charts within worksheets.

Functionality:
The Charts collection enables you to create, access, and manipulate chart sheets. Chart sheets are standalone sheets that contain only a chart, separate from worksheet data. You can add new chart sheets, reference existing ones by name or index, and perform operations like copying, moving, or deleting them. This is essential for automating report generation or dashboard creation where charts need to be organized independently.

Syntax:
In xlwings, you typically access Charts through the workbook or application context. The general syntax is:

• app.api.Charts: Returns the Charts collection for the entire Excel application, including all open workbooks.
• wb.api.Charts: Returns the Charts collection for a specific workbook (where wb is a workbook object).

Key methods and properties include:

• Add([Before], [After], [Count]): Adds new chart sheets. Parameters are optional: Before and After specify the sheet position (as a sheet object), and Count sets the number of sheets to add (default is 1).
• Item(Index): Returns a single Chart object by index (integer) or name (string).
• Count: Property that returns the number of chart sheets in the collection.

For parameters like Before and After, you can use sheet references, such as wb.sheets['Sheet1'].api, to position the new chart sheet relative to existing sheets.

Examples:
Here are xlwings API code examples demonstrating the use of the Charts member:

1. Adding a new chart sheet to a workbook:

import xlwings as xw

# Connect to an existing workbook or create a new one
wb = xw.Book('example.xlsx')
app = xw.apps.active

# Add a new chart sheet named "SalesChart" at the end
new_chart = wb.api.Charts.Add()
new_chart.Name = "SalesChart"
print(f"Added chart sheet: {new_chart.Name}")

2. Accessing and iterating through all chart sheets in the application:

import xlwings as xw

app = xw.apps.active

# Count the total chart sheets across all open workbooks
total_charts = app.api.Charts.Count
print(f"Total chart sheets in application: {total_charts}")

# Iterate through each chart sheet and print its name
for chart in app.api.Charts:
print(chart.Name)

3. Creating a chart sheet with specific positioning:

import xlwings as xw

wb = xw.Book('data.xlsx')
# Add a chart sheet before the first worksheet
first_sheet = wb.sheets[0].api
new_chart = wb.api.Charts.Add(Before=first_sheet)
new_chart.Name = "AnalysisChart"

# You can then use the Chart object to set data sources or formats
# For example, set a chart type (assuming data is prepared)
new_chart.ChartType = 51 # 51 corresponds to xlLineMarkers in Excel constants

4. Deleting a chart sheet by name:

import xlwings as xw

wb = xw.Book('report.xlsx')
# Delete a chart sheet named "OldChart"
try:
    wb.api.Charts("OldChart").Delete()
    print("Deleted chart sheet: OldChart")
except Exception as e:
    print(f"Error: {e}")