Sorting Tables - DON-PHAM/EPPlus GitHub Wiki

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, 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
table.Sort(x => x.SortBy.Column(0).ThenSortBy.Column(2).ThenSortBy.ColumnNamed("Price", 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
table.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
table.Sort(x => x.SortBy.Column(0).ThenSortBy.Column(2).UsingCustomList("S", "M", "L", "XL").ThenSortBy.ColumnNamed("Price"));

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.

table.Sort(x =>
{
    x.CompareOptions = CompareOptions.IgnoreCase;
    x.SortBy.Column(0).UsingCustomList("S", "M", "L").ThenSortBy.Column(1);
});

Sort state

With the new sorting functionality added in v 5.7 EPPlus also updates the table's SortState. This means that the sort configuration will be visible in Excel. In this example we use the following sort:

table.Sort(x => x.SortBy.ColumnNamed("Size").UsingCustomList("S", "M", "L", "XL")
                    .ThenSortBy.ColumnNamed("Price", eSortOrder.Descending)
                    .ThenSortBy.Column(2).UsingCustomList("Blue", "Yellow"));

/Images/TableSort_2.png

Screenshot from Excel 2019

If you open the sort configuration in Excel 2019 (right click the table => Sort => Custom sort), the sort set by EPPlus will be filled in.

/Images/TableSort_1.png

You can read the Sort state via the ExcelTable.SortState property. Modifying it will not trigger a new sort by EPPlus.

Samples

See Sample 28 for .NET Core