officefileapi-405735-spreadsheet-document-api-document-security-document-protection.md
The Spreadsheet Document API supports three protection levels:
You can combine these protection types for enhanced security.
The Spreadsheet Document API can open and save password encrypted files in binary (.xls, .xlsb) and OpenXml (.xlsx) formats.
Run Demo: Spreadsheet Document Encryption
Specify the WorkbookImportOptions.Password property to decrypt a loaded file. The table below lists a chain of events raised if the Password property is not specified or returns an invalid password.
| Event | Description |
|---|---|
| Workbook.EncryptedFilePasswordRequest | Occurs if the Password property is not specified or returns an invalid password. Use the EncryptedFilePasswordRequestEventArgs.Password property to specify a new password. |
| Workbook.EncryptedFilePasswordCheckFailed | Occurs if the EncryptedFilePasswordRequestEventArgs.Password is set to an empty or invalid password. Handle this event to obtain the error that caused this event (EncryptedFilePasswordCheckFailedEventArgs.Error) and determine whether to prompt a user for a password (EncryptedFilePasswordCheckFailedEventArgs.TryAgain). |
| Workbook.InvalidFormatException | Occurs when the EncryptedFilePasswordCheckFailedEventArgs.TryAgain property is false. |
The Workbook.EncryptedFileIntegrityCheckFailed event occurs if the document fails code verification.
The following code snippet handles the Workbook.EncryptedFilePasswordRequest and Workbook.EncryptedFilePasswordCheckFailed events to prompt users to enter a password. If the user cancels the operation, the Spreadsheet Document API shows an exception message and cancels the file loading operation.
View Example: Load and Save Password-Encrypted Files
static bool IsValid { get; set; }
static void Main(string[] args) {
Workbook workbook = new Workbook();
workbook.EncryptedFilePasswordRequest += Workbook_EncryptedFilePasswordRequest;
workbook.EncryptedFilePasswordCheckFailed += Workbook_EncryptedFilePasswordCheckFailed;
workbook.InvalidFormatException += Workbook_InvalidFormatException;
workbook.LoadDocument("Documents\\encrypted.xlsx");
}
private static void Workbook_EncryptedFilePasswordRequest(object sender, EncryptedFilePasswordRequestEventArgs e) {
//Prompt the user to enter the password
Console.WriteLine("Enter password:");
e.Password = Console.ReadLine();
e.Handled = true;
IsValid = true;
}
private static void Workbook_EncryptedFilePasswordCheckFailed(object sender, EncryptedFilePasswordCheckFailedEventArgs e) {
//Analyze the password error:
switch (e.Error) {
//If the password is empty, raise the request again
case SpreadsheetDecryptionError.PasswordRequired:
Console.WriteLine("You did not enter the password!");
e.TryAgain = true;
e.Handled = true;
break;
//If the password is invalid, ask user whether to continue the operation:
case SpreadsheetDecryptionError.WrongPassword:
Console.WriteLine("The password is incorrect. Try Again? (y/n)");
string answer = Console.ReadLine()?.ToLower();
if (answer == "y") {
e.TryAgain = true;
e.Handled = true;
}
//If user cancels the operation, show an exception message:
else {
IsValid = false;
}
break;
}
Program.IsValid = false;
}
private static void Workbook_InvalidFormatException(object sender, SpreadsheetInvalidFormatExceptionEventArgs e) {
Console.WriteLine(e.Exception.Message.ToString() + " Press any key to close...");
Console.ReadKey(true);
}
Private Shared Property IsValid As Boolean
Shared Sub Main(ByVal args() As String)
Dim workbook As New Workbook()
AddHandler workbook.EncryptedFilePasswordRequest, AddressOf Workbook_EncryptedFilePasswordRequest
AddHandler workbook.EncryptedFilePasswordCheckFailed, AddressOf Workbook_EncryptedFilePasswordCheckFailed
AddHandler workbook.InvalidFormatException, AddressOf Workbook_InvalidFormatException
workbook.LoadDocument("Documents\encrypted.xlsx")
End Sub
Private Shared Sub Workbook_EncryptedFilePasswordRequest(ByVal sender As Object, ByVal e As EncryptedFilePasswordRequestEventArgs)
'Prompt the user to enter the password
Console.WriteLine("Enter password:")
e.Password = Console.ReadLine()
e.Handled = True
IsValid = True
End Sub
Private Shared Sub Workbook_EncryptedFilePasswordCheckFailed(ByVal sender As Object, ByVal e As EncryptedFilePasswordCheckFailedEventArgs)
'Analyze the password error:
Select Case e.Error
'If the password is empty, raise the request again
Case SpreadsheetDecryptionError.PasswordRequired
Console.WriteLine("You did not enter the password!")
e.TryAgain = True
e.Handled = True
'If the password is invalid, ask user whether to continue the operation:
Case SpreadsheetDecryptionError.WrongPassword
Console.WriteLine("The password is incorrect. Try Again? (y/n)")
Dim answer As String = Console.ReadLine()?.ToLower()
If answer = "y" Then
e.TryAgain = True
e.Handled = True
'If user cancels the operation, show an exception message:
Else
IsValid = False
End If
End Select
Program.IsValid = False
End Sub
Private Shared Sub Workbook_InvalidFormatException(ByVal sender As Object, ByVal e As SpreadsheetInvalidFormatExceptionEventArgs)
Console.WriteLine(e.Exception.Message.ToString() & " Press any key to close...")
Console.ReadKey(True)
End Sub
Call the Workbook.SaveDocument method overloads to password-protect the document on save. Use the EncryptionOptions object (accessible with the DocumentSettings.Encryption property) properties to specify password and protection type.
EncryptionSettings encryptionOptions = new EncryptionSettings();
encryptionOptions.Type = EncryptionType.Strong;
encryptionOptions.Password = "12345";
workbook.SaveDocument(fileName, documentFormat, encryptionOptions);
Dim encryptionOptions As New EncryptionSettings()
encryptionOptions.Type = EncryptionType.Strong
encryptionOptions.Password = "12345"
workbook.SaveDocument(fileName, documentFormat, encryptionOptions)
The Spreadsheet Document API contains the following members that allow you to perform various protection-related actions on workbooks:
| Member | Description |
|---|---|
| Workbook.Protect |
Protects the structure and windows of a workbook.
You need a license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this method in production code.
| | Workbook.Unprotect |
Removes protection from a workbook.
You need a license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this method in production code.
| | Workbook.DocumentSettings.WriteProtection | Returns write-protection options for a workbook. |
Run Demo: Spreadsheet Document Protection
Call the Workbook.Protect method to protect an entire workbook. Users cannot modify a protected workbook structure (move, delete, add, rename, or hide existing worksheets; display hidden sheets).
Worksheet worksheet = workbook.Worksheets["ProtectionSample"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Protect workbook structure with a password (prevent users from adding or
// deleting worksheets or displaying hidden worksheets).
if (!workbook.IsProtected)
workbook.Protect("password", true, false);
// Add a note.
worksheet["B2"].Value = "Workbook structure is protected with a password. \n You cannot add, move or delete worksheets until protection is removed.";
worksheet.Visible = true;
Dim worksheet As Worksheet = workbook.Worksheets("ProtectionSample")
workbook.Worksheets.ActiveWorksheet = worksheet
' Protect workbook structure with the password (prevent users from adding or
'deleting worksheets or displaying hidden worksheets).
If Not workbook.IsProtected Then
workbook.Protect("password", True, False)
End If
' Add a note.
worksheet("B2").Value = "Workbook structure is protected with a password. " & ControlChars.Lf & " You cannot add, move or delete worksheets until protection is removed."
worksheet.Visible = True
Call the Workbook.Unprotect method to remove protection.
Worksheet worksheet = workbook.Worksheets["ProtectionSample"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Unprotect the workbook (requires the password).
if (workbook.IsProtected)
workbook.Unprotect("password");
// Add a note.
worksheet["B2"].Value = "Workbook is unprotected. Workheets can be added, moved or deleted.";
worksheet.Visible = true;
Dim worksheet As Worksheet = workbook.Worksheets("ProtectionSample")
workbook.Worksheets.ActiveWorksheet = worksheet
' Unprotect the workbook using a password.
If workbook.IsProtected Then
workbook.Unprotect("password")
End If
' Add a note.
worksheet("B2").Value = "Workbook is unprotected. Workheets can be added, moved or deleted."
worksheet.Visible = True
Set the WriteProtectionOptions.ReadOnlyRecommended property to true to prompt users to open the workbook in read-only mode.
using(var workbook = new Workbook()) {
var wpOptions = workbook.DocumentSettings.WriteProtection;
wpOptions.ReadOnlyRecommended = true;
workbook.SaveDocument("WriteProtectedDocument.xlsx");
}
Using workbook As New Workbook()
Dim wpOptions As WriteProtectionOptions = workbook.DocumentSettings.WriteProtection
wpOptions.ReadOnlyRecommended = True
workbook.SaveDocument("WriteProtectedDocument.xlsx")
End Using
When users open this workbook in Microsoft® Excel®, it prompts them to open the document as read-only.
Call the WriteProtectionOptions.SetPassword method to specify a password and prevent modifications by unauthorized users.
using(var workbook = new Workbook()) {
var wpOptions = workbook.DocumentSettings.WriteProtection;
wpOptions.SetPassword("Password");
wpOptions.UserName = "John Smith";
workbook.SaveDocument("WriteProtectedDocument.xlsx");
}
Using workbook As New Workbook()
Dim wpOptions As WriteProtectionOptions = workbook.DocumentSettings.WriteProtection
wpOptions.SetPassword("Password")
wpOptions.UserName = "John Smith"
workbook.SaveDocument("WriteProtectedDocument.xlsx")
End Using
When users open this workbook in Microsoft® Excel®, it prompts them to enter a password to modify the document.
You can call the WriteProtectionOptions.CheckPassword method to check a given password. If the password is valid, call the WriteProtectionOptions.ClearPassword method to remove write-protection from a workbook.
using (Workbook workbook = new Workbook()) {
workbook.LoadDocument("WriteProtectedDocument.xlsx");
RemoveWriteProtection(workbook, "password");
}
// ...
private void RemoveWriteProtection(Workbook workbook, string password) {
var wpOptions = workbook.DocumentSettings.WriteProtection;
if (wpOptions.IsPasswordProtected && wpOptions.CheckPassword(password))
wpOptions.ClearPassword();
else {
Console.WriteLine("The file is not write-protected or the specified password is invalid!");
Console.ReadKey();
}
}
Using workbook As New Workbook()
workbook.LoadDocument("WriteProtectedDocument.xlsx")
RemoveWriteProtection(workbook, "password")
End Using
' ...
Private Sub RemoveWriteProtection(workbook As Workbook, password As String)
Dim wpOptions As WriteProtectionOptions = workbook.DocumentSettings.WriteProtection
If wpOptions.IsPasswordProtected AndAlso wpOptions.CheckPassword(password) Then
wpOptions.ClearPassword()
Else
Console.WriteLine("The file is not write-protected or the specified password is invalid!")
Console.ReadKey()
End If
End Sub
Call the Worksheet.Protect method to protect cells whose Protection.Locked property is true. The Protection.Locked defaults to true for all cells. Users cannot edit or delete content in locked cells on a protected worksheet. To allow a user to modify specific cells, set their Protection.Locked to false.
Run Demo: Spreadsheet Document Protection
The WorksheetProtectionPermissions enumeration specifies actions that users can perform on a protected worksheet. The WorksheetProtectionPermissions.Default value allows users only to select cells.
Worksheet worksheet = workbook.Worksheets["ProtectionSample"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Protect the worksheet. Prevent end-users from changing worksheet elements.
if (!worksheet.IsProtected)
worksheet.Protect("password", WorksheetProtectionPermissions.Default);
// Add a note.
worksheet["B2"].Value = "Worksheet is protected with a password. \n You cannot edit or format cells until protection is removed." +
"\nTo remove protection, on the Review tab, in the Changes group," +
"\nclick \"Unprotect Sheet\" and enter \"password\".";
worksheet.Visible = true;
Dim worksheet As Worksheet = workbook.Worksheets("ProtectionSample")
workbook.Worksheets.ActiveWorksheet = worksheet
' Protect the worksheet. Prevent end-users from changing worksheet elements.
If Not worksheet.IsProtected Then
worksheet.Protect("password", WorksheetProtectionPermissions.Default)
End If
' Add a note.
worksheet("B2").Value = "Worksheet is protected with a password. " & ControlChars.Lf & " You cannot edit or format cells until protection is removed." & ControlChars.Lf & "To remove protection, on the Review tab, in the Changes group," & ControlChars.Lf & "click ""Unprotect Sheet"" and enter ""password""."
worksheet.Visible = True
Call the Worksheet.Unprotect method to remove worksheet protection.
Worksheet worksheet = workbook.Worksheets("ProtectionSample");
workbook.Worksheets.ActiveWorksheet = worksheet;
// Remove worksheet protection (requires the password).
if (worksheet.IsProtected) {
worksheet.Unprotect("password");
}
// Add a note.
worksheet("B2").Value = "Worksheet is unprotected. You can edit and format cells.";
worksheet.Visible = true;
Dim worksheet As Worksheet = workbook.Worksheets("ProtectionSample")
workbook.Worksheets.ActiveWorksheet = worksheet
' Remove worksheet protection using a password.
If worksheet.IsProtected Then
worksheet.Unprotect("password")
End If
' Add a note.
worksheet("B2").Value = "Worksheet is unprotected. You can edit and format cells."
worksheet.Visible = True
Follow the steps below to unlock protected worksheet ranges for authenticated users.
Add ranges that you want to unlock to the ProtectedRangeCollection collection. Use the Worksheet.ProtectedRanges property to access this collection.
Worksheet worksheet = workbook.Worksheets["ProtectionSample"];
ProtectedRange protectedRange = worksheet.ProtectedRanges.Add("My Range", worksheet["B2:J5"]);
// ...
Dim worksheet As Worksheet = workbook.Worksheets("ProtectionSample")
Dim protectedRange As ProtectedRange = worksheet.ProtectedRanges.Add("My Range", worksheet("B2:J5"))
'...
You can authenticate a user in one of the following ways.
You can authenticate a user by the account that runs the application. In this case, Windows authenticates a user based on the standard security mechanisms that rely on domain security.
Follow the steps below to unlock a range for a specific user or a user group:
Call the ProtectedRange.SetPassword method to specify a password for the range. When a user attempts to edit the range, the application asks for the password.
The following code snippet unlocks a range ( “B2:J5” ) and implements both user authentication types:
Worksheet worksheet = workbook.Worksheets["ProtectionSample"];
workbook.Worksheets.ActiveWorksheet = worksheet;
worksheet["B2:J5"].Borders.SetOutsideBorders(Color.Red, BorderLineStyle.Thin);
// Specify user permission to edit a range in a protected worksheet.
ProtectedRange protectedRange = worksheet.ProtectedRanges.Add("My Range", worksheet["B2:J5"]);
EditRangePermission permission = new EditRangePermission();
permission.UserName = Environment.UserName;
permission.DomainName = Environment.UserDomainName;
permission.Deny = false;
protectedRange.SecurityDescriptor = protectedRange.CreateSecurityDescriptor(new EditRangePermission[] { permission });
protectedRange.SetPassword("123");
// Protect a worksheet.
if (!worksheet.IsProtected)
worksheet.Protect("password", WorksheetProtectionPermissions.Default);
// Add a note.
worksheet["B2"].Value = "This cell range is protected with a password. \n You cannot edit or format it until protection is removed." +
"\nTo remove protection, double-click the range and enter \"123\".";
worksheet.Visible = true;
Dim worksheet As Worksheet = workbook.Worksheets("ProtectionSample")
workbook.Worksheets.ActiveWorksheet = worksheet
worksheet("B2:J5").Borders.SetOutsideBorders(Color.Red, BorderLineStyle.Thin)
' Specify user permission to edit a range in a protected worksheet.
Dim protectedRange As ProtectedRange = worksheet.ProtectedRanges.Add("My Range", worksheet("B2:J5"))
Dim permission As New EditRangePermission()
permission.UserName = Environment.UserName
permission.DomainName = Environment.UserDomainName
permission.Deny = False
protectedRange.SecurityDescriptor = protectedRange.CreateSecurityDescriptor(New EditRangePermission() { permission })
protectedRange.SetPassword("123")
' Protect a worksheet.
If Not worksheet.IsProtected Then
worksheet.Protect("password", WorksheetProtectionPermissions.Default)
End If
' Add a note.
worksheet("B2").Value = "This cell range is protected with a password. " & ControlChars.Lf & " You cannot edit or format it until protection is removed." & ControlChars.Lf & "To remove protection, double-click the range and enter ""123""."
worksheet.Visible = True
EncryptedFilePasswordCheckFailed to provide user-friendly error messages.