How to use Range.DataSeries in the xlwings API way

The DataSeries member of the Range object in Excel VBA is a powerful feature for creating and manipulating data series within charts. When using xlwings in Python, this functionality is accessed through the api property, which exposes the underlying VBA object model. The DataSeries object itself is typically a collection (SeriesCollection) associated with a Chart object, but it is often referenced from a specific range of data that defines the series values or categories. In xlwings, you primarily interact with chart series via the Chart object, but understanding how ranges feed into data series is crucial.

Functionality:
The DataSeries (or more precisely, a series within a SeriesCollection) represents a single data series in a chart, such as a line in a line chart or a set of columns in a column chart. Key operations include defining the source data range for series values (e.g., y-values) and category labels (e.g., x-values), formatting the series, and modifying its properties. Through xlwings, you can automate the creation and customization of charts by setting these ranges programmatically.

Syntax and Parameters:
In xlwings, you typically work with the Series object from a chart’s series_collection. To set or get the range defining the series values, you use the values property. For category labels, use the x_values property. These properties accept a range address string or an xlwings Range object. The general approach is:

series = chart.series_collection[0] # Access the first series
series.values = sheet.range("B2:B10").api # Set values range via .api
series.x_values = sheet.range("A2:A10").api # Set categories range via .api

Here, chart is an xlwings Chart object, and sheet is an xlwings Sheet object. The .api property returns the underlying VBA-compatible object, allowing you to assign ranges directly. Parameters:

• values: Specifies the range containing the data points (y-values) for the series. It can be a string like "Sheet1!B2:B10" or a VBA Range object.
• x_values: Specifies the range for category labels (x-values). It is optional; if omitted, Excel uses sequential numbers.

Example Code:
Suppose you have an Excel workbook with sales data in columns A (Month) and B (Sales), and you want to create a line chart. The following xlwings code demonstrates how to define the data series from a range:

import xlwings as xw

# Connect to the active workbook and sheet
wb = xw.Book.active
sheet = wb.sheets["Sheet1"]

# Create a chart object
chart = sheet.charts.add(left=100, top=10, width=400, height=300)
chart.set_source_data(sheet.range("A1:B10")) # Set overall data range

# Access the first series in the chart and modify its data range via .api
series = chart.api.SeriesCollection(1) # Using .api for VBA-like access
series.Values = sheet.range("B2:B10").api # Set values to Sales column
series.XValues = sheet.range("A2:A10").api # Set categories to Month column

# Alternatively, use xlwings' higher-level methods (if available) for simplicity
# For instance, you can also set series data directly during chart creation:
chart = sheet.charts.add()
chart.chart_type = "line"
chart.series_collection.add(sheet.range("B2:B10"), rowcol="columns", series_from="columns")