Sorting
- 2 minutes to read
To sort data in a range in ascending or descending order, use the Worksheet.Sort method. Note that this 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 data by multiple columns, follow the steps below.
- 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 IComparer<T><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.
- Call the Worksheet.Sort method with two parameters: the first is the Range to be sorted, the other is the list of sort fields.
Note
A complete sample project is available at https://github.com/DevExpress-Examples/spreadsheet-document-server-api-part-2-t217615
Dim worksheet As Worksheet = workbook.Worksheets("SortSample")
workbook.Worksheets.ActiveWorksheet = worksheet
' 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)
The image below shows the result (the workbook is opened in Microsoft® Excel®). The cell range is sorted by the first and second columns in ascending order.