Protect and Unprotect Worksheet

Protect Worksheets

When a worksheet is protected, the actions a user can take are restricted. For example, they cannot input data, insert or delete rows or columns, etc. The general protection options in Microsoft Excel are:

• Contents
• Objects
• Scenarios

Protected worksheets don’t hide or protect sensitive data, so it’s different from file encryption. Generally, worksheet protection is suitable for presentation purposes. It prevents the end user from modifying data, content and formatting in the worksheet.

Adding or Removing Protection

Aspose.Cells provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains a WorksheetCollection that allows access to each worksheet in an Excel file. A worksheet is represented by the Worksheet class.

The Worksheet class provides the Protect method that is used to apply protection to a worksheet. The Protect method accepts the following parameters:

• Protection Type, the type of protection to apply on the worksheet. The protection type is applied with the help of the ProtectionType enumeration.
• New Password, the new password used to protect the worksheet.
• Old Password, the old password, if the worksheet is already password protected. If the worksheet is not already protected, then just pass null.

The ProtectionType enumeration contains the following pre‑defined protection types:

Protection Types	Description
ALL	User cannot modify anything on this worksheet
CONTENTS	User cannot enter data in this worksheet
OBJECTS	User cannot modify drawing objects
SCENARIOS	User cannot modify saved scenarios
STRUCTURE	User cannot modify saved structure
WINDOWS	User cannot modify saved windows
NONE	No protection

The example below shows how to protect a worksheet with a password.

After using the above code to protect the worksheet, check the protection by opening it. Once you open the file and try to add some data to the worksheet, the following dialog is displayed:

A dialog warning that a user can’t modify the worksheet

To work on the worksheet, unprotect the worksheet by selecting Protection, then Unprotect Sheet from the Tools menu, as shown below.

Selecting Unprotect Sheet menu item

A dialog opens prompting for a password.

Entering password to unprotect the worksheet

Protecting a Few Cells

There might be certain scenarios where you need to lock only a few cells in the worksheet. If you want to lock some specific cells in the worksheet, you have to unlock all the other cells. All the cells in a worksheet are already initialized for locking. You may check this by opening any Excel file in MS Excel and clicking Format | Cells…, then selecting the Protection tab where the Locked check box is checked by default.

Following are the two approaches to implement the task.

Method 1:

The following points describe how to lock a few cells using MS Excel. This method applies to Microsoft Office Excel 97, 2000, 2002, 2003 and later versions.

1. Select the entire worksheet by clicking the Select All button (the gray rectangle directly above the row number for row 1 and to the left of column letter A).

2. Click Cells on the Format menu, select the Protection tab, and then clear the Locked check box.
   This unlocks all the cells on the worksheet.

   If the Cells command is not available, parts of the worksheet may already be locked. On the Tools menu, point to Protection, and then click Unprotect Sheet.

3. Select just the cells you want to lock and repeat step 2, but this time select the Locked check box.

4. On the Tools menu, select Protection, click Protect Sheet, and then click OK.

   In the Protect Sheet dialog box, you have the option to specify a password and select the elements that you want users to be able to change.

Method 2:

In this method we use only the Aspose.Cells API to perform the task.

The following example exhibits how to protect a few cells in the worksheet. It unlocks all the cells first and then locks three cells (A1, B1, C1). Finally, it protects the worksheet. A row/column has a Style API that further contains a setLocked method. You can use this method to lock or unlock the row/column.

Protect a Row in the Worksheet

Aspose.Cells allows you to easily lock any row in the worksheet. Here we can make use of the applyStyle() method of the Row class to apply a style to a particular row. This method takes two arguments: a Style object and a StyleFlag struct that contains all the members related to applied formatting.

The following example shows how to protect a row in the worksheet. It unlocks all the cells first and then locks the first row. Finally, it protects the worksheet. A row/column has a Style API that further contains a setCellLocked method. You can lock or unlock the row/column using the StyleFlag struct.

Protect a Column in the Worksheet

Aspose.Cells allows you to easily lock any column in the worksheet. Here we can make use of the applyStyle() method of the Column class to apply a style to a particular column. This method takes two arguments: a Style object and a StyleFlag struct that contains all the members related to applied formatting.

The following example shows how to protect a column in the worksheet. It unlocks all the cells first and then locks the first column. Finally, it protects the worksheet. A row/column has a Style API that further contains a setLocked method. You can lock or unlock the row/column using the StyleFlag struct.

Unprotect a Worksheet

Protecting Worksheets and Advanced Protection Settings since Excel XP discussed different approaches of protecting worksheets. What if a developer needs to remove protection from a protected worksheet at runtime so that some changes can be made to the file? This can easily be done with Aspose.Cells.

Using Microsoft Excel

To remove protection from a worksheet:

From the Tools menu, select Protection, then Unprotect Sheet.

Selecting Unprotect Sheet

Protection is removed, unless the worksheet is password protected. In this case, a dialog prompts for the password.

Entering password to unprotect the worksheet

Using Aspose.Cells

A worksheet can be unprotected by calling the Worksheet class’s Unprotect method. The Unprotect method can be used in two ways, described below.

Unprotecting a Simply Protected Worksheet

A simply protected worksheet is one that is not password protected. Such worksheets can be unprotected by calling the unprotect method without passing a parameter.

Unprotecting a Password‑Protected Worksheet

A password‑protected worksheet is one that is protected with a password. Such worksheets can be unprotected by calling an overloaded version of the Unprotect method that takes the password as a parameter.

Advanced Protection Settings since Excel XP

Protecting Worksheets discussed protecting a worksheet in Microsoft Excel 97 and 2000. Since the release of Excel 2002 (XP), Microsoft has added many advanced protection settings. These settings restrict or allow users to:

• Delete rows or columns.
• Edit contents, objects or scenarios.
• Format cells, rows or columns.
• Insert rows, columns or hyperlinks.
• Select locked or unlocked cells.
• Use pivot tables and much more.

Aspose.Cells supports all the advanced protection settings offered by Excel XP and later versions.

Advanced Protection Settings Using Excel XP and Later Versions

To view the protection settings available in Excel XP:

1. From the Tools menu, select Protection, then Protect Sheet. A dialog is displayed.

   Dialog to show protection options in Excel XP

   

2. Allow or restrict worksheet features or apply a password.

Advanced Protection Settings Using Aspose.Cells

Aspose.Cells supports all of the advanced protection settings.

Aspose.Cells provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains a WorksheetCollection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class.

The Worksheet class provides the Protection property that is used to apply these advanced protection settings. The Protection property is, in fact, an object of the Protection class that encapsulates several Boolean properties for disabling or enabling restrictions.

Below is a small example application. It opens an Excel file and uses most of the advanced protection settings supported by Excel XP and later versions.

Save the file in EXCEL97TO2003 or XLSX format because these advanced protection settings are only supported by MS Excel XP and later versions.

Cell Locking Issue

If you want to restrict users from editing cells, the cells must be locked before any protection settings are applied. Otherwise, the cells can be edited even if the worksheet is protected. In Microsoft Excel XP, cells can be locked through the following dialog:

Dialog to lock cells in Excel XP

It is also possible to lock cells using the Aspose.Cells API. Each cell has a Style API that further contains a setLocked method. Use it to lock or unlock cells.