Protect Excel Documents
- 11 minutes to read
The Spreadsheet Document API supports three protection levels:
- File Encryption - Requires password to open the file.
- Workbook Protection - Prevents structural changes (add/delete/rename sheets).
- Worksheet Protection - Prevents cell content modifications.
You can combine these protection types for enhanced security.
File Encryption
The Spreadsheet Document API can open and save password encrypted files in binary (.xls, .xlsb) and OpenXml (.xlsx) formats.
Open an Encrypted File
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.
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);
}
Encrypt a Workbook with a Password
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);
Workbook Protection
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. |
Protect a Workbook
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;
Unprotect a Workbook
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;
Recommend Workbook Read-Only Mode
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");
}
When users open this workbook in Microsoft® Excel®, it prompts them to open the document as read-only.
Specify a Workbook Modification Password
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");
}
When users open this workbook in Microsoft® Excel®, it prompts them to enter a password to modify the document.
Clear the Password
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();
}
}
Worksheet Protection
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.
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;
Unprotect a Worksheet
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;
Unlock Specific Worksheet Ranges
Follow the steps below to unlock protected worksheet ranges for authenticated users.
1. Define Ranges
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"]);
// ...
2. Authenticate a User
You can authenticate a user in one of the following ways.
2.1. Authenticate a User Account
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:
- Create an EditRangePermission object for each user or group.
- Call the ProtectedRange.CreateSecurityDescriptor method to transform a list of permissions to a security descriptor.
- Use the ProtectedRange.SecurityDescriptor property to assign the created security descriptor to the range.
2.2. Password Authentication
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.
Example
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;
Best Practices
- Combine protection levels: Use encryption, workbook protection, and worksheet protection for maximum security.
- Use protected ranges for collaboration: Allow specific users to edit specific areas.
- Store passwords securely: Never hardcode passwords in production code.
- Handle events gracefully: Always handle
EncryptedFilePasswordCheckFailedto provide user-friendly error messages. - Test permissions: Verify that protected ranges work correctly with different user accounts.