Protecting Worksheets

When a worksheet is protected, user's actions are restricted. For example, you cannot input data. Moreover, you can't insert or delete rows or columns etc. There are only 3 protection options in MS Excel 97 and Excel 2000:

Contents

Objects

Scenarios

Protected worksheet doesn'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 users from modifying the data, content and formatting in the worksheet.

Protect a Worksheet

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

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

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

ProtectionType enumeration contains the following pre-defined protections 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

Example:

An example is given below that is used to protect a worksheet with a password.

[C#]

//Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream("C:\\book1.xls", FileMode.Open);

//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook excel = new Workbook(fstream);

//Accessing the first worksheet in the Excel file
Worksheet worksheet = excel.Worksheets[0];

//Protecting the worksheet with a password
worksheet.Protect(ProtectionType.All, "salman", null);

//Saving the modified Excel file in default format
excel.Save("C:\\output.xls", SaveFormat.Excel97To2003);

//Closing the file stream to free all resources
fstream.Close();

[VB.NET]

'Creating a file stream containing the Excel file to be opened
Dim fstream As FileStream = New FileStream("C:\\book1.xls", FileMode.Open)

'Instantiating a Workbook object
'Opening the Excel file through the file stream
Dim excel As Workbook = New Workbook(fstream)

'Accessing the first worksheet in the Excel file
Dim worksheet As Worksheet = excel.Worksheets(0)

'Protecting the worksheet with a password
worksheet.Protect(ProtectionType.All, "salman", DBNull.Value.ToString())

'Saving the modified Excel file in default format
excel.Save("C:\\output.xls", SaveFormat.Excel97To2003)

'Closing the file stream to free all resources
fstream.Close()

After the above code is used to protect the worksheet, you can check the protection on the worksheet by opening it. Once you open the file and try to add some data to the worksheet, you will see the following dialog:

Figure: A Message Dialog to notify that user can't modify the worksheet

To work on the worksheet, you would need to unprotect the worksheet by selecting the Tools | Protection | Unprotect Sheet menu item as shown below:

Figure: Selecting Unprotect Sheet menu item

After you select Unprotect Sheet menu item, a dialog will open that would prompt you to enter the password so that you may work on the worksheet as shown below:

Figure: Entering password to unprotect the worksheet

Protect a few Cells in the Worksheet

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

Following are the two approaches to implement the task.

Method1

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

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, click 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.

On the Tools menu, point to 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.

Method2

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

Example :The following example exhibits how to protect a few cells in the worksheet. It unlocks all the cells in the worksheet first and then locks 3 cells (A1, B1, C1) in it. Finally, it protects the worksheet. The Style object contains a boolean property, IsLocked . You can set IsLocked property to true or false and apply Column/Row.ApplyStyle() method to lock or unlock the row / column with your desired attributes.

[C#]

// Create a new workbook.
Workbook wb = new Workbook();

// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];

// Define the style object.
Style style;

//Define the styleflag object
StyleFlag styleflag;

// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{

    style = sheet.Cells.Columns[(byte)i].Style;
    style.IsLocked = false;
    styleflag = new StyleFlag();
    styleflag.Locked = true;
    sheet.Cells.Columns[(byte)i].ApplyStyle(style, styleflag);

}

// Lock the three cells...i.e. A1, B1, C1.
style = sheet.Cells["A1"].GetStyle();
style.IsLocked = true;
sheet.Cells["A1"].SetStyle(style);
style = sheet.Cells["B1"].GetStyle();
style.IsLocked = true;
sheet.Cells["B1"].SetStyle(style);
style = sheet.Cells["C1"].GetStyle();
style.IsLocked = true;
sheet.Cells["C1"].SetStyle(style);

// Finally, Protect the sheet now.
sheet.Protect(ProtectionType.All);

// Save the excel file.
wb.Save("d:\\test\\lockedcells.xls", SaveFormat.Excel97To2003);

[VB.NET]

' Create a new workbook.
Dim wb As New Workbook()

' Create a worksheet object and obtain the first sheet.
Dim sheet As Worksheet = wb.Worksheets(0)

' Define the style object.
Dim style As Style

'Define the styleflag object
Dim styleflag As StyleFlag

' Loop through all the columns in the worksheet and unlock them.
For i As Integer = 0 To 255

    style = sheet.Cells.Columns(CByte(i)).Style
    style.IsLocked = False
    styleflag = New StyleFlag()
    styleflag.Locked = True
    sheet.Cells.Columns(CByte(i)).ApplyStyle(style, styleflag)

Next i

' Lock the three cells...i.e. A1, B1, C1.
style = sheet.Cells("A1").GetStyle()
style.IsLocked = True
sheet.Cells("A1").SetStyle(style)
style = sheet.Cells("B1").GetStyle()
style.IsLocked = True
sheet.Cells("B1").SetStyle(style)
style = sheet.Cells("C1").GetStyle()
style.IsLocked = True
sheet.Cells("C1").SetStyle(style)

' Finally, Protect the sheet now.
sheet.Protect(ProtectionType.All)

' Save the excel file.
wb.Save("d:\test\lockedcells.xls", SaveFormat.Excel97To2003)

Protect a Row in the Worksheet

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

Example:

The following example shows how to protect a row in the worksheet. It unlocks all the cells in the worksheet first and then locks the first row in it. Finally, it protects the worksheet. The Style object contains a boolean property, IsLocked . You can set IsLocked property to true or false to lock or unlock the row / column using the StyleFlag object.

[C#]

// Create a new workbook.
Workbook wb = new Workbook();

// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];

// Define the style object.
Style style;

// Define the styleflag object.
StyleFlag flag;

// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
    style = sheet.Cells.Columns[(byte)i].Style;
    style.IsLocked = false;
    flag = new StyleFlag();
    flag.Locked = true;
    sheet.Cells.Columns[(byte)i].ApplyStyle(style, flag);

}

// Get the first row style.
style = sheet.Cells.Rows[0].Style;

// Lock it.
style.IsLocked = true;

// Instantiate the flag.
flag = new StyleFlag();

// Set the lock setting.
flag.Locked = true;

// Apply the style to the first row.
sheet.Cells.ApplyRowStyle(0, style, flag);

// Protect the sheet.
sheet.Protect(ProtectionType.All);

// Save the excel file.
wb.Save("d:\\test\\lockedrow.xls", SaveFormat.Excel97To2003);

[VB.NET]

' Create a new workbook.
Dim wb As New Workbook()

' Create a worksheet object and obtain the first sheet.
Dim sheet As Worksheet = wb.Worksheets(0)

' Define the style object.
Dim style As Style

' Define the styleflag object.
Dim flag As StyleFlag

' Loop through all the columns in the worksheet and unlock them
For i As Integer = 0 To 255
    style = sheet.Cells.Columns(CByte(i)).Style
    style.IsLocked = False
    flag = New StyleFlag()
    flag.Locked = True
    sheet.Cells.Columns(CByte(i)).ApplyStyle(style, flag)

Next i

' Get the first row style.
style = sheet.Cells.Rows(0).Style

' Lock it.
style.IsLocked = True

' Instantiate the flag.
flag = New StyleFlag()

' Set the lock setting.
flag.Locked = True

' Apply the style to the first row.
sheet.Cells.ApplyRowStyle(0, style, flag)

' Protect the sheet.
sheet.Protect(ProtectionType.All)

' Save the excel file.
wb.Save("d:\test\lockedrow.xls", SaveFormat.Excel97To2003)

Protect a Column in the Worksheet

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

Example:The following example shows how to protect a column in the worksheet. It unlocks all the cells in the worksheet first and then locks the first column in it. Finally, it protects the worksheet. The Style object contains a boolean property, IsLocked . You can set IsLocked property to true or false to lock or unlock the row / column using the StyleFlag object.

[C#]

// Create a new workbook.
Workbook wb = new Workbook();

// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];

// Define the style object.
Style style;

// Define the styleflag object.
StyleFlag flag;

// Loop through all the columns in the worksheet and unlock them.
for (int i = 0; i <= 255; i++)
{
    style = sheet.Cells.Columns[(byte)i].Style;
    style.IsLocked = false;
    flag = new StyleFlag();
    flag.Locked = true;
    sheet.Cells.Columns[(byte)i].ApplyStyle(style, flag);

}

// Get the first column style.
style = sheet.Cells.Columns[0].Style;

// Lock it.
style.IsLocked = true;

// Instantiate the flag.
flag = new StyleFlag();

// Set the lock setting.
flag.Locked = true;

// Apply the style to the first column.
sheet.Cells.Columns[0].ApplyStyle(style, flag);

// Protect the sheet.
sheet.Protect(ProtectionType.All);

// Save the excel file.
wb.Save("d:\\test\\lockedcolumn.xls", SaveFormat.Excel97To2003);

[VB.NET]

' Create a new workbook.
Dim wb As New Workbook()

' Create a worksheet object and obtain the first sheet.
Dim sheet As Worksheet = wb.Worksheets(0)

' Define the style object.
Dim style As Style
' Define the styleflag object.

Dim flag As StyleFlag

' Loop through all the columns in the worksheet and unlock them.
For i As Integer = 0 To 255
    style = sheet.Cells.Columns(CByte(i)).Style
    style.IsLocked = False
    flag = New StyleFlag()
    flag.Locked = True
    sheet.Cells.Columns(CByte(i)).ApplyStyle(style, flag)

Next i

' Get the first column style.
style = sheet.Cells.Columns(0).Style

' Lock it.
style.IsLocked = True

' Instantiate the flag.
flag = New StyleFlag()

' Set the lock setting.
flag.Locked = True

' Apply the style to the first column.
sheet.Cells.Columns(0).ApplyStyle(style, flag)

' Protect the sheet.
sheet.Protect(ProtectionType.All)

' Save the excel file.
wb.Save("d:\test\lockedcolumn.xls", SaveFormat.Excel97To2003)

Allow Users to Edit Ranges

Example:The following example shows how to allow users to edit a range in a protected worksheet.

[C#]

//Instantiate a new Workbook
Workbook book = new Workbook();

//Get the first (default) worksheet
Worksheet sheet = book.Worksheets[0];
//Get the Allow Edit Ranges
ProtectedRangeCollection allowRanges = sheet.AllowEditRanges;
//Define ProtectedRange
ProtectedRange proteced_range;

//Create the range
int idx = allowRanges.Add("r2", 1, 1, 3, 3);
proteced_range = allowRanges[idx];
//Specify the passoword
proteced_range.Password = "123";

//Protect the sheet
sheet.Protect(ProtectionType.All);

//Save the Excel file
book.Save("e:\\test2\\protectedrange.xls");

[VB.NET]

'Instantiate a new Workbook
Dim book As New Workbook()

'Get the first (default) worksheet
Dim sheet As Worksheet = book.Worksheets(0)
'Get the Allow Edit Ranges
Dim allowRanges As ProtectedRangeCollection = sheet.AllowEditRanges
'Define ProtectedRange
Dim proteced_range As ProtectedRange

'Create the range
Dim idx As Integer = allowRanges.Add("r2", 1, 1, 3, 3)
proteced_range = allowRanges(idx)
'Specify the passoword
proteced_range.Password = "123"

'Protect the sheet
sheet.Protect(ProtectionType.All)

'Save the Excel file
book.Save("e:\test2\protectedrange.xls")

Here is the screen_shot of the generated Excel file after executing the above code:

Figure: You need to enter password to unprotect the range

Protecting Worksheets

Protect a Worksheet

Protect a few Cells in the Worksheet

Method1

Method2

Protect a Row in the Worksheet

Protect a Column in the Worksheet

Allow Users to Edit Ranges

Labels