Protection
- 3 minutes to read
The ASPxSpreadsheet control supports workbook protection functionality to prevent users from modifying workbooks, worksheets, and specific ranges. In a protected workbook, worksheets cannot be added, deleted or renamed by users. Also, users are prohibited from moving worksheets within the workbook. A protected worksheet does not allow a user to change cell values or modify the worksheet in other ways. Also, different sheets can have different levels of protection. For example, a range in a protected worksheet can be unlocked for specific users, but other users may be prompted for a password when they try to edit the range.
#Workbook Protection
You can protect a workbook to lock its structure or freeze/unfreeze windows. For this purpose, use the IWorkbook.Protect method. To unprotect a worksheet, use the IWorkbook.Unprotect method.
#Worksheet Protection
You can protect a worksheet to lock the cells so that end-users can only perform a specific (restricted) set of actions.
By default, all cells in the worksheet have the Protection.Locked attribute set to true
. When protection is applied to the worksheet, these cells become read-only. Cells with the Protection.Locked attribute set to false
are not protected when worksheet protection is applied, so that the end-users can edit their contents.
To apply worksheet protection, use the Worksheet.Protect method. You can provide an optional password parameter to prompt the end-user for a password to unprotect a worksheet. To unprotect a worksheet, use the Worksheet.Unprotect method.
By default (when you pass the WorksheetProtectionPermissions.Default parameter to the Protect method), a protected worksheet is locked so that the user is only able to select cells.
Note
The Select locked cells and Select unlocked cells options (the Worksheet
Limitations:
The following options of the WorksheetProtectionPermissions enumeration are not currently supported:
- WorksheetProtectionPermissions.PivotTables
- WorksheetProtectionPermissions.Objects
- WorksheetProtectionPermissions.Scenarios
The ‘Defining User-Specific Permissions for Ranges in a Protected Worksheet’ feature is not currently supported. So, the use of the following API will not be in effect:
- Worksheet.ProtectedRanges
- EditRangePermission
- ProtectedRange.CreateSecurityDescriptor
- ProtectedRange.SecurityDescriptor
- ProtectedRange.SetPassword
#Example (Worksheet Protection)
using DevExpress.Spreadsheet;
public partial class CellProtection : Page {
protected const string password = "123";
protected IWorkbook Document {
get { return ASPxSpreadsheet1.Document; }
}
protected Worksheet ActiveSheet {
get { return Document.Worksheets.ActiveWorksheet; }
}
protected void Page_Load(object sender, EventArgs e) {
if(!Page.IsPostBack) {
PrepareSpreadsheetDocument();
}
}
protected void PrepareSpreadsheetDocument() {
Document.BeginUpdate();
if(ActiveSheet.IsProtected)
ActiveSheet.Unprotect(password);
UnlockCellRange();
WorksheetProtectionPermissions selectedPermissions = WorksheetProtectionPermissions.Default;
//Allows end-users to insert hyperlinks
selectedPermissions |= WorksheetProtectionPermissions.InsertHyperlinks;
//Allows end-users to format cells
selectedPermissions |= WorksheetProtectionPermissions.FormatCells;
ActiveSheet.Protect(password, selectedPermissions);
Document.EndUpdate();
Document.History.Clear();
}
protected void UnlockCellRange() {
Range unlockedRange = ActiveSheet["E9:K18"];
unlockedRange.Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Thin);
unlockedRange.Protection.Locked = false;
Range titleRange = ActiveSheet["E8:K8"];
titleRange.Merge();
titleRange.Borders.SetOutsideBorders(Color.Black, BorderLineStyle.Thin);
titleRange.SetValue("Not protected cells below");
titleRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
titleRange.FillColor = Color.Tomato;
}
}