The Sort method of the Range object in Excel, accessible through the xlwings API, is a powerful tool for organizing data within a specified range. Its primary function is to sort a range of cells based on one or more keys (columns or rows), allowing for both ascending and descending order, custom sort orders, and header recognition. This is essential for data analysis tasks where data needs to be ordered by specific criteria, such as sorting sales figures by date or alphabetizing a list of names.
In xlwings, the syntax for the Sort method mirrors the VBA object model but follows Python conventions. The general call format is:
range.sort(key1, order1, key2, order2, key3, order3, header, ordercustom, matchcase, orientation, sortmethod, dataoption1, dataoption2, dataoption3)
The parameters are:
key1,key2,key3: These areRangeobjects specifying the first, second, and third sort fields (the columns or rows to sort by). Onlykey1is required.order1,order2,order3: Constants that determine the sort order for each key. Usexlwings.constants.xlAscending(or'asc'as a string) for ascending order andxlwings.constants.xlDescending(or'desc') for descending.header: Specifies whether the first row (or column) contains headers. Usexlwings.constants.xlYes,xlNo, orxlGuess.orientation: Defines whether to sort by rows (xlSortRows, default) or columns (xlSortColumns).matchcase: Set toTruefor case-sensitive sorting.sortmethod: Can bexlPinYin(default for Chinese) orxlStrokefor East Asian languages.
For most common sorts, you only need to specify the key1 and order1 parameters. The method sorts the entire original range object in place.
Here are two practical xlwings code examples:
Example 1: Basic Single-Column Sort
This code sorts a data table in Sheet1 based on the values in column B (the “Sales” column) in descending order, assuming the first row is a header.
import xlwings as xw
wb = xw.Book('report.xlsx')
sheet = wb.sheets['Sheet1']
# Define the data range (A1 to C10, including headers)
data_range = sheet.range('A1:C10')
# Sort by column B (index 2) in descending order
data_range.sort(key1=data_range.columns[1], # B column is index 1 (0-based)
order1=xw.constants.xlDescending,
header=xw.constants.xlYes)
Example 2: Multi-Column Sort
This example performs a more complex sort on a list of employees. It first sorts by “Department” (column A) in ascending order, and then within each department, sorts by “Name” (column B) in ascending order.
import xlwings as xw
wb = xw.Book('employees.xlsx')
sheet = wb.sheets['Data']
main_range = sheet.range('A1:C100')
# Multi-key sort: Department first, then Name
main_range.sort(key1=main_range.columns[0], # Column A
order1='asc', # String shortcut for ascending
key2=main_range.columns[1], # Column B
order2=xw.constants.xlAscending,
header=xw.constants.xlYes)
wb.save()
Leave a Reply