Worksheet Protection
Worksheet protection is designed to restrict the user from modifying the content and structure of the worksheet. When a worksheet is protected the user can edit the contents only of the cells that were explicitly marked as unlocked. Additionally, the model offers protection options that let you choose a set of commands that will be available to the user when protection is enabled.
You can enforce worksheet protection with or without a password and you can always unprotect the workbook as needed. You can also let the user remove the protection by entering the correct password.
The protection functionality protects the worksheet content from being edited. If you need to protect the document so that it can be opened only after providing a password for it, you should encrypt it. The encryption functionality is currently not supported in SpreadProcessing and you can vote for its implementation using the public request for it.
How to Protect and Unprotect a Worksheet
The Worksheet class exposes a Protect() method that takes two parameters: the password string and the worksheet protection options.
Example 1 sets the IsLocked property of cell A1 to false and protects the worksheet using a password and the default protection options. Since by default all cells are locked, after the sheet is protected, the user will be able to edit the value only in cell A1 as it is explicitly marked as unlocked.
Example 1: Protect a Worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetIsLocked(false);
worksheet.Protect("telerik", WorksheetProtectionOptions.Default);
Use the Unprotect(string) method of the Worksheet class to remove the protection. The method returns a Boolean value that indicates whether the sheet is successfully unprotected.
Example 2 demonstrates how to unprotect a worksheet.
Example 2: Unprotect a Worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Protect("telerik", WorksheetProtectionOptions.Default);
worksheet.Unprotect("telerik");
Protection Options
The worksheet protection feature lets specify a set of options that will be available to the user when protection is enforced. For example, you may want to allow the user to insert and delete rows, but restrict the insertion and deletion of columns.
To achieve that, you need to pass a WorksheetProtectionOptions instance as the second argument of the Protect() method.
Example 3 demonstrates how to protect a worksheet using WorksheetProtectionOptions.
Example 3: Protect Worksheet with WorksheetProtectionOptions
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
WorksheetProtectionOptions options = new WorksheetProtectionOptions(allowInsertRows: true, allowDeleteRows: true);
worksheet.Protect("telerik", options);
The WorksheetProtectionOptions class exposes the following properties so you can control what actions the users will be able to perform:
- AllowInsertColumns: Gets a value indicating whether the user can insert columns when the worksheet is protected.
- AllowInsertRows: Gets a value indicating whether the user can insert rows when the worksheet is protected.
- AllowDeleteColumns: Gets a value indicating whether the user can delete columns when the worksheet is protected.
- AllowDeleteRows: Gets a value indicating whether the user can delete rows when the worksheet is protected.
- AllowFormatCells: Gets a value indicating whether the user can format cells when the worksheet is protected.
- AllowFormatColumns: Gets a value indicating whether the user can format columns when the worksheet is protected.
- AllowFormatRows: Gets a value indicating whether the user can format rows when the worksheet is protected.
- AllowFiltering: Gets a value indicating whether the user can use autofilter when the worksheet is protected.
- AllowSorting: Gets a value indicating whether the user can sort when the worksheet is protected.