How to use Worksheet.ChartObjects in the xlwings API way
The ChartObjects member of a Worksheet object in Excel’s object model represents a collection of all embedded chart objects (charts that are placed directly on a worksheet, as opposed to chart sheets). In xlwings, this collection is accessible via the api property, which provides direct access to the underlying COM object model. This allows for detailed control over chart creation, modification, and management directly from Python.
Functionality:
The primary function of the ChartObjects collection is to enable programmatic manipulation of charts embedded within a specific worksheet. Through xlwings, you can add new charts, access existing ones, modify their properties (such as position, size, and chart type), or delete them. This is particularly useful for automating report generation, dynamically updating visualizations based on data changes, or creating dashboards where charts need to be precisely positioned alongside data.
Syntax:
In xlwings, you access the ChartObjects collection via the api property of a Sheet object (which corresponds to a worksheet). The typical syntax is:
chart_objects = ws.api.ChartObjectsHere, ws is an xlwings Sheet object representing the worksheet. The ChartObjects object has several methods and properties. A key method for adding a new chart is Add, which has the following parameters:
Left(Required,float): The position, in points, of the left edge of the new chart object relative to the left edge of column A on the worksheet.Top(Required,float): The position, in points, of the top edge of the new chart object relative to the top edge of row 1.Width(Required,float): The width of the new chart object in points.Height(Required,float): The height of the new chart object in points.
The method returns a ChartObject object, which has a Chart property that provides access to the actual chart (where you set the chart type, data source, etc.).
Code Examples:
- Adding a New Chart:
import xlwings as xw
# Connect to an existing workbook or create a new one
wb = xw.Book() # Creates a new workbook
ws = wb.sheets['Sheet1']
# Add some sample data
ws.range('A1').value = [['Month', 'Sales'], ['Jan', 150], ['Feb', 200], ['Mar', 180]]
# Access the ChartObjects collection and add a new chart
chart_obj = ws.api.ChartObjects().Add(Left=100, Top=50, Width=300, Height=200)
chart = chart_obj.Chart
# Set the chart type and data source
chart.ChartType = 51 # xlColumnClustered (value from Excel constants)
chart.SetSourceData(Source=ws.range('A1:B4').api)
chart.HasTitle = True
chart.ChartTitle.Text = "Monthly Sales"- Accessing and Modifying an Existing Chart:
Assume there is already an embedded chart named “Chart 1” on the worksheet.
# Access the ChartObjects collection
all_charts = ws.api.ChartObjects
# Access a specific chart by its index (1-based) or name
chart_obj = all_charts(1) # First chart in the collection
# Or by name: chart_obj = all_charts("Chart 1")
# Modify its position and size
chart_obj.Left = 200
chart_obj.Top = 100
chart_obj.Width = 400
chart_obj.Height = 250
# Change the chart title via the Chart property
chart_obj.Chart.ChartTitle.Text = "Updated Sales Report"- Looping Through All Charts on a Worksheet:
# Iterate over all embedded chart objects
for chart_obj in ws.api.ChartObjects:
print(f"Chart Name: {chart_obj.Name}")
print(f"Chart Type: {chart_obj.Chart.ChartType}")
# You can perform operations like checking title or changing data series
if chart_obj.Chart.HasTitle:
print(f"Title: {chart_obj.Chart.ChartTitle.Text}")- Deleting a Chart:
# Delete a specific chart by name
ws.api.ChartObjects("Chart 1").Delete()
# Or delete all charts on the worksheet
for chart_obj in ws.api.ChartObjects:
chart_obj.Delete()