How to use Application.ExtendList in the xlwings API way

The Application.ExtendList property in Excel is a read-only Boolean property that indicates whether the “Extend list formats and formulas” option is enabled in Excel’s AutoCorrect settings. This setting, when turned on, automatically extends formatting and formulas when new data is added to a list, facilitating consistent data entry and calculation in structured ranges like tables. In xlwings, this property can be accessed through the api property of the App object, allowing Python scripts to check the current state of this Excel feature programmatically.

Functionality:
The primary function of ExtendList is to inform whether Excel is configured to automatically apply existing formats and formulas to new rows or columns added to a list. This is particularly useful in scenarios involving dynamic data ranges where maintaining uniformity is critical. By querying this property, developers can decide whether to rely on Excel’s built-in automation or implement custom logic for data expansion in their scripts.

Syntax in xlwings:
The property is accessed via the api interface, which mirrors Excel’s VBA object model. The syntax is straightforward:

app.api.ExtendList

This returns a Boolean value: True if the “Extend list formats and formulas” option is enabled, and False otherwise. There are no parameters for this property, as it is read-only. The property is part of the Application object, which represents the Excel instance itself.

Example Usage:
Below is a practical example demonstrating how to use ExtendList in an xlwings script. This code checks the setting and prints a message, then conditionally performs an action based on the result, such as manually extending formulas if the feature is disabled.

import xlwings as xw

# Connect to the active Excel instance or start a new one
app = xw.apps.active

# Check the state of the ExtendList property
extend_enabled = app.api.ExtendList

# Output the result
if extend_enabled:
    print("Extend list formats and formulas is ENABLED in Excel.")
else:
    print("Extend list formats and formulas is DISABLED in Excel.")

# Example: If disabled, manually copy a formula down a column in a specific worksheet
if not extend_enabled:
    wb = app.books.active
    ws = wb.sheets['Sheet1']
    # Assume a formula exists in cell B2 and we want to extend it down to B10
    formula_range = ws.range('B2:B10')
    formula_range.formula = '=A2*2' # Set a sample formula
    print("Manually extended formula in column B due to disabled ExtendList.")
else:
    print("Relying on Excel's auto-extension for formats and formulas.")

# Close the Excel instance if needed (optional)
app.quit()