How to Use Worksheet Protection Properties In ExcelWriter

In ExcelWriter 8.6.1, the ability to set specific worksheet protection properties was added with the new Sheet Protection Object. Worksheet Protection Properties allow the user to add permissions to do certain actions on protected worksheets.

The Excel Protection Properties

Protection Properties Limitations

Microsoft Excel has a limitation on four of the protection properties: AllowSort, AllowDeleteColumns, AllowDeleteRows, and AllowInsertHyperlinks. These properties require that affected cells be editable when the worksheet is locked. This is because these properties entirely remove cells or alter the content of cells that are protected. Therefore, in order to use the properties you must unlock cells, put cells in an editable range, or use a macro.

Using the Protection Properties in Excel Writer

Steps to Use Protection Properties in ExcelWriter: 

1. Create a new sheet protection object
2. Set the worksheet protection properties as desired. By default, Select Locked cells and Select Unlocked Cells are set to true
3. Call Protect() on the worksheet

            //Create a sheet protection object for the first sheet of the workbook
            SheetProtection protection = excelWb[0].SheetProtection;

            //Set the properties how you want them
            protection.AllowFormatCells= true;

            //Call worksheet.Protect() to protect the worksheet, set the password,   and apply your properties settings
            excelWb[0].Protect("password");

Enabling Sorting (or other limited protection properties) with ExcelWriter

If you want to use AllowSort, AllowDeleteColumns, AllowDeleteRows, and AllowInsertHyperlinks, you can by making a range with those affected cells editable. For more information about implementing this workaround in Excel, see this article. Here are the steps to implement it with ExcelWriter:

In your template:

1. Select the cells you would like to use one of the limited protection properties on
2. Go to Review-> Allow Users to Edit Ranges
3. Click New and then OK
4. To exit the dialogue, click Apply and OK

In your code:

1. Set the worksheet protection property AllowSelectLockedCell to false
2. Set one of the limited protection properties to true: AllowSort, AllowDeleteColumns, AllowDeleteRows, or AllowInsertHyperlinks
3. Activate worksheet protection with the worksheet.protect() method

            //Create a new workbook
            ExcelApplication excelApp = new ExcelApplication();
            Workbook excelWb = excelApp.Open(Page.MapPath("SomeTemplate.xlsx"));

            //Create a sheet protection object for the first sheet of the workbook
            SheetProtection protection = excelWb[0].SheetProtection;

            //Set AllowSelectLockedCells to false.             
            protection.AllowSelectLockedCells = false;

            //Set the four limited properties to true
            protection.AllowInsertHyperlinks = true;
            protection.AllowDeleteColumns = true;
            protection.AllowDeleteRows = true;
            protection.AllowSort = true;

            //Call worksheet.Protect() to protect the worksheet, set the password, and apply your properties settings
            excelWb[0].Protect("password");

Follow

Related posts: