Skip to main content

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.

sort data 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).

sort data by single column

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:

sort data by font color

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.

sort data by fill

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:

The following code sample sorts the cell range by the first and second columns in ascending order.

SpreadsheetDocServer_SortResult

View Example

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);