Sorting ranges

EPPlus has a new interface and extended functionality for sorting ranges from version 5.7. With this interface you can either sort the rows of a range on one or multiple columns (top-down) or sort the columns of a range on one or multiple rows - in both cases sorting is based on cell values. Ascending as well as descending sort direction is supported. You can also use a custom list to define the sort order. EPPlus will also update the SortState in the Office Open Xml, so the configuration of your last sort operation on a worksheet will be visible in your spreadsheet program.

When EPPlus sorts a range hyperlinks, comments, formulas and metadata of the cells will be adjusted to the new cell address in the spreadsheet.

The Sort function

A range in EPPlus has a Sort function - this function has several different signatures but in this examples we will use the variant that takes a lambda (Action<RangeSortOptions>) as a parameter.

Examples sort rows by columns (top-down)

Sort on one column ascending

// The Column function takes the zero based column index in the range
worksheet.Cells["A1:D15"].Sort(x => x.SortBy.Column(0));

Sort on one column descending

// The Column function takes the zero based column index in the range
worksheet.Cells["A1:D15"].Sort(x => x.SortBy.Column(0, eSortOrder.Descending));

Sort on three columns, the two first ascending, the last descending

// The Column function takes the zero based column index in the range
worksheet.Cells["A1:D15"].Sort(x => 
   x.SortBy.Column(0)
   .ThenSortBy.Column(2)
   .ThenSortBy.Column(3, eSortOrder.Descending));

Sort on one column using a custom list

A custom list can be used when you want to define your own sort order. In this case we use a list for T-shirt sizes with sort order from S to XL.

// The Column function takes the zero based column index in the range
worksheet.Cells["A1:D15"].Sort(x => x.SortBy.Column(0).UsingCustomList("S", "M", "L", "XL"));

Note that if you sort using a custom list a sort order argument (ascending/descending) will be ignored if present. When using a custom list the set will always be sorted by the custom list in ascending order and non matching items will be sorted in ascending order below the items that matches the custom list.

Sort on three columns using a custom list on the second level

A custom list can be used when you want to define your own sort order. In this case we use a list for T-shirt sizes with sort order from S to XL.

// The Column function takes the zero based column index in the range
worksheet.Cells["A1:D15"].Sort(x => x.SortBy.Column(0)
                                     .ThenSortBy.Column(2).UsingCustomList("S", "M", "L", "XL")
                                     .ThenSortBy.Column(3));

Adding sort conditions without the fluent syntax.

Using the previous example.

var options = RangeSortOptions.Create();
var builder = options.SortBy.Column(0);
builder.ThenSortBy.Column(2).UsingCustomList("S", "M", "L", "XL");
builder.ThenSortBy.Column(3);
worksheet.Cells["A1:D15"].Sort(options);

Examples sort columns by rows (Left to right)

Sort on one row ascending

// The Row function takes the zero based row index in the range
worksheet.Cells["A1:D15"].Sort(x => x.SortLeftToRightBy.Row(0));

Sort on one row descending

// The Row function takes the zero based row index in the range
worksheet.Cells["A1:D15"].Sort(x => x.SortLeftToRightBy.Row(0, eSortOrder.Descending));

Sort on three rows, the two first ascending, the last descending

// The Rowfunction takes the zero based row index in the range
worksheet.Cells["A1:D15"].Sort(x => x.SortLeftToRightBy.Row(0)
                                     .ThenSortBy.Row(2)
                                     .ThenSortBy.Row(3, eSortOrder.Descending));

Sort on one row using a custom list

A custom list can be used when you want to define your own sort order. In this case we use a list for T-shirt sizes with sort order from S to XL.

// The Row function takes the zero based row index in the range
worksheet.Cells["A1:D15"].Sort(x => x.SortLeftToRightBy.Row(0).UsingCustomList("S", "M", "L", "XL"));

Note that if you sort using a custom list a sort order argument (ascending/descending) will be ignored if present. When using a custom list the set will always be sorted by the custom list in ascending order and non matching items will be sorted in ascending order to the right of the items that matches the custom list.

Ignore case

Here is an example of configure your sort to ignore case. In Excel this is set once for the entire sort, so it works the same with EPPlus for compatibility reasons. The SortState´s CaseSensitive property will be set to true if CompareOptions is either IgnoreCase or OrdinalIgnoreCase.

sheet.Cells["A1:E2"].Sort(x =>
{
    x.CompareOptions = CompareOptions.IgnoreCase;
    x.SortLeftToRightBy.Row(0).UsingCustomList("S", "M", "L").ThenSortBy.Row(1);
});

Sort state

With the new sorting functionality added in v 5.7 EPPlus also updates the worksheet's SortState. This means that the sort configuration will be visible in Excel - if you have autofilters on the row directly above the sorted range there will be arrows on the filter showing the sort direction.

You can read the sort state via the ExcelWorksheet.SortState property.

From EPPlus 6.1 there is a Clear() method on the SortState.

Samples

See Sample 1.4-C# or Sample 1.4-VB