How to Apply Filter to Protected Sheet in Excel file using C#

Aspose.Cells Product Family
,
1

Hi,

How to apply filter to protected sheet in Excel file using C#.NET via Aspose.Cells APIs? I am trying apply filter functionality to protected Excel sheet but it is not showing data in filter.
When I click on filter it display as Showing All.

Platform: .NET

------------------------------------Dummy Code---------------------------------

            this.worksheet.Cells[0, 0].PutValue("Product ID");
            this.worksheet.Cells[0, 1].PutValue("Product Name");
            this.worksheet.Cells[1, 0].PutValue(1);
            this.worksheet.Cells[2, 0].PutValue(2);
            this.worksheet.Cells[3, 0].PutValue(3);
            this.worksheet.Cells[4, 0].PutValue(4);

            this.worksheet.Cells[1, 1].PutValue("Apples");
            this.worksheet.Cells[2, 1].PutValue("Bananas");
            this.worksheet.Cells[3, 1].PutValue("Grapes");
            this.worksheet.Cells[4, 1].PutValue("Oranges");
            this.worksheet.AutoFilter.Range = "A1:B1";
            this.worksheet.AutoFilter.Refresh();

            workbook.Worksheets[0].Protect(ProtectionType.All, "test", null);

Current Ouput : -
output.zip (6.7 KB)
Current_Output.png (3.2 KB)

Expected Output as below:-
Expected_Output.png (8.3 KB)

Happy Coding :slight_smile:
Thanks

2

@sachinmali26,

This is not an issue with the APIs rather expected behavior. Please perform the same task in MS Excel, i.e., insert some data into the cells, then apply auto-filters to it. Now protect the sheet and you will get same behavior. In short, filtering data won’t work in protected sheet.

If you still think it is an issue in Aspose.Cells APIs, kindly do share your expected file (filters intact and work while sheet is protected) here. We will check on how to do it via Aspose.Cells APIs.

PS. please zip the file prior attaching.

3

@sachinmali26,

After further evaluation of your task in MS Excel manually, we found there is an option to allow filtering data while sheet is protected. Please try to add a line to your code:

workbook.Worksheets[0].Protection.AllowFiltering = true;
workbook.Worksheets[0].Protect(ProtectionType.All, "test", null);

Let us know if you still have any queries or issue.

1 Like
4

Thanks :slight_smile: Resolved

5

@sachinmali26,

Good to know that it figures out your now. In the event of further queries, feel free to write us back.