Sort Excel sheet by column using EPPlus

Question

I'm using EPPlus to generate Excel workbooks.

I'm trying to figure out how to either:

1. Sort a worksheet by a specific column (the equivalent of clicking sort A-Z in Excel) or...
2. Set the sort order for a specific column's AutoFilter

Solution 1

EPPlus doesn't have the ability to sort by column: https://epplus.codeplex.com/workitem/14791

This isn't what you asked for, but in case this helps in excel interop you can sort by column like this:

Worksheet sheet = workBook.Sheets[1];
Range sortRange = sheet.Range["A1", "S100"];
sortRange.Sort(sortRange.Columns[5], Microsoft.Office.Interop.Excel.XlSortOrder.xlDescending);

This will sort the range A1:S100 by column E.

Solution 2

var startRow = 1;    
var startColumn= 1;
var endRow= 10;
var endColumn= 10;
var sortColumn = 5; //6th Column because index is ZeroBased.
using (ExcelRange excelRange = yourWorkSheet.Cells[startRow, startColumn, endRow, endColumn])
     {
         excelRange.Sort(sortColumn, true);
     }

Solution 3

Jan Kallman has published a sorting method in 4.5.1. You can see an example he shows here:

https://github.com/JanKallman/EPPlus/issues/78#issuecomment-349650208

    /// Sort the range by value
    /// </summary>
    /// <param name="columns">The column(s) to sort by within the range. Zerobased</param>
    /// <param name="descending">Descending if true, otherwise Ascending. Default Ascending. Zerobased</param>
    /// <param name="culture">The CultureInfo used to compare values. A null value means CurrentCulture</param>
    /// <param name="compareOptions">String compare option</param>
    public void Sort(int[] columns, bool[] descending=null, CultureInfo culture=null, CompareOptions compareOptions=CompareOptions.None)

Comments

[increddibelly]

You can sort the data before you feed it to the worksheet.

[Ted Nyberg]

I did use AutoFilter, but I couldn't see any way of setting its sort order setting?

[MrOodles]

AutoFilter is a property, but you use it here like a method. You also provide no explanation at all about how to use AutoFilter. Poor answer.

[dudeNumber4]

existingWorksheet.UsedRange.Sort( excel.Columns[5], XlSortOrder.xlDescending );

[Si8]

Unf the Office library doesn't work in server side only client side.

[Kiquenet]

autofilter for EpPlus ?