How to use Worksheet.Sort in the xlwings API way

The Sort member of the Worksheet object in the xlwings library provides a powerful way to programmatically sort data ranges within an Excel worksheet. It directly leverages Excel’s native sorting capabilities, allowing for multi-key sorts with custom orders. This is essential for automating data organization tasks in reports or datasets directly from Python.

Functionality:
The primary function is to sort a specified range of cells. You can define one or more sort keys (columns or rows to sort by), specify the sort order (ascending or descending), and, for advanced cases, even apply custom sort orders (like sorting by day-of-week or month name). It operates similarly to the sort functionality in the Excel UI but is controlled via code.

Syntax:
The basic syntax in xlwings is:

worksheet.api.Sort.SortFields.Clear()
worksheet.api.Sort.SortFields.Add(
Key=range_to_sort_by,
SortOn=sort_on_parameter,
Order=sort_order,
CustomOrder=custom_order,
DataOption=data_option
)
worksheet.api.Sort.SetRange(range_to_sort)
worksheet.api.Sort.Header = header_setting
worksheet.api.Sort.MatchCase = match_case_setting
worksheet.api.Sort.Orientation = orientation
worksheet.api.Sort.SortMethod = sort_method
worksheet.api.Sort.Apply()

Key parameters include:

• Key: An xlwings Range object representing the column or row that serves as the sort key.
• Order: Use win32c.SortOrder.xlAscending (1) or win32c.SortOrder.xlDescending (2).
• Header: Specifies if the first row contains headers. Use win32c.YesNoGuess.xlYes (1), xlNo (2), or xlGuess (0).
• Orientation: Defines if sorting is by column (win32c.SortOrientation.xlSortColumns, 1) or by row (xlSortRows, 2).
• SortOn: Typically win32c.SortOn.xlSortOnValues (0).
• CustomOrder: A string indicating a custom list (e.g., “Low,Medium,High”).
• SetRange: The main Range object encompassing all cells to be sorted.

Code Examples:

1. Simple Single-Column Sort:
   Sort data in range A1:C10 based on values in column A (the first column) in ascending order, assuming the first row is a header.

import xlwings as xw
from xlwings.constants import SortOrder, YesNoGuess

app = xw.App(visible=False)
wb = app.books.open('data.xlsx')
ws = wb.sheets['Sheet1']
sort_range = ws.range('A1:C10')

ws.api.Sort.SortFields.Clear()
ws.api.Sort.SortFields.Add(Key=ws.range('A1:A10'), Order=SortOrder.xlAscending)
ws.api.Sort.SetRange(sort_range.api)
ws.api.Sort.Header = YesNoGuess.xlYes
ws.api.Sort.Apply()

wb.save()
wb.close()
app.quit()

2. Multi-Column Sort:
   Sort range A1:D100 first by column C (descending), then by column A (ascending).

import xlwings as xw
from xlwings.constants import SortOrder, YesNoGuess

app = xw.App(visible=False)
wb = app.books.open('report.xlsx')
ws = wb.sheets[0]
main_range = ws.range('A1:D100')

ws.api.Sort.SortFields.Clear()
# First key: Column C
ws.api.Sort.SortFields.Add(Key=ws.range('C1:C100'), Order=SortOrder.xlDescending)
# Second key: Column A
ws.api.Sort.SortFields.Add(Key=ws.range('A1:A100'), Order=SortOrder.xlAscending)
ws.api.Sort.SetRange(main_range.api)
ws.api.Sort.Header = YesNoGuess.xlYes
ws.api.Sort.Apply()

wb.save()
app.quit()

3. Sort with Custom Order:
   Sort a list in A1:A20 by the custom priority “High”, “Medium”, “Low”.

import xlwings as xw
from xlwings.constants import YesNoGuess

app = xw.App(visible=False)
wb = app.books.open('tasks.xlsx')
ws = wb.sheets['Priority']

sort_range = ws.range('A1:A20')
ws.api.Sort.SortFields.Clear()
ws.api.Sort.SortFields.Add(Key=sort_range.api,
CustomOrder="High,Medium,Low")
ws.api.Sort.SetRange(sort_range.api)
ws.api.Sort.Header = YesNoGuess.xlNo # No header in this single column
ws.api.Sort.Apply()

wb.save()
app.quit()