How to use Range.Consolidate in the xlwings API way

The Consolidate method in the Range object is a powerful feature for summarizing and combining data from multiple ranges or worksheets into a single location. It is particularly useful for creating consolidated reports from various data sources, such as different departments or periods, without manually copying and pasting. This method supports various consolidation functions, like sum, average, count, and more, allowing for flexible data aggregation based on specified criteria. In xlwings, the Consolidate method can be accessed through the Range API, enabling automation of these tasks within Python scripts.

Syntax and Parameters:
In xlwings, the Consolidate method is called as:

range_object.consolidate(sources, function, top_row, left_column, create_links)

The parameters are as follows:

• sources: A list of strings representing the source ranges to consolidate. Each string should be in standard Excel reference format (e.g., “Sheet1!A1:B10”). This parameter is required.
• function: An integer or constant specifying the consolidation function. Common values include:
• xlwings.constants.ConsolidationFunction.xlSum (or -4157) for sum.
• xlwings.constants.ConsolidationFunction.xlAverage (or -4106) for average.
• xlwings.constants.ConsolidationFunction.xlCount (or -4112) for count.
• xlwings.constants.ConsolidationFunction.xlMax (or -4136) for maximum.
• xlwings.constants.ConsolidationFunction.xlMin (or -4139) for minimum.
  This parameter is required.
• top_row: A boolean value indicating whether to use the top row of the consolidation ranges as labels. Default is False.
• left_column: A boolean value indicating whether to use the left column of the consolidation ranges as labels. Default is False.
• create_links: A boolean value indicating whether to create links to the source data. If True, the consolidated data will be linked to the original sources for updates. Default is False.

Example Usage:
Suppose you have an Excel workbook with two sheets named “Sales_Q1” and “Sales_Q2”, each containing sales data in the range A1:B5, where column A has product names and column B has sales amounts. You want to consolidate the total sales by product into a new sheet called “Summary”. Here is an xlwings code example:

import xlwings as xw

# Open the workbook
wb = xw.Book('sales_data.xlsx')

# Define the target range in the Summary sheet for consolidation
summary_sheet = wb.sheets['Summary']
target_range = summary_sheet.range('A1')

# List the source ranges to consolidate
sources = ["Sales_Q1!A1:B5", "Sales_Q2!A1:B5"]

# Perform consolidation to sum the sales amounts, using the first row and left column as labels
target_range.consolidate(sources=sources,
function=xw.constants.ConsolidationFunction.xlSum,
top_row=True,
left_column=True,
create_links=False)

# Save and close the workbook
wb.save()
wb.close()