Protected worksheet with Epplus - need to allow filtering and sorting. Sorting not permitted

Question

The purpose of the following method is to style and protect a worksheet after data has been written to it with Epplus. The styling is correct, and the appropriate cells (in a particular row) are protected. It is designed to allow sorting and filtering despite the fact that the "header" row cells are protected.

In the generated spreadsheet, Excel allows filtering to be performed. However, sorting is met with an error message. How to solve this?

public void FormatSpreadsheet(ExcelWorksheet worksheet)
{
    ExcelRange range;

    // styling header cells...

    range.AutoFilter = true;

    // protect headers and allow sorting and filtering, amongst other things
    worksheet.Protection.IsProtected = true;
    worksheet.Protection.AllowAutoFilter = true;
    worksheet.Protection.AllowDeleteColumns = false;
    worksheet.Protection.AllowDeleteRows = true;
    worksheet.Protection.AllowEditObject = true;
    worksheet.Protection.AllowEditScenarios = true;
    worksheet.Protection.AllowFormatCells = true;
    worksheet.Protection.AllowFormatColumns = true;
    worksheet.Protection.AllowFormatRows = true;
    worksheet.Protection.AllowInsertColumns = false;
    worksheet.Protection.AllowInsertHyperlinks = false;
    worksheet.Protection.AllowInsertRows = true;
    worksheet.Protection.AllowPivotTables = false;
    worksheet.Protection.AllowSelectLockedCells = true;
    worksheet.Protection.AllowSelectUnlockedCells = true;
    worksheet.Protection.AllowSort = true;

    // set a random password so it's impossible for anybody to edit the protected cells...

    // autofit columns
    range = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, lastHeaderCol];
    range.AutoFitColumns();
}

Answer 1

You'll need to actually activate the AutoFilter rather than just allowing it. To filter using first row of your worksheet as the header, the range object you have above will work, you just need to set one more property:

range.AutoFilter = true;