The SortSpecial method in the Range object is a powerful feature for sorting data in Excel, particularly when dealing with custom lists or locale-specific sorting rules, such as those for East Asian languages. In xlwings, this method provides programmatic access to Excel’s advanced sorting capabilities, allowing for more nuanced sorting beyond simple alphabetical or numerical order. It is especially useful for sorting by custom lists (e.g., days of the week, months) or when sorting data in languages that use specific character orders, like Japanese or Chinese.
The xlwings API syntax for SortSpecial closely mirrors the VBA method, with parameters that control the sorting behavior. The method is called on a Range object, and its parameters define the sort order, key, and other options. The general syntax in xlwings is:
range_object.api.SortSpecial(SortMethod, Key1, Order1, Type, Key2, Order2, Key3, Order3, Header, OrderCustom, MatchCase, Orientation, SortMethod, DataOption1, DataOption2, DataOption3)
Key parameters include:
SortMethod: Specifies the type of sort. UsexlSortMethodPinYinfor phonetic sorting in East Asian languages orxlSortMethodStrokefor stroke count sorting in Chinese. In xlwings, these constants are available viaxlwings.constants(e.g.,xlwings.constants.xlSortMethodPinYin).Key1,Key2,Key3: The ranges that define the columns or rows to sort by, specified as strings (e.g., “A1” for a column). In xlwings, you can pass aRangeobject or a string.Order1,Order2,Order3: Determine the sort order for each key, using constants likexlAscending(for ascending order) orxlDescending(for descending), accessible viaxlwings.constants.Header: Indicates whether the range has headers. UsexlYes,xlNo, orxlGuessfromxlwings.constants.OrderCustom: An integer specifying a custom sort order from Excel’s custom lists (e.g., 1 for the default list).
For example, to sort a range by a custom list (like months) in ascending order, you might set OrderCustom to the index of that list. Note that many parameters are optional and can be omitted by passing None in Python.
Here is an xlwings code example that demonstrates using SortSpecial to sort a range by a custom list. Assume we have an Excel workbook with data in range “A1:B10”, where column A contains month names (e.g., “January”, “February”) and column B has corresponding values. We want to sort by column A using the default month order:
import xlwings as xw
# Connect to the active workbook and sheet
app = xw.apps.active
wb = app.books.active
sheet = wb.sheets.active
# Define the range to sort (including headers)
sort_range = sheet.range("A1:B10")
# Perform the sort using SortSpecial with a custom order (months)
sort_range.api.SortSpecial(
SortMethod=xw.constants.xlSortMethodPinYin, # Use phonetic sorting, but custom list overrides
Key1=sheet.range("A1"), # Sort by column A
Order1=xw.constants.xlAscending,
Header=xw.constants.xlYes, # Range has headers
OrderCustom=1 # Use the first custom list (typically months in English versions)
)
Leave a Reply