How to use Worksheet.ConsolidationFunction in the xlwings API way

The ConsolidationFunction property of the Worksheet object in Excel VBA refers to the function used when consolidating ranges (e.g., Sum, Count, Average). However, in the context of xlwings, a Python library for automating Excel, direct access to this specific VBA property is not typically exposed as a first-class API feature because xlwings focuses more on data manipulation, calculation, and automation rather than replicating the entire UI-driven consolidation feature set. The consolidation functionality in Excel is often accessed via the UI (Data > Consolidate) or VBA, and xlwings can automate these actions through the .api property to access the underlying Excel object model.

In xlwings, to utilize the ConsolidationFunction, you would work through the Excel Object Model via the api property. The ConsolidationFunction is a property of a Worksheet object in Excel’s VBA, which returns or sets the function used for consolidation (an XlConsolidationFunction constant). It is primarily used when a worksheet has a consolidation set up. The syntax in VBA is Worksheet.ConsolidationFunction, and in xlwings, you access it similarly through the worksheet’s API object.

Functionality:
This property indicates the consolidation function (e.g., sum, average, count) applied to data ranges that have been consolidated on the worksheet. It is read-only and returns an integer corresponding to an XlConsolidationFunction enumeration. Common values include:

• -4106 or xlSum for summation
• -4109 or xlCount for counting numbers
• -4116 or xlAverage for averaging
• -4135 or xlMax for maximum value
• -4136 or xlMin for minimum value
  It is useful for programmatically checking the type of consolidation applied, especially in automated reports or when auditing workbook structures.

Syntax in xlwings:
To access this property in xlwings, use:

worksheet.api.ConsolidationFunction

This returns an integer representing the consolidation function constant. Note that this property is only meaningful if the worksheet contains a consolidation range; otherwise, it may return xlNone or another default.

Example Usage:
Suppose you have an Excel workbook with a worksheet that has a consolidation set up to sum data from multiple ranges. You can use xlwings to inspect the consolidation function:

import xlwings as xw

# Connect to the active workbook or open a specific one
wb = xw.Book('consolidation_example.xlsx')
ws = wb.sheets['ConsolidatedSheet']

# Access the ConsolidationFunction property via .api
consolidation_func = ws.api.ConsolidationFunction

# Map the integer to a readable function name
func_map = {
-4106: 'Sum',
-4109: 'Count',
-4116: 'Average',
-4135: 'Max',
-4136: 'Min',
-4142: 'Unknown' # xlNone or other
}
func_name = func_map.get(consolidation_func, 'Not Consolidated')

print(f"The consolidation function on '{ws.name}' is: {func_name} (Code: {consolidation_func})")

# Optionally, you can set up a new consolidation using VBA methods via .api
# This requires using the Range.Consolidate method, which is more complex
# Example: Consolidate data from multiple ranges with a sum function
if func_name == 'Not Consolidated':
    # Define source ranges (example: ranges from other sheets)
    sources = ["Sheet1!R1C1:R10C5", "Sheet2!R1C1:R10C5"]
    ws.api.Range("A1").Consolidate(Sources=sources, Function=-4106) # -4106 for xlSum
    print("Consolidation set up with Sum function.")
else:
    print("Consolidation already exists.")

wb.save()
wb.close()