EPPlus 5/6/7

Features and technical overview

Sorting ranges and tables

EPPlus

EPPlus has from version 5.7 enhanced capabilities for sorting tables and ranges.

Sorting ranges top to bottom
            
worksheet.Cells["A1:D15"].Sort(x => 
                    x.SortBy.Column(0)
                    .ThenSortBy.Column(2).UsingCustomList("S", "M", "L", "XL")
                    .ThenSortBy.Column(3, eSortOrder.Descending));
                
            
Sorting ranges left to right
            
// The Row function 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));
                
            
Sorting Excel tables
            
// 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"));

                
            
Sort state

EPPlus will update the sort state after each sort, so your spreadsheet program can visualize the sort parameters. See this example in our wiki


Auto sort on pivot tables - Sorting on data fields

EPPlus 5.7 adds support for auto sort via pivot areas.

        
rowField1 = pt3.RowFields.Add(pt3.Fields["Name"]);
var columnField1 = pt3.ColumnFields.Add(pt3.Fields["Country"]);
dataField = pt3.DataFields.Add(pt3.Fields["OrderValue"]);
dataField.Format = "#,##0";
//Add auto sort on the data field we have just added. We want to sort by the column field with value "Poland""
rowField1.SetAutoSort(dataField, eSortType.Ascending);

var conditionField = rowField1.AutoSort.Conditions.Fields.Add(columnField1);
//Before setting a reference to a value column we need to refresh the items cache.
columnField1.Items.Refresh();
conditionField.Items.AddByValue("Poland");