wpf-17471-controls-and-libraries-spreadsheet-data-presentation-sorting.md
The SpreadsheetControl can sort data in a range based on values in a specified column. The sorted column can contain data of different types: text (sorted alphabetically, according to the current culture), numbers, dates, colors, and times.
Select a cell range in a column. On the Data tab, in the Sort & Filter group, click Sort A to Z for ascending order or Sort Z to A for descending order.
If you select multiple columns, the first column supplies sorting values.
Use the filter menu to sort data. Once you activate filtering, a drop-down arrow appears on the right side of each column header in the range.
The AutoFilter menu allows a user to sort cells by color. Click a drop-down arrow to open the menu and select Sort by Color. You can sort data by cell background or font color.
When you select the required sorting option, a dialog with available colors appears. Select the target color and click OK.
The Worksheet.Sort(CellRange) method sorts the cell range passed as a parameter in ascending order. Pass false as the second method parameter to sort the data in descending order.
View Example: WPF Spreadsheet API: How to Process Excel Workbooks in Code
Worksheet worksheet = workbook.Worksheets[0];
// Fill in the range.
worksheet.Cells["A2"].Value = "Donald Dozier Bradley";
worksheet.Cells["A3"].Value = "Tony Charles Mccallum-Geteer";
worksheet.Cells["A4"].Value = "Calvin Liu";
worksheet.Cells["A5"].Value = "Anita A Boyd";
worksheet.Cells["A6"].Value = "Angela R. Scott";
worksheet.Cells["A7"].Value = "D Fox";
// Sort the "A2:A7" range in descending order.
CellRange range = worksheet.Range["A2:A7"];
worksheet.Sort(range, false);
// Create a heading.
CellRange header = worksheet.Range["A1"];
header[0].Value = "Descending order";
header.ColumnWidthInCharacters = 30;
header.Style = workbook.Styles["Heading 1"];
Dim worksheet As Worksheet = workbook.Worksheets(0)
' Fill in the range.
worksheet.Cells("A2").Value = "Donald Dozier Bradley"
worksheet.Cells("A3").Value = "Tony Charles Mccallum-Geteer"
worksheet.Cells("A4").Value = "Calvin Liu"
worksheet.Cells("A5").Value = "Anita A Boyd"
worksheet.Cells("A6").Value = "Angela R. Scott"
worksheet.Cells("A7").Value = "D Fox"
' Sort the "A2:A7" range in descending order.
Dim range As CellRange = worksheet.Range("A2:A7")
worksheet.Sort(range, False)
' Create a heading.
Dim header As CellRange = worksheet.Range("A1")
header(0).Value = "Descending order"
header.ColumnWidthInCharacters = 30
header.Style = workbook.Styles("Heading 1")
The Sort method’s columnOffset parameter lets you specify which column sorts the cell range. It counts the column index starting from the first column in the range. For instance, in the “B3:F22” cell range, column B has an offset of 0.
The following code sample sorts the “A3:F22” range by column “D” in ascending order (from smallest to largest).
Worksheet worksheet = workbook.Worksheets["SortSample"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Sort the "A3:F22" range by column "D" in ascending order.
CellRange range = worksheet.Range["A3:F22"];
worksheet.Sort(range, 3);
Dim worksheet As Worksheet = workbook.Worksheets("SortSample")
workbook.Worksheets.ActiveWorksheet = worksheet
' Sort the "A3:F22" range by column "D" in ascending order.
Dim range As CellRange = worksheet.Range("A3:F22")
worksheet.Sort(range, 3)
You can sort cell range by font color and cell’s fill parameters.
Use the Color object as the Sort method’s fontColor parameter to sort cells by font color. You can also specify the order in which the range should be sorted.
The following code sample sorts the cell range by font color in column F , in ascending order:
Worksheet worksheet = workbook.Worksheets["SortSample"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Sort the "A3:F22" range by column "F" in ascending order.
CellRange range = worksheet.Range["A3:F22"];
worksheet.Sort(range,5, worksheet["F12"].Font.Color);
Dim worksheet As Worksheet = workbook.Worksheets("SortSample")
workbook.Worksheets.ActiveWorksheet = worksheet
' Sort the "A3:F22" range by column "F" in ascending order.
Dim range As CellRange = worksheet.Range("A3:F22")
worksheet.Sort(range,5, worksheet("F12").Font.Color)
Use the Fill object as a fillColor parameter of the Sort method to sort data by fill parameters - background color, pattern, gradient and so on.
The following code sample sorts the cell range by background color in column E , in descending order.
using DevExpress.Spreadsheet;
spreadsheetControl.LoadDocument(@"Document.xlsx");
Worksheet worksheet = spreadsheetControl.Document.Worksheets["SortSample"];
workbook.Worksheets.ActiveWorksheet = worksheet;
CellRange range = worksheet.Range["A3:F22"];
Fill fillSettings = worksheet["F15"].Fill;
worksheet.Sort(range, 5, fillSettings, false);
Imports DevExpress.Spreadsheet
spreadsheetControl.LoadDocument("Document.xlsx")
Dim worksheet As Worksheet = spreadsheetControl.Document.Worksheets("SortSample")
workbook.Worksheets.ActiveWorksheet = worksheet
Dim range As CellRange = worksheet.Range("A3:F22")
Dim fillSettings As Fill = worksheet("F15").Fill
worksheet.Sort(range, 5, fillSettings, False)
To sort data by multiple columns, use the Worksheet.Sort(CellRange, IEnumerable<SortFieldBase>) method overload.
Each SortFieldBase object in the list defines a column within the range to be sorted and a sort criterion. Use the SortFieldBase.ColumnOffset property to specify the position of the target column within the range. You can sort columns by value, fill color, and font color. You can create the following objects for each sort type:
The following code sample sorts the cell range by the first and second columns in ascending order.
workbook.LoadDocument("Documents\\Sortsample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
// Create sorting fields.
List<SortFieldBase> fields = new List<SortFieldBase>();
// 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);
workbook.LoadDocument("Documents\Sortsample.xlsx")
Dim worksheet As Worksheet = workbook.Worksheets(0)
' Create sorting fields.
Dim fields As New List(Of SortFieldBase)()
' 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)
Use the Table.AutoFilter property to obtain the TableAutoFilter object, which specifies filtering options for a table. The TableAutoFilter object inherits from the AutoFilterBase base interface, which contains basic methods and properties used to sort table data similarly for a worksheet range. Use the AutoFilterBase.SortState property to apply sorting.
The following code snippet applies sorting to a column with the specified name:
Worksheet worksheet = spreadsheetControl1.ActiveWorksheet;
int colIndex = table.Columns.Where(col => col.Name == "Name").First().Index;
table.AutoFilter.SortState.Sort(colIndex, true);
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)