I am trying to apply sort and autofilter while protecting excel file in win c#. but, i am getting error. what is it..?

Hi GaniTPT, What did you use to access worksheets? I made a test by using EPPlus and it worked fine. Here is a test code you can refer to: ExcelPackage.LicenseContext = LicenseContext.Commercial; using (var p = new ExcelPackage(new FileInfo(@"C:\Users\Desktop\Test.xlsx"))) { var ws = p.Workbook.Worksheets["Sheet1"]; // 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.AllowSort = true; ws.Protection.IsProtected = true; ws.Protection.AllowAutoFilter = true; ws.Protection.SetPassword("hunter2"); p.SaveAs(new FileInfo(@"C:\Users\Desktop\Test1.xlsx")); } Best Regards, Daniel Zhang If the response is helpful, please click " Accept Answer " and upvote it. Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Sort and UseAutoFilter not working while Protect Excel sheet in win c#

Accepted answer

Daniel Zhang-MSFT 9,621 Reputation points

2021-02-24T03:01:52.22+00:00
Hi GaniTPT,
What did you use to access worksheets?
I made a test by using EPPlus and it worked fine.
Here is a test code you can refer to:

ExcelPackage.LicenseContext = LicenseContext.Commercial; using (var p = new ExcelPackage(new FileInfo(@"C:\Users\Desktop\Test.xlsx"))) { var ws = p.Workbook.Worksheets["Sheet1"]; // 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.AllowSort = true; ws.Protection.IsProtected = true; ws.Protection.AllowAutoFilter = true; ws.Protection.SetPassword("hunter2"); p.SaveAs(new FileInfo(@"C:\Users\Desktop\Test1.xlsx")); }

Best Regards,
Daniel Zhang

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
BeUnique 2,112 Reputation points

2021-02-24T04:50:02.187+00:00

Thanks Daniel.

I am using "Microsoft.Office.Interop.Excel" for all my excel related works.

Also, mostly used open source for all my requirement and projects.

i don't have any issue if "EPPlus" is OpenSource.

Can you pls. confirm "EPPlus" is a opensource or not...?

If it is Licensed version, i need some other alternative solution especially for open source...

pls. help.

below is my reference code.

xlApp = new Excel.Application(); xlWb = xlApp.Workbooks.Open(path); xlSht = (Excel.Worksheet)workbook.Sheets[ShtName]; //Not Working xlWb.Worksheets[ShtName].Protection.IsProtected = true; xlWb.Worksheets[ShtName].Protection.AllowSorting = true; xlWb.Worksheets[ShtName].Protection.AllowFiltering = true; //Not working xlWb.Worksheets[ShtName].Protect(XLwbpwd); xlWb.Save(); xlWb.Close(); xlApp.Quit();

Daniel Zhang-MSFT 9,621 Reputation points

2021-02-24T06:41:00.777+00:00

Hi @BeUnique ,
In a protected worksheet, sorting can only be performed on unlocked or unprotected cells.
And the cells to be filtered must be unlocked when the sheet is protected.
The AllowFiltering property and AllowSorting property can be set by using the Protect method arguments.
So you can use Worksheet.Protect(Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object) method to protect a worksheet.
About EPPlus 5, please refer to this link.
Best Regards,
Daniel Zhang

BeUnique 2,112 Reputation points

2021-02-24T07:44:41.117+00:00

Thanks for your reply.
Actually i am using defined template and updating values based on the condition.
I recieved template with protect mode.
First i have unprotect excel and then do some process and update cell values. In that template, there will be defined filter in the row of 10.
After unprotect the sheet, filter also enabled. ==> it is fine.
But, when i protect the sheet again, only filter and sort should enable is mandatory as per client requirement.
below is complete reference code

BeUnique 2,112 Reputation points

2021-02-24T07:50:18.4+00:00

xlApp = new Excel.Application(); xlWb = xlApp.Workbooks.Open(path); xlSht = (Excel.Worksheet)workbook.Sheets[ShtName]; foreach (Excel.Worksheet wsht1 in xlWb.Worksheets) { wsht1.Unprotect(XLwbpwd); //unprotect with password //Calculation and printing values in the cell here - after unprotect } // Received from other source, but not known //xlWb.Worksheets[ShtName].Protection.IsProtected = true; xlWb.Worksheets[ShtName].Protection.AllowSorting = true; xlWb.Worksheets[ShtName].Protection.AllowFiltering = true; // Received from other source, but not known //Finally protect the sheet with password xlWb.Worksheets[ShtName].Protect(XLwbpwd); //// Save Book and close xlWb.Save(); xlWb.Close(); xlApp.Quit();

Daniel Zhang-MSFT 9,621 Reputation points

2021-02-25T01:38:41.507+00:00

Hi @BeUnique ,
When you change xlWb.Worksheets[ShtName].Protection.AllowSorting = true to xlSht.Protection.AllowSorting = true.
It will appear compiler error CS0200 which means that you can't assign a value to the AllowSorting or AllowFiltering property, it is read only.
So I suggest you use Worksheet.Protect method to allow sorting and filtering.
Best Regards,
Daniel Zhang

BeUnique 2,112 Reputation points

2021-02-25T10:37:34.897+00:00

Thanks gain.

you are mentioning, "you can use Worksheet.Protect(Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object, Object) method to protect a worksheet"...?

Can you give some sample reference code for Worksheet.Protect method to allow sorting and filtering.

Daniel Zhang-MSFT 9,621 Reputation points

2021-02-26T07:40:06.707+00:00

Hi @BeUnique ,
Please refer to the following code:

// Allow sorting and filtering to be performed on the protected worksheet. if (workSheet.Protection.AllowFiltering == false& workSheet.Protection.AllowSorting == false) { workSheet.Protect(AllowFiltering: true,AllowSorting:true); }

Best Regards,
Daniel Zhang
Sign in to comment

Share via

Sort and UseAutoFilter not working while Protect Excel sheet in win c#

0 additional answers