Sort Data in Spreadsheet Documents
- 5 minutes to read
The Spreadsheet Document API offers multiple ways to sort data in a worksheet. You can sort a multi-column range by a single column, use multiple sorting columns, and implement a custom comparer.
To sort data in a range, use the Worksheet.Sort method. This method has multiple overloads with different advantages.
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 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;
using (var workbook = new Workbook()) {
workbook.LoadDocument(@"Document.xlsx");
Worksheet worksheet = workbook.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.
Worksheet worksheet = workbook.Worksheets["SortSample"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// 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.
CellRange range = worksheet.Range["A3:F22"];
worksheet.Sort(range, fields);