question

GaniTPT avatar image
0 Votes"
GaniTPT asked ·

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

I am trying to apply sort and autofilter while protecting excel file in win c#.

but, i am getting error. what is it..?

71040-image.png


dotnet-csharp
image.png (38.0 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GaniTPT avatar image
0 Votes"
GaniTPT answered ·

Any update pls.

· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GaniTPT avatar image
0 Votes"
GaniTPT answered ·

pls. update. we are unable to find the exact result in win c#.

· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered ·

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.


· 7 · Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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();


0 Votes 0 ·

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


0 Votes 0 ·
GaniTPT avatar image GaniTPT DanielZhang-MSFT ·

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

0 Votes 0 ·
Show more comments