Sorting Tables

EPPlus has a new interface for sorting tables from version 5.7. With this interface you can sort a table on one or multiple columns based on cell values in ascending or descending order. You can also use a custom list to define the sort order. EPPlus will also update the table's SortState in the Office Open Xml, so the configuration of your last sort operation on a worksheet will be visible in your spreadsheet program.

The Sort function

A table (the ExcelTable class) in EPPlus has a Sort function - this function has different signatures but in this example we will use the variant that takes a lambda (Action<TableSortOptions>) as a parameter.

Examples

Sort on one column ascending

// The Column function takes the zero based column index in the range
table.Sort(x => x.SortBy.Column(0));
// You can also use the _ColumnNamed(string columnName) function to sort on a column
table.Sort(x => x.SortBy.ColumnNamed("Size")

Sort on one column descending

// The Column function takes the zero based column index in the range
table.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
table.Sort(x => x.SortBy.Column(0).ThenSortBy.Column(2).ThenSortBy.ColumnNamed("Price", 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
table.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
table.Sort(x => x.SortBy.Column(0).ThenSortBy.Column(2).UsingCustomList("S", "M", "L", "XL").ThenSortBy.ColumnNamed("Price"));