Sorting in Spreadsheet Documents

• May 26, 2023
• 3 minutes to read

Sort Data in the User Interface

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.

Sort Cell Ranges in Code

Use the Worksheet.Sort method to sort in ascending or descending order (in the same manner as the sort commands available to the user). This method has several overloads. 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, 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.

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

  

View Example

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.
CellRange range = worksheet.Range["A3:F22"];
worksheet.Sort(range, fields);

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 CellRange = worksheet.Range("A3:F22")
worksheet.Sort(range, fields)

Sort Table Columns in Code

Use the Table.AutoFilter property to obtains the TableAutoFilter object, which specifies filtering options for a table. The TableAutoFilter object inherits from the AutoFilterBase base interface that contains basic methods and properties used to sort table data in the same manner as it can be done for a worksheet range. Use the AutoFilterBase.SortState property apply sorting.

The code sample below applies sorting to a column with the specified name:

C#

Worksheet worksheet = spreadsheetControl1.ActiveWorksheet;

int colIndex = table.Columns.Where(col => col.Name == "Name").First().Index;
table.AutoFilter.SortState.Sort(colIndex, true);

VB.NET

Dim worksheet As Worksheet = spreadsheetControl1.ActiveWorksheet

Dim colIndex As Integer = table.Columns.Where(Function(col) col.Name = "Name").First().Index
table.AutoFilter.SortState.Sort(colIndex, True)