This project has moved and is read-only. For the latest updates, please go here.

AutoFilter and protected ranges don't seem to want to play together

Jul 2, 2014 at 12:11 AM
I have to create a spreadsheet, add auto filtering to the columns, protect the sheet and allow sorting, all easy enough except I can't get my code working :-)

Here's an example
using (var package = new ExcelPackage(newFile))
    var worksheet = package.Workbook.Worksheets.Add("Yo!");

    worksheet.Protection.AllowDeleteColumns =
        worksheet.Protection.AllowDeleteRows = 
        worksheet.Protection.AllowSelectLockedCells = false;
    worksheet.Protection.AllowSort =
        worksheet.Protection.AllowFormatColumns =
            worksheet.Protection.AllowAutoFilter = 
            worksheet.Protection.AllowPivotTables = true;


    worksheet.Cells["A1"].Value = "Column1";
    worksheet.Cells["B1"].Value = "Column2";
    worksheet.Cells["C1"].Value = "Column3";
    worksheet.Cells["D1"].Value = "Column4";
    worksheet.Cells["E1"].Value = "Column5";

    worksheet.Cells["A2:E4"].Value = 1;
    worksheet.Cells["A1:E1"].AutoFilter = true;
    worksheet.ProtectedRanges.Add("XXX", new ExcelAddress("A2:E4"));

If I run this the excel doesn't open, if I remove either the AutoFilter line OR the ProtectedRanges.Add line all is good??

Anyone point me in the right direction please?