How to use Worksheet.ConsolidationOptions in the xlwings API way

The ConsolidationOptions member of a Worksheet object in Excel’s object model refers to settings that control how data consolidation is performed on the worksheet. In xlwings, this is accessed through the api property, which provides direct access to the underlying Excel object model. The ConsolidationOptions property returns a Consolidation object, which itself has several properties and methods to define the source ranges, function, and other options for consolidating data from multiple ranges into a single summary range. This feature is useful for summarizing data from various sheets or workbooks, such as combining sales figures from different regions.

Syntax and Parameters:
In xlwings, you access ConsolidationOptions via Worksheet.api.ConsolidationOptions. This returns a Consolidation object with key properties including:

• Sources: A list of source ranges as strings (e.g., ["Sheet1!R1C1:R10C5", "Sheet2!R1C1:R10C5"]). Each string represents a range in R1C1-style notation.
• Function: An Excel constant specifying the consolidation function, such as xlwings.constants.xlSum for sum or xlwings.constants.xlAverage for average. Common values include:
• xlSum (-4157): Adds values.
• xlAverage (-4106): Calculates the average.
• xlCount (-4112): Counts non-empty cells.
• xlMax (-4136): Finds the maximum value.
• xlMin (-4139): Finds the minimum value.
• TopRow and LeftColumn: Boolean values indicating whether to use labels from the top row or left column of the source ranges for consolidation.
• CreateLinks: A boolean that, if True, creates links to the source data (default is False).

To set up consolidation, you typically assign these properties and then use the Consolidate method of the Range object where you want the consolidated data. However, note that ConsolidationOptions itself is primarily for retrieving or setting options rather than executing consolidation directly.

Code Example:
Here is an example using xlwings to set consolidation options and perform consolidation on a worksheet. This script assumes you have an Excel workbook open with data in multiple sheets, and you want to sum values from specific ranges into a summary sheet.

import xlwings as xw

# Connect to the active workbook
wb = xw.books.active
# Assume we have a summary sheet named "Summary"
summary_sheet = wb.sheets["Summary"]

# Access the ConsolidationOptions via the api property
consolidation = summary_sheet.api.ConsolidationOptions

# Set the sources for consolidation (using R1C1 notation for ranges from two sheets)
consolidation.Sources = ["Sheet1!R1C1:R10C3", "Sheet2!R1C1:R10C3"]

# Set the function to sum (xlSum constant)
consolidation.Function = xw.constants.xlSum

# Use labels from the top row and left column
consolidation.TopRow = True
consolidation.LeftColumn = True
# Do not create links to source data
consolidation.CreateLinks = False

# Now, consolidate the data into a starting cell on the summary sheet (e.g., A1)
# The Consolidate method is called on the Range object where consolidation begins
summary_sheet.range("A1").api.Consolidate(Sources=consolidation.Sources,
Function=consolidation.Function,
TopRow=consolidation.TopRow,
LeftColumn=consolidation.LeftColumn,
CreateLinks=consolidation.CreateLinks)

# Optionally, you can check the current consolidation settings
print("Current sources:", consolidation.Sources)
print("Function used:", consolidation.Function)