Write protect columns but allow Auto-Filter in Excel created with ClosedXML

Question

I am using ClosedXML with C# to create a Excel Worksheet.

The requirement is to

• Allow Auto-Filer on all columns
• Allow edit only form column "B", write protect the remaining columns
• Allow column formatting and addition of columns.

The following code though write-protects all the columns, and disables the use of the Auto-Filter (even though the dropdown icon is visible).

var exWB = new XLWorkbook();
var exWS = exWB.Worksheets.Add("Sheet1");
exWS.Cell("A1").Value = "ID";
exWS.Cell("B1").Value = "Name";

exWS.Cell("A2").Value = "25";
exWS.Cell("B2").Value = "Fred";

exWS.Cell("A3").Value = "28";
exWS.Cell("B3").Value = "Burt";

.
.

exWS.Columns().AdjustToContents();
exWS.RangeUsed().SetAutoFilter();
exWS.Protect();
exWS.Column("B").Style.Protection.SetLocked(false);


exWB.SaveAs("my.xlsx");

This gives a spreadsheet where column A is locked but B is not as per requirement, but the auto-filter does not open the options (stuck on ALL).

Answer 1

The answer was to use the AutoFilter option on the Protect command (I also need to allow FormatCells and InsertColumns for another requirement).

exWS.Columns().AdjustToContents();
exWS.RangeUsed().SetAutoFilter();
exWS.Protect().AllowElement(XLSheetProtectionElements.FormatCells)
              .AllowElement(XLSheetProtectionElements.InsertColumns)
              .AllowElement(XLSheetProtectionElements.AutoFilter);
exWS.Column("B").Style.Protection.SetLocked(false);