Password Protection
- 7 minutes to read
The Spreadsheet and Report Designer controls provide several password protection options for XLSX/XLTX and XLS/XLT spreadsheet documents, allowing you to:
Encrypt the entire document, preventing unauthorized loading;
Protect the document structure from accidental changes (that is, worksheet names, states, sheet tab order, etc.);
Protect specific worksheet content (including but not limited to, floating containers and hyperlinks) and/or to restrict specific end-user editing options (such as the capability to manage columns and rows, sort cell values, etc.).
Spreadsheet Document Encryption
Support for importing encrypted XLSX/XLTX and XLS/XLT files in both the Spreadsheet and Report Designer controls is available out-of-the-box: when an end-user attempts to load a password-protected file (by executing the OpenDocument command, for instance), the Password Protection dialog is invoked:
If the specified password is correct, the encrypted document is loaded by the control, replacing its current content. Otherwise, an exception is raised, displaying this error message box:
The password used to decrypt the loaded document is assigned to the Spreadsheet/Report Designer control’s Password property. However, if a spreadsheet document is encrypted using the default password, the Spreadsheet/Report Designer control loads it without prompting an end-user to enter a password.
If you need to load an encrypted XLSX/XLTX or XLS/XLT file programmatically, without displaying the Password Protection dialog, you can:
Assign the required password to the control’s Password property;
Pass the required password as the Password parameter in the control’s OnGetPassword event handler:
function TMySpreadSheetForm.dxSpreadSheet1GetPassword(Sender: TObject; var Password: string): Boolean;
begin
Password := '123'; // Specifies the password required to decrypt a document file
Result := True; // Confirms the encrypted document loading operation
end;
If the Password property value differs from an empty string, the displayed document is automatically encrypted when exported to the XLSX/XLTX or XLS/XLT file formats. Call the ShowPasswordDialog global function that invokes the Password Protection dialog to allow end-users to assign a spreadsheet encryption password:
Spreadsheet Document Structure Protection
Both the Spreadsheet and Report Designer controls allow you to protect the structure of the loaded spreadsheet document from accidental changes by disabling all worksheet management operations. To enable the document structure protection without a password, you can either set the control’s OptionsProtection.Protected property to True or click the “OK” button on the Protect Structure dialog that can be invoked using one of the following techniques:
Call the control’s Protect procedure;
Call the global ShowProtectWorkbookDialog procedure;
Execute the ProtectWorkbook command.
End-users can specify the optional protection password in the Protect Structure dialog to enforce the spreadsheet document structure password protection:
Unlike the document file encryption case, the Spreadsheet and Report Designer controls do not provide a dedicated property that you can use to specify a protection password directly. Instead, they use the concept of password protection provider objects storing the hashed protection password and concomitant settings that can be saved to XLSX/XLTX (OpenXML) files. Depending on the required protection level and the target file format, you can use one of the following protection providers:
| Protection Level | Protection Provider Class | Supported in |
|---|---|---|
| Standard | TdxSpreadSheetStandardProtectionInfo | XLSX and XLS files |
| Strong | TdxSpreadSheetStrongProtectionInfo | XLSX files only |
Set the document structure protection password by assigning a new instance of the supported protection provider to the control’s OptionsProtection.ProtectionInfo property and passing the required password as the APassword parameter of the provider’s constructor:
var
AProtectionInfo: IdxSpreadSheetProtectionInfo;
// Specify the end-user permission
dxSpreadSheet1.OptionsProtection.AllowChangeStructure := False;
// Assign a password (if required)
AProtectionInfo := TdxSpreadSheetStandardProtectionInfo.Create('Password'); // Creates a standard protection provider with the required password
dxSpreadSheet1.OptionsProtection.ProtectionInfo := AProtectionInfo;
dxSpreadSheet1.OptionsProtection.&Protected := True; // Enables the document structure's protection
To remove the spreadsheet document structure protection, you can use one of the following techniques:
Invoke the control’s Unprotect procedure;
Execute the ProtectWorkbook command;
Set the control’s OptionsProtection.Protected property to False.
Calling the control’s Unprotect procedure or executing the ProtectWorkbook command disables protection if the loaded document is not protected with a password. The Enter Password dialog is invoked if the protection password was assigned:
If the specified password is correct, the document structure protection is disabled. Otherwise, an exception is raised:
You need to set the control’s OptionsProtect.ProtectionInfo property to nil to disengage the password protection of the spreadsheet document structure programmatically:
dxSpreadSheet1.OptionsProtection.ProtectionInfo := nil; // Destroys the current password protection provider via the interface
dxSpreadSheet1.OptionsProtection.Protected := False; // Disables the document structure's protection
Worksheet Protection
You can restrict certain end-user actions (including changes to the cell content and formatting) in a worksheet by enabling its protection. End-users can only select any cell and customize floating containers (including comments) in a protected worksheet by default. However, you can customize the end-user permission set by using:
- The Protect Sheet dialog;
- The Table View worksheet’s OptionsProtection property set.
An end-user’s attempt to perform one of the currently restricted operations in the worksheet raises the EdxSpreadSheetProtectionError exception, displaying the following error message box:
The Protect Sheet dialog can be invoked by:
Calling the worksheet’s Protect procedure;
Calling the global ShowProtectSheetDialog procedure;
Executing the ProtectSheet command;
Clicking the “Protect Sheet…” item in the caption bar’s context menu invoked at the corresponding sheet tab:
Set the worksheet protection password by assigning a new supported protection provider instance to the Table View worksheet’s OptionsProtection.ProtectionInfo property and passing the required password as the APassword parameter of the provider’s constructor, similar to the document structure protection case:
var
ATableView: TdxSpreadSheetTableView;
AProtectionInfo: IdxSpreadSheetProtectionInfo;
//...
ATableView := dxSpreadSheet1.ActiveSheetAsTable;
// Specify all required end-user permissions
ATableView.OptionsProtection.AllowEditHyperlinks := True;
ATableView.OptionsProtection.AllowResizeColumns := True;
ATableView.OptionsProtection.AllowResizeRows := True;
// Assign a password (if required)
AProtectionInfo := TdxSpreadSheetStandardProtectionInfo.Create('Password'); // Creates a standard protection provider with the required password
ATableView.OptionsProtection.ProtectionInfo := AProtectionInfo;
// Enables the worksheet protection
ATableView.OptionsProtection.&Protected := True; // Enables the worksheet content protection
You can use one of the following techniques to disable the worksheet’s protection:
Invoke the Table View worksheet’s Unprotect procedure;
Execute the ProtectSheet command;
Set the worksheet’s OptionsProtection.Protected property to False.
Calling a protected worksheet’s Unprotect procedure or executing the ProtectSheet command disables protection if it has no protection password assigned. The Enter Password dialog is invoked if a password exists, identical to the document structure protection case. Disabling the password protection programmatically also looks similar:
var
ATableView: TdxSpreadSheetTableView;
//...
ATableView := dxSpreadSheet1.ActiveSheetAsTable;
ATableView.OptionsProtection.ProtectionInfo := nil; // Destroysthe current password protection provider via the interface
ATableView.OptionsProtection.Protected := False; // Disables the worksheet protection