Sorting in Spreadsheet Documents
- 6 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
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 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 cell range by font color and cell’s fill parameters.
Sort by Font Color
Use the required 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 the 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 the 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 criteria. 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 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 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: