Sort Data in Spreadsheet
- 3 minutes to read
Sorting in User Iinterface
The ASPxSpreadsheet 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 the ascending order or click Sort Z to A for the descending order.
If multiple columns are selected, the range is sorted by the first column.
The Spreadsheet API provides a Worksheet.Sort method, which allows for sorting in an 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 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.
workbook.LoadDocument("Documents\\Sortsample.xlsx"); Worksheet worksheet = workbook.Worksheets; // 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);