Setting Excel worksheet protection with EPPlus

c#, epplus, excel, worksheet

Solution

Here is the source:

https://github.com/pruiz/EPPlus/blob/master/EPPlus/ExcelSheetProtection.cs

Setting the `IsProtected` property is overwriting your choices:

 public bool IsProtected
    {
        get
        {
            return GetXmlNodeBool(_isProtectedPath, false);
        }
        set
        {
            SetXmlNodeBool(_isProtectedPath, value, false);
            if (value)
            {
                AllowEditObject = true;
                AllowEditScenarios = true;
            }
            else
            {
                DeleteAllNode(_isProtectedPath); //delete the whole sheetprotection node
            }
        }
    }

Move your `IsProtected = true` call to the start of the code or handle however you want, but you are accidently overriding your previous choice. I would look at the properties at that link to see which ones are going to override your existing selections.

Problem

I'm trying to set worksheet permissions for an XLSM file using EPPlus but it seems I can only set the default protection level, individual protections are not being set. For the record, I'm trying to accomplish programmatically method 1 in this article. Here's the code I'm using: ``` using (var p = new ExcelPackage("output.xlsm")) { var ws = p.Workbook.Worksheets["MySheet"]; // Set some cell values here // Filtering, sorting, protection ws.Cells[7, 1, 10, 5].AutoFilter = true; ws.View.FreezePanes(7, 1); ws.ProtectedRanges.Add("FilteredCells", new ExcelAddress(7, 1, 10, 5)); // Worksheet protection ws.Protection.AllowAutoFilter = true; ws.Protection.AllowDeleteColumns = false; ws.Protection.AllowDeleteRows = false; ws.Protection.AllowEditObject = false; ws.Protection.AllowEditScenarios = false; ws.Protection.AllowFormatCells = false; ws.Protection.AllowFormatColumns = false; ws.Protection.AllowFormatRows = false; ws.Protection.AllowInsertColumns = false; ws.Protection.AllowInsertHyperlinks = false; ws.Protection.AllowInsertRows = false; ws.Protection.AllowPivotTables = false; ws.Protection.AllowSelectLockedCells = false; ws.Protection.AllowSelectUnlockedCells = true; ws.Protection.AllowSort = true; ws.Protection.IsProtected = true; ws.Protection.SetPassword("hunter2"); p.SaveAs(new FileInfo("output.xlsm")); } ``` This runs without errors, but when I open the file in Excel, or load it back into EPPlus, I find that different protection options have been applied: ``` AllowAutoFilter = false AllowDeleteColumns = false AllowDeleteRows = false AllowEditObject = true AllowEditScenarios = true AllowFormatCells = false AllowFormatColumns = false AllowFormatRows = false AllowInsertColumns = false AllowInsertHyperlinks = false AllowInsertRows = false AllowPivotTables = false AllowSelectLockedCells = true AllowSelectUnlockedCells = true AllowSort = false IsProtected = true ``` These obviously aren't the permissions I set before, so how can I make sure they are set correctly? Everything else is saved correctly.

Original source