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

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.
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();
Hi @GaniTPT,
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
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
6 people are following this question.
Insert a node as child ,before or after a node in nested dynamic JSON Node using C#
Visual Studio 2019: Undefined behavior in a C++/CLI wrapper project.
Example for how to get Package Metadata from Azure DevOps Rest-Api Artifacts using c#
How to collapse individual nested grids/stackpanels inside a grid?