C# Protect sheet content, but allow to sort and filter

[Den12345678]

Want to be able to protet a sheet, so readers cannot alter the data, cannot insert rows and columns, etc.
But are allowed to filter and sort.

I have a table on a sheet, A2:U20.
Row 1 has a title on it.
Row 2 has the filter & sorting functionality (of the table), and the other rows have content.

Existing code:
ws.AddAllowEditRange("Range One", ws.Range["A2:S2"]);
ws.Protect("xxxxxxx", SheetProtectionType.Sorting);

Tried things like:
ws.Protect("xxxxxx", SheetProtectionType.All | SheetProtectionType.DeletingRows | SheetProtectionType.DeletingColumns);
ws.Protect("xxxxxx", SheetProtectionType.None | SheetProtectionType.DeletingRows | SheetProtectionType.DeletingColumns);
ws.Protect("xxxxxx", SheetProtectionType.Sorting | SheetProtectionType.DeletingRows | SheetProtectionType.DeletingColumns);
ws.Protect(SheetProtectionType.DeletingRows);

But with no succes.

[Abel.He]

Hi,

Thanks for your inquiry.
For your requirement, first you need to set AllowEditRange to A2:U20, instead of A2:S2, than set the second paramter of Protect method to SheetProtectionType.Sorting | SheetProtectionType.Filtering. I put the complete code below for your reference.

Code: Select all

// Create a new workbook object
            Workbook workbook = new Workbook();

            // Load an existing Excel document from file
            workbook.LoadFromFile(@"..\..\data\input.xlsx");

            // Get the first worksheet from the workbook
            Worksheet sheet = workbook.Worksheets[0];

            // Define the specified ranges that allow users to edit while the sheet is protected
            sheet.AddAllowEditRange("EditableRanges", sheet.Range["A2:U20"]);

            // Protect the worksheet with a password
            sheet.Protect("TestPassword", SheetProtectionType.Sorting | SheetProtectionType.Filtering);


            // Specify the output filename for the workbook
            String result = @"../../output/output_result.xlsx";

            // Save the modified workbook to a file
            workbook.SaveToFile(result, ExcelVersion.Version2010);

            // Dispose of the workbook object to release resources
            workbook.Dispose();

Sincerely
Abel
E-iceblue support team

[Den12345678]

Thank you very much Abel for this perfect solution. !!!!

Hi,

Thanks for your inquiry.
For your requirement, first you need to set AllowEditRange to A2:U20, instead of A2:S2, than set the second paramter of Protect method to SheetProtectionType.Sorting | SheetProtectionType.Filtering. I put the complete code below for your reference.
....
Sincerely
Abel
E-iceblue support team

[Abel.He]

Hi,

Thanks for your feedback.
You're welcome! If you have any issues in the future, just feel free to write back.

Sincerely,
Abel
E-iceblue support team