Free Support Forum - aspose.com

Protect a worksheet that has an AutoFilter

Aspose.Cells Product Family
#1

The code example below does allow the worksheet to be autofiltered but how is the worksheet then protected?

When I run this, the autofilter works but the worksheet is not protected. (I'm using excel 2003, Aspose.Cells v4.8.1.0, C#)

Kind regards
Andrew

Laurence:

To enable autofilter while protecting the worksheet, please try:

Excel.Worksheets(0).Protection.IsFilteringAllowed = True

Excel.Save("abc.xls", FileFormatType.ExcelXP)

Please don't use Protect method and save file format as ExcelXP or Excel2003.

#2

Hi,

You need to add one line if you need to protect the sheet in any case, if you skip this line, the worksheet won’t be protected with any settings you specify. Setting this option (“IsEditingContentsAllowed”) to false refers to when you manually click on the Protect Sheet option (in Tools|Protection menu in MS Excel 2003) and check the first option: “Protect worksheet and contents of the locked cells” for a file.


Excel.Worksheets(0).Protection.IsFilteringAllowed = True

Excel.Worksheets(0).Protection.IsEditingContentsAllowed = false;

Excel.Save("abc.xls", FileFormatType.ExcelXP)



Thank you.
#3

Hi Amjad

When I apply that setting my filtering works but the sorting doesn't?

For each new worksheet I add I'm setting the autofilter using,

ws.AutoFilter.SetRange(0, 0, lastColumnIndex -1);

and the then before I save the file I'm setting the protection on each worksheet using

wb.Worksheets[ws.Index].IsGridlinesVisible = false;
wb.Worksheets[ws.Index].Protection.IsSelectingLockedCellsAllowed = false;
wb.Worksheets[ws.Index].Protection.IsSortingAllowed = true;
wb.Worksheets[ws.Index].Protection.IsFilteringAllowed = true;
wb.Worksheets[ws.Index].Protection.IsEditingContentsAllowed = false;
wb.Worksheets[ws.Index].Protection.Password = "1234";

The error I get when trying to sort is 'The cell or chart you are trying to change is protected and therefore read only.' but I'm not locking down any cells

I have some hidden columns in by autofilter range, but these doesn't cause a problem when the worksheet is not protected, could that have an impact?

Kind regards
Andrew

#4

Hi Andrew,

If you could perform the steps manually in MS Excel mentioned in your sample code, you can find that MS Excel does not allow this. Aspose.Cells for .NET follows the MS Excel standards, so, the task is not possible.

Thank you.