Protect Excel With Allow Sort and Allow Auto Filter C#

Question

I'm using EPPLUS.dll for doing my excel works I tried to protect the worksheet using AllowSort and AllowAutoFilter Properties but it's not working.

worksheet.Protection.AllowSort = true;
worksheet.Protection.AllowAutoFilter = true;
worksheet.Protection.SetPassword("password");
worksheet.Protection.IsProtected = true;

I tried Below Code too but i didn't get my desired result

worksheet.Column(1).Style.Locked = true;

Epplus Lock Cells By default and Locking Cells will not Fix my Problem; the only thing I need is a protected or read-only cell while sorting and Filtering Allowed

any help would be greatly appreciated.

Answer 1

The only way i Found was, using Visual Basic Code inside of that Excel Document which locked right-click and copy-paste options. unfortunately before excel opens the documents, that asks something about dangerous scripts and offer to disable running scripts. at last i was impossible in my case.

Answer 2

Have you tried moving the worksheet.Protection.IsProtected = true; to be the first statement in your block of code. Perhaps this is overriding your previous statements.

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

The below has worked for me in the past, as in it will protect the worksheet, i.e. make it read only

        worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
        worksheet.Protection.IsProtected = true;
        worksheet.View.FreezePanes(2, 1); // freeze header row
        worksheet.Protection.AllowSort = true;
        worksheet.Cells[worksheet.Dimension.Address].AutoFilter = true;
        worksheet.Protection.AllowAutoFilter = true;

I think to password protect it, it has to be against the package not the worksheet.

So use the .Save overload if saving the package.

package.Save("password");

Or if you're saving as a ByteArray then

package.GetAsByteArray("password");