Protect Excel worksheet while keeping autofilter, using Spreadsheet gear

Would anyone know how to use protect an Excel worksheet while still allowing the use of the Autofilter? Normally it's straightforward, but in the current implementation of the project I'm working on, it has to be done using the Spreadsheetgear extension, which apparently doesn't have this level of detail in it.

Thanks everybody,

Dan M.

Solution

UPDATE

SpreadsheetGear 2023 / V9 now supports fine-grained worksheet protection and so can allow AutoFilters to be used while protecting other aspects of a workbook. Example:

// Open a workbook with AutoFilters and access its IWorksheet.
IWorkbook workbook = Factory.GetWorkbook(@"C:\path\to\workbook.xlsx");
IWorksheet worksheet = workbook.ActiveWorksheet;

worksheet.Protect(
    // Specify null if you don't want to protect the sheet with a password.
    password: "MyPassword1234", 

    // Enables worksheet protection
    protectContents: true,

    // Allow AutoFilters
    allowFiltering: true,

    // Restrict other options as desired.
    allowFormattingColumns: false,
    allowFormattingRows: false,
    allowFormattingCells: false,
    allowInsertingColumns: false,
    allowInsertingRows: false,
    allowInsertingHyperlinks: false,
    allowDeletingColumns: false,
    allowDeletingRows: false,
    allowSorting: false,
    allowUsingPivotTables: false,
    protectDrawingObjects: true,
    protectScenarios: true,

    // True enforces protection only on the user interface (i.e., WorkbookView) and
    // allows your code to make API calls to modify the protected worksheet.  False 
    // will not even let your own code make modifications.
    userInterfaceOnly: true
);

Please see the SpreadsheetGear API Samples > Worksheet > Protection sample for more examples of using this new API. See also the following documentation links:

IWorksheet.Protection property and IProtection interface.
IProtection.AllowFormattingRows / AllowFormattingColumns
IWorksheet.Protect(...) method

ORIGINAL ANSWER

Excel provides a number of "fine-grained protection options" which allow the user to pick and choose what should or should not be protected in a worksheet, such as allowing AutoFilter operations. SpreadsheetGear offers general worksheet protection, but does not yet support choosing protection options at this fine grained level. So enabling AutoFilters with worksheet protection enabled is not possible, unfortunately.

We do keep track of these sorts of requests (I work for SpreadsheetGear). You are welcome to contact us at support@spreadsheetgear.com so that I can add your contact information to this feature request and let you know when we do move forward on this.