Sorting

• Jun 07, 2019
• 3 minutes to read

End-User Sorting

The SpreadsheetControl provides the capability to sort data by text (alphabetically, according to the current culture), numbers, dates and times in one column. The end-user can select a range in one column and on the Data tab, in the Sort & Filter group, click Sort A to Z for ascending order or click Sort Z to A for descending order.

If multiple columns are selected, the range is sorted by the first column.

Sorting Programmatically

The Spreadsheet API provides a Worksheet.Sort method, which allows sorting in ascending or descending order in the same manner as the sort commands available to the end-user. However, the method has several overloads with serious advantages. You can specify the column by which to sort a multi-column range, sort by multiple columns or use a custom comparer.

To sort by multiple columns, perform the following steps.

• Create the SortField object for each of the columns from which to sort. Specify the position of this column within the range using the SortField.ColumnOffset property and assign a comparer using the SortField.Comparer property. Instead of implementing your own comparer with the System.Collections.Generic.IComparer<CellValue> interface, you can use one of the built-in comparers which are responsible for sort operations performed by end-users. There are two built-in comparers - Worksheet.Comparers.Ascending is used for sorting in ascending order; Worksheet.Comparers.Descending is used for sorting in descending order.
• Create a list containing sort fields.

• Use the Worksheet.Sort method with two parameters - the first is the Range to sort, the other is the list of sort fields.

  

Note

A complete sample project is available at https://github.com/DevExpress-Examples/wpf-spreadsheetcontrol-api-part-2-e4944.

SortActions.vb

workbook.LoadDocument("Documents\Sortsample.xlsx")
Dim worksheet As Worksheet = workbook.Worksheets(0)

' Create sorting fields.
Dim fields As New List(Of SortField)()

' First sorting field. First column (offset = 0) will be sorted using ascending order.
Dim sortField1 As New SortField()
sortField1.ColumnOffset = 0
sortField1.Comparer = worksheet.Comparers.Ascending
fields.Add(sortField1)

' Second sorting field. Second column (offset = 1) will be sorted using ascending order.
Dim sortField2 As New SortField()
sortField2.ColumnOffset = 1
sortField2.Comparer = worksheet.Comparers.Ascending
fields.Add(sortField2)

' Sort the range by sorting fields.
Dim range As Range = worksheet.Range("A3:F22")
worksheet.Sort(range, fields)

SortActions.cs

workbook.LoadDocument("Documents\\Sortsample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];

// Create sorting fields.
List<SortField> fields = new List<SortField>();

// First sorting field. First column (offset = 0) will be sorted using ascending order.
SortField sortField1 = new SortField();
sortField1.ColumnOffset = 0;
sortField1.Comparer = worksheet.Comparers.Ascending;
fields.Add(sortField1);

// Second sorting field. Second column (offset = 1) will be sorted using ascending order.
SortField sortField2 = new SortField();
sortField2.ColumnOffset = 1;
sortField2.Comparer = worksheet.Comparers.Ascending;
fields.Add(sortField2);

// Sort the range by sorting fields.
Range range = worksheet.Range["A3:F22"];
worksheet.Sort(range, fields);