Blog

How to use Application.AddCustomList in the xlwings API way

The AddCustomList member of the Application object in Excel is a method that allows you to define a custom list for sorting and auto-filling data. Custom lists are particularly useful for creating personalized sorting orders, such as days of the week, months, or any user-defined sequence, which can then be applied across worksheets to ensure consistent data organization. In xlwings, this functionality is accessed through the api property, which provides direct access to the underlying Excel object model, enabling precise control over Excel’s features from Python.

Functionality:
The primary function of AddCustomList is to add a new custom list to Excel’s memory. Once added, this list can be used in sorting operations or for auto-fill actions, where dragging a cell’s fill handle will populate cells based on the defined sequence. This is beneficial for standardizing data entry and maintaining order in datasets that follow non-alphabetical or non-numeric sequences.

Syntax in xlwings:
The xlwings API call follows the pattern:

app.api.AddCustomList(ListArray, ByRow)

• ListArray: This parameter specifies the items to be included in the custom list. It can be provided as a Python list or tuple containing strings or numbers. For example, ['Low', 'Medium', 'High'] or ('Q1', 'Q2', 'Q3', 'Q4'). The list must be one-dimensional.
• ByRow: This is a Boolean parameter that indicates whether the list is arranged by rows. In most cases, setting ByRow to False is appropriate, as custom lists are typically column-oriented. If set to True, the list is interpreted as a row-based array, but this is less common. The default behavior in Excel VBA is False, and it is generally recommended to use False in xlwings unless specific row-based data is provided.

Example Usage:
Below is an xlwings code example that demonstrates how to add a custom list and then use it for sorting data in an Excel worksheet. This example assumes an existing Excel workbook is open via xlwings.

import xlwings as xw

# Connect to the active Excel application
app = xw.apps.active

# Define a custom list for priority levels
custom_list = ['Low', 'Medium', 'High']

# Add the custom list using the Application object's AddCustomList method
app.api.AddCustomList(ListArray=custom_list, ByRow=False)

# Now, use the custom list to sort data in a specific worksheet
wb = xw.books.active
ws = wb.sheets['Sheet1']

# Assume column A contains priority data to be sorted based on the custom list
# Set the sort range (e.g., A1:A10)
sort_range = ws.range('A1:A10')

# Apply sorting with the custom order
sort_range.api.Sort(
Key1=ws.range('A1').api,
Order1=1, # Ascending order
CustomOrder=custom_list[0], # Use the first item of the list to reference the custom list
DataOption1=0
)

# Note: In Excel, the custom list is stored globally, so it can be reused across workbooks during the session.