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 example 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, eSortDirection.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, eSortDirection.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"));

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

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, eSortDirection.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, eSortDirection.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"));