Edit this page
Available for: UI for ASP.NET AJAX | UI for ASP.NET MVC | UI for WPF | UI for WinForms | UI for Silverlight

Sorting

This article describes what is sorting and how to work with it through the document model. It contains the following sections:

What is Sorting?

The sorting feature allows the user to arrange the data according to one or more sorting conditions.

The information about the sorting applied to a worksheet is contained in the worksheet property SortState which is of type SortState. Through it, you can set and modify the worksheet sorting conditions. The interface implemented by all sort conditions is ISortCondition.

SortState

The SortState class exposes the following public members:

  • Count: The number of sorting conditions currently applied.

  • SortConditions: The sorting conditions currently applied.

  • SortRange: Property of type CellRange representing the sorting range to which the sorting conditions are applied. The worksheet can have only one range sorted at a time. If no sorting is applied, the sort range is null.

  • void Set(CellRange sortRange, params ISortCondition[] sortConditions): Sets the specified sorting conditions to the specified range.

  • void Clear(): Removes all the sorting from the worksheet.

ISortCondition

All sorting conditions which can be applied to the sorted range implement the ISortCondition interface. The interface exposes the following members:

  • SortIndex: Gets the index of the column to which the sort condition is applied. The index is relative to the beginning of the sorted range.

  • IComparer Comparer: Determines the order of the sorted values.

  • object GetValue(Cells cells, int rowIndex, int columnIndex): Gets the value of the cell at the specified index. This value is used to determine how the cell containing the value should be ordered during the sorting.

The diagram in Figure 1 shows the different types of conditions, which inherit the ISortCondition interface, and the classes which implement them.

Figure 1: Types of conditions

Rad Spread Processing Features Sorting 01

OrderedSortCondition

The ordered sort condition is a type of condition which sorts the values in an ordered manner, in ascending or descending order. It is represented by the abstract class OrderedSortConditionBase.

This class has one additional member, other than the members of the ISortCondition interface:

  • SortOrder: The sort order. It can have one of these values:

    • Ascending

    • Descending

ValuesSortCondition

The values sort condition is a condition which uses the values of the cells to sort them.

Example 1 shows how to create a ValuesSortCondition.

[C#] Example 1: Create ValuesSortCondition

ValuesSortCondition condition = new ValuesSortCondition(0, SortOrder.Ascending);

This condition will use a predefined comparer to sort the values of the cells in the selected range in an intuitive ascending order. The result is visible from Figure 2.

Figure 2: Values sort result

Rad Spread Processing Features Sorting 02

CustomValuesSortCondition

Sometimes the behavior of the predefined comparers is not sufficient. In this case you may wish to use a custom values sort condition. With it, you can specify the order in which you'd like the values to be ordered.

Example 2 shows how to create a CustomValuesSortCondition.

[C#] Example 2: Create CustomValuesSortCondition

CustomValuesSortCondition condition = new CustomValuesSortCondition(0, new string[] { "regular", "1 day", "2 days", "express" }, SortOrder.Ascending);

Figure 3: Custom value sort result

Rad Spread Processing Features Sorting 03

ForeColorSortCondition

A fore color sort condition orders the cells according to the color of the text in them. Each condition can have one color which it sets on the top or on the bottom of the sorted order.

Example 3 demonstrates how to create a ForeColorSortCondition. This condition will sort the range by putting all cells with a red fore color on the top.

[C#] Example 3: Create ForeColorSortCondition

ForeColorSortCondition condition = new ForeColorSortCondition(0, new ThemableColor(Colors.Red), SortOrder.Ascending);

FillColorSortCondition

A fill color sort condition orders the cells according to their fill color. Each condition can have one fill which it sets on the top or on the bottom of the sorted order.

Example 4 shows how to create a FillColorSortCondition.

[C#] Example 4: Create FillColorSortCondition

FillColorSortCondition condition = new FillColorSortCondition(0, PatternFill.CreateSolidFill(Color.FromArgb(255, 181, 18, 27)), SortOrder.Ascending);

Figure 4 shows that this condition will sort the range by putting all cells with a red color on the top.

Figure 4: Fill color sort result

Rad Spread Processing Features Sorting 04

Setting Sorting Conditions

There are two ways to sort a range on a worksheet: using the SortState property of the worksheet, or through the cell selection. In both cases you need to create a sort condition and then apply it.

Note that unlike the case with Filtering , you can apply more than one sort condition on one column. In fact, this is what you need to do if you'd like to sort by more than one color.

Example 5 shows how to create three sorting conditions.

[C#] Example 5: Create Conditions

Worksheet worksheet = workbook.ActiveWorksheet;

CustomValuesSortCondition condition1 = new CustomValuesSortCondition(5, new string[] { "regular", "1 day", "2 days", "express" }, SortOrder.Ascending);
FillColorSortCondition condition2 = new FillColorSortCondition(7, PatternFill.CreateSolidFill(Color.FromArgb(255, 181, 18, 27)), SortOrder.Ascending);
FillColorSortCondition condition3 = new FillColorSortCondition(7, PatternFill.CreateSolidFill(Color.FromArgb(255, 94, 151, 50)), SortOrder.Ascending);

Further, Example 6 shows how to apply the sorting conditions through the SortState property.

[C#] Example 6: Set conditions through SortState

worksheet.SortState.Set(new CellRange(3, 0, 17, 7), condition1, condition2, condition3);

Alternatively, Example 7 shows how to apply the sorting conditions through the cell selection property.

[C#] Example 7: Set conditions through selection

worksheet.Cells[3, 0, 17, 7].Sort(condition1, condition2, condition3);

Whichever option you chose, the result will be the same. The conditions will be applied in the order you set them. In Figure 5 you can see that in this example the rows are rearranged first by the custom list given for column F. After that the red color is placed on top and the green color is placed after it in each section formed by the rows with same values in column F.

Figure 5: Set conditions result

Rad Spread Processing Features Sorting 05

Clearing the Sorting

In order to clear the sorting, you can use the Clear() method of the SortState property. There is no need to clear the old sort conditions if you want to set new ones, they will be cleared internally.

Example 8 shows how to clear the sorting.

[C#] Example 8: Clear sorting

worksheet.SortState.Clear();

See Also