Sort Data in Spreadsheet Documents
- 6 minutes to read
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.
Sort Data in the User Interface
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.
Sort Data in the Filter Menu
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.
Sort Cell Ranges in Code
Sort Data in Ascending or Descending Order
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.
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"];
Sort a Range by a Single Column
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);
Sort Range by Color
You can sort the cell range by font color and cell’s fill parameters.
Sort by Font Color
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);
Sort by Fill Parameters
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);
Sort a Range by Multiple Columns
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:
- SortField - to sort a column by value.
- FillSortField - to sort a column by fill color.
- FontColorSortField - to sort a column by font color.
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);
Sort Table Columns in Code
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: