Introduction

In our previous topic, we have discussed about how to protect a worksheet in Excel 97 and 2000 versions. But with the release of Excel 2002 or XP, many advanced protection settings are added to worksheets by Microsoft. These protection 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 of these advacned protection settings offered by Excel XP.

Advanced Protection Settings Using Excel XP

To view the various kinds of protection settings in Excel XP, please choose Tools | Protection | Protect Sheet menu item in Microsoft Excel XP. When you click Protect Sheet menu item, a dialog will be displayed as shown below:

 

 

Figure: Dialog to show protection options in Excel XP

 

Using the above dialog, you can allow or restrict various features on the worksheets and can also apply a password.

Advanced Protection Settings Using Aspose.Cells

All of these advanced protection settings in Excel XP are supported in Aspose.Cells. 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 Protection property that is used to apply these advanced protection settings of Excel XP on the worksheets. Protection property is in fact an object of Protection class that encapsulates several boolean properties to disable or enable the restrictions on the worksheets.

Example:

[C#]

 

 

//Allowing users to select locked cells of the worksheet

worksheet.Protection.IsSelectingLockedCellsAllowed = true;

 

 

//Allowing users to select unlocked cells of the worksheet

worksheet.Protection.IsSelectingUnlockedCellsAllowed = true;

 

 

[VB.NET]

 

 

'Allowing users to select locked cells of the worksheet

worksheet.Protection.IsSelectingLockedCellsAllowed = True

 

 

'Allowing users to select unlocked cells of the worksheet

worksheet.Protection.IsSelectingUnlockedCellsAllowed = True

 

 

[JAVA]

 

 

//Allowing users to select locked cells of the worksheet

protection.setSelectingLockedCellsAllowed(true);

 

 

//Allowing users to select unlocked cells of the worksheet

protection.setSelectingUnlockedCellsAllowed(true);

 

Example:

A small example application is created below that opens an Excel file and uses most of the advanced protection settings of Excel XP.

[C#]

 

 

//Instantiating a Workbook object

Workbook excel=new Workbook();

 

 

//Creating a file stream containing the Excel file to be opened

FileStream fstream=new FileStream("C:\\book1.xls",FileMode.Open);

 

 

//Opening the Excel file through the file stream

excel.Open(fstream);

 

 

//Accessing the first worksheet in the Excel file

Worksheet worksheet=excel.Worksheets[0];

 

 

//Restricting users to delete columns of the worksheet

worksheet.Protection.IsDeletingColumnsAllowed = false;

 

 

//Restricting users to delete row of the worksheet

worksheet.Protection.IsDeletingRowsAllowed = false;

 

 

//Allowing users to edit contents of the worksheet

worksheet.Protection.IsEditingContentsAllowed = true;

 

 

//Allowing users to edit objects of the worksheet

worksheet.Protection.IsEditingObjectsAllowed = true;

 

 

//Allowing users to edit scenarios of the worksheet

worksheet.Protection.IsEditingScenariosAllowed = true;

 

 

//Restricting users to filter

worksheet.Protection.IsFilteringAllowed = false;

 

 

//Allowing users to format cells of the worksheet

worksheet.Protection.IsFormattingCellsAllowed = true;

 

 

//Allowing users to format rows of the worksheet

worksheet.Protection.IsFormattingRowsAllowed = true;

 

 

//Allowing users to insert columns in the worksheet

worksheet.Protection.IsInsertingColumnsAllowed = true;

 

 

//Allowing users to insert hyperlinks in the worksheet

worksheet.Protection.IsInsertingHyperlinksAllowed = true;

 

 

//Allowing users to insert rows in the worksheet

worksheet.Protection.IsInsertingRowsAllowed = true;

 

 

//Allowing users to select locked cells of the worksheet

worksheet.Protection.IsSelectingLockedCellsAllowed = true;

 

 

//Allowing users to select unlocked cells of the worksheet

worksheet.Protection.IsSelectingUnlockedCellsAllowed = true;

 

 

//Allowing users to sort

worksheet.Protection.IsSortingAllowed = true;

 

 

//Allowing users to use pivot tables in the worksheet

worksheet.Protection.IsUsingPivotTablesAllowed = true;

 

 

//Saving the modified Excel file Excel XP format

excel.Save("C:\\output.xls",FileFormatType.ExcelXP);

 

 

//Closing the file stream to free all resources

fstream.Close();

 

 

[VB.NET]

 

 

'Instantiating a Workbook object

Dim excel As Workbook = New Workbook()

 

 

'Creating a file stream containing the Excel file to be opened

Dim fstream As FileStream = New FileStream("C:\\book1.xls",FileMode.Open)

 

 

'Opening the Excel file through the file stream

excel.Open(fstream)

 

 

'Accessing the first worksheet in the Excel file

Dim worksheet As Worksheet = excel.Worksheets(0)

 

 

'Restricting users to delete columns of the worksheet

worksheet.Protection.IsDeletingColumnsAllowed = False

 

 

'Restricting users to delete row of the worksheet

worksheet.Protection.IsDeletingRowsAllowed = False

 

 

'Allowing users to edit contents of the worksheet

worksheet.Protection.IsEditingContentsAllowed = True

 

 

'Allowing users to edit objects of the worksheet

worksheet.Protection.IsEditingObjectsAllowed = True

 

 

'Allowing users to edit scenarios of the worksheet

worksheet.Protection.IsEditingScenariosAllowed = True

 

 

'Restricting users to filter

worksheet.Protection.IsFilteringAllowed = False

 

 

'Allowing users to format cells of the worksheet

worksheet.Protection.IsFormattingCellsAllowed = True

 

 

'Allowing users to format rows of the worksheet

worksheet.Protection.IsFormattingRowsAllowed = True

 

 

'Allowing users to insert columns in the worksheet

worksheet.Protection.IsInsertingColumnsAllowed = True

 

 

'Allowing users to insert hyperlinks in the worksheet

worksheet.Protection.IsInsertingHyperlinksAllowed = True

 

 

'Allowing users to insert rows in the worksheet

worksheet.Protection.IsInsertingRowsAllowed = True

 

 

'Allowing users to select locked cells of the worksheet

worksheet.Protection.IsSelectingLockedCellsAllowed = True

 

 

'Allowing users to select unlocked cells of the worksheet

worksheet.Protection.IsSelectingUnlockedCellsAllowed = True

 

 

'Allowing users to sort

worksheet.Protection.IsSortingAllowed = True

 

 

'Allowing users to use pivot tables in the worksheet

worksheet.Protection.IsUsingPivotTablesAllowed = True

 

 

'Saving the modified Excel file Excel XP format

excel.Save("C:\\output.xls",FileFormatType.ExcelXP)

 

 

'Closing the file stream to free all resources

fstream.Close()

 

 

[JAVA]

 

 

//Instantiating a Workbook object

Workbook excel = new Workbook();

 

 

//Accessing the first worksheet in the Excel file

excel.open("F:\\book1.xls");

Worksheets worksheets = excel.getWorksheets();

Worksheet worksheet = worksheets.getSheet(0);

 

 

Protection protection = new Protection();

worksheet.protect(protection);

 

 

//Restricting users to delete columns of the worksheet

protection.setDeletingColumnsAllowed(false);

 

 

//Restricting users to delete row of the worksheet

protection.setDeletingRowsAllowed (false);

 

 

//Allowing users to edit contents of the worksheet

protection.setEditingContentsAllowed (true);

 

 

//Allowing users to edit objects of the worksheet

protection.setEditingObjectsAllowed (true);

 

 

//Allowing users to edit scenarios of the worksheet

protection.setEditingScenariosAllowed (true);

 

 

//Restricting users to filter

protection.setFilteringAllowed (false);

 

 

//Allowing users to format cells of the worksheet

protection.setFormattingCellsAllowed (true);

 

 

//Allowing users to format rows of the worksheet

protection.setFormattingRowsAllowed (true);

 

 

//Allowing users to insert columns in the worksheet

protection.setInsertingColumnsAllowed (true);

 

 

//Allowing users to insert hyperlinks in the worksheet

protection.setInsertingHyperlinksAllowed (true);

 

 

//Allowing users to insert rows in the worksheet

protection.setInsertingRowsAllowed(true);

 

 

//Allowing users to select locked cells of the worksheet

protection.setSelectingLockedCellsAllowed(true);

 

 

//Allowing users to select unlocked cells of the worksheet

protection.setSelectingUnlockedCellsAllowed(true);

 

 

//Allowing users to sort

protection.setSortingAllowed(true);

 

 

//Allowing users to use pivot tables in the worksheet

protection.setUsingPivotTablesAllowed (true);

 

 

//Saving the modified Excel file Excel XP format

excel.save("C:\\output.xls",FileFormatType.EXCELXP);

 

Note: Please don't call Protect method of the Worksheet class when you use Protection property. Moreover, please save the file as ExcelXP or Excel2003 format because these advanced protection settings are only supported since Excel XP.

Cells Locking Issue

If you want to restrict users from the editing of cells in the worksheets then the cells must be locked first before applying any protection settings otherwise the cells can be edited even the worksheet is protected. Cells can be locked using Microsoft Excel XP through the following dialog:

 

 

Figure: Dialog to lock cells in Excel XP

 

Developers can lock cells using Aspose.Cells API too. Each cell has a Style property that further contains a boolean property, IsLocked . You can set IsLocked property to true or false to lock or unlock the cell.

Example: