**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

```csharp
// 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")
```

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

### Sort on one column descending

```csharp
// The Column function takes the zero based column index in the range
table.Sort(x => x.SortBy.Column(0, eSortOrder.Descending));
```

```vbnet
' The Column function takes the zero based column index in the range
table.Sort(Sub(x) x.SortBy.Column(0, eSortOrder.Descending))
```

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

```csharp
// 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));
```

```vbnet
' The Column function takes the zero based column index in the range
table.Sort(Sub(x)
               x.SortBy.Column(0) _
                   .ThenSortBy.Column(2) _
                   .ThenSortBy.ColumnNamed("Price", eSortOrder.Descending)
           End Sub)
```

### 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.

```csharp
// The Column function takes the zero based column index in the range
table.Sort(x => x.SortBy.Column(0).UsingCustomList("S", "M", "L", "XL"));
```

```vbnet
' The Column function takes the zero based column index in the range
table.Sort(Sub(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.

```csharp
// 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"));
```

```vbnet
' The Column function takes the zero based column index in the range
table.Sort(Sub(x)
               x.SortBy.Column(0) _
                   .ThenSortBy.Column(2).UsingCustomList("S", "M", "L", "XL") _
                   .ThenSortBy.ColumnNamed("Price")
           End Sub)
```

## 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`.

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

```vbnet
table.Sort(Sub(x)
               x.CompareOptions = CompareOptions.IgnoreCase
               x.SortBy.Column(0).UsingCustomList("S", "M", "L").ThenSortBy.Column(1)
           End Sub)
```

## 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:

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

```vbnet
table.Sort(Sub(x)
               x.SortBy.ColumnNamed("Size").UsingCustomList("S", "M", "L", "XL") _
                   .ThenSortBy.ColumnNamed("Price", eSortOrder.Descending) _
                   .ThenSortBy.Column(2).UsingCustomList("Blue", "Yellow")
           End Sub)
```

*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.

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

## See also

- [Sample 7.1-C\#](<https://github.com/EPPlusSoftware/EPPlus.Samples.CSharp/blob/master/07-Tables pivot tables and slicers/01-Tables/SortingTablesSample.cs>) or [Sample 7.1-VB](<https://github.com/EPPlusSoftware/EPPlus.Samples.VB/blob/master/07-Tables pivot tables and slicers/01-Tables/SortingTablesSample.vb>)
- [Tables](<https://epplussoftware.com/docs/tables-and-pivot-tables/tables/index.md>)