Protect Workbook and Sheet

Operation Name

Function Overview

Protect workbook and sheets of an Excel workbook with password to modify.

Properties

For details on use of variables, refer to “Variables”.

Basic settings

Item name	Required/Optional	Use of variables	Description	Remarks
Name	Required	Not available	Enter the name to show on the script canvas.

Required settings

Item name	Required/Optional	Use of variables	Description	Remarks
File	Required	Available	Enter the file path of Excel workbook. You can click [Browse] button to launch file chooser and find the file.	Specify a file path of Excel workbook with “.xlsx” or “.xlsm” extension. Specify the file path as an absolute path of DataSpider File System. Except for the path separator “/”, restricted characters of DataSpider File System cannot be used.

Workbook protection settings

Item name	Required/Optional	Use of variables	Description	Remarks
Set password to modify	Optional	Not available	Select whether or not to protect the workbook with password to modify. [Checked]: Protect the workbook with password to modify. [Not checked]: (default) Do not protect the workbook with password to modify.
User name	Required	Available	Enter the user name who protects the workbook.	Enabled when [Set password to modify] is checked.
Password to modify	Required	Available	Enter the password to modify that is used to protect the workbook.	Enabled when [Set password to modify] is checked.

Sheet protection settings

Item name	Required/Optional	Use of variables	Description	Remarks
Protect contents of worksheet and locked cells	Optional	Not available	Select whether or not to protect the contents of the worksheet and locked cells. [Checked]: Protect the contents of the worksheet and locked cells. [Not checked]: (default) Do not protect the contents of the worksheet and locked cells.
Sheet name	Required	Available	Select or enter a sheet to be protected in the Excel file specified in [File].	Enabled when [Protect contents of worksheet and locked cells] is checked.
Password to unprotect sheet	Optional	Available	Enter the password to unprotect the sheet.	Enabled when [Protect contents of worksheet and locked cells] is checked. When omitted, the sheet will be protected without password.
Operations to allow all users of worksheet	Optional	-	Select operations to allow all users of the worksheet specified in [Sheet name].	Enabled when [Protect contents of worksheet and locked cells] is checked.
Operations to allow all users of worksheet/Allow	Optional	-	Select operation to allow in the protected sheet from the list of [Operation]. [Checked]: The operation is allowed. [Not checked]: (default) The operation is not allowed.
Operation to allow all users of worksheet/Operation	-	-	Operations which whether or not to allow in the protected sheet can be selected are displayed. [Select locked cells] [Select unlocked cells] [Format cells] [Format columns] [Format rows] [Insert columns] [Insert rows] [Insert hyperlinks] [Delete columns] [Delete rows] [Sort] [Use AutoFilter] [Use PivotTable reports] [Edit objects] [Edit scenarios]

Transaction

Item name	Required/Optional	Use of variables	Description	Remarks
Execute transaction processing	Optional	Not available	Select whether or not to execute transaction processing. [Checked]: Transaction processing will be executed. [Not checked]: (default) Transaction processing will not be executed. Refer to “Transaction of file category adapters” for more details.

Comment

Item name	Required/Optional	Use of variables	Description	Remarks
Comment	Optional	Not available	You can write a short description of this adapter. The description will be reflected in the specifications.

Schema

Input schema

None.

Output schema

None.

Transaction

Transaction is supported.

Transaction is enabled only when [Execute transaction processing] of [Transaction] is checked.

PSP Usage

PSP is not supported.

Available Component Variables

Component variable name	Description	Remarks
message_category	When an error occurs, the corresponding message code's category is stored.	Default value is null.
message_code	When an error occurs, the corresponding message code is stored.	Default value is null.
message_level	When an error occurs, the corresponding message code's severity is stored.	Default value is null.
error_type	When an error occurs, the error type is stored.	Default value is null. The error type will be in the following format. Example: “java.io.FileNotFoundException” The contents that will be stored may vary depending on DataSpider Servista versions.
error_message	When an error occurs, the error message is stored.	Default value is null. The contents that will be stored may vary depending on DataSpider Servista versions.
error_trace	When an error occurs, the error's trace information is stored.	Default value is null. The contents that will be stored may vary depending on DataSpider Servista versions and client applications.

Specification Limits

Only Excel workbook is supported.

When [Set password to modify] and [Protect contents of worksheet and locked cells] are not checked, the Excel file specified in [File] will not be processed.

When [Select locked cells] is checked in [Operation to allow all users of worksheet], [Select unlocked cells] also need to be checked.

When password to modify is set to the Excel file specified in [File], it will be overwritten with the values specified in [User name] and [Password to modify].

When the sheet specified in [Sheet name] is protected, it will be overwritten with the values specified in[Password to unprotect sheet] and [Operation to allow all users of worksheet].

The characters allowed in [Password to modify] and [Password to unprotect sheet] are the following.
- Alphabets
- Numbers
- “!” “@” “#” “$” “%” “^” “&” “*” “(“ “)” “+” “=”
You cannot protect an Excel book encrypted with password.

Major Exceptions

Exception name	Cause	Solution
InvalidPropertyConfigurationException File path is not specified.	[File] is not specified.	Specify [File].
InvalidPropertyConfigurationException A workbook of unsupported format is specified.	A workbook of unsupported format is specified in [File].	Specify a workbook with supported format in [File].
InvalidPropertyConfigurationException User name is not specified.	[User name] is not specified.	Specify [User name].
InvalidPropertyConfigurationException Password to modify is not specified.	[Password to modify] is not specified.	Specify [Password to modify].
InvalidPropertyConfigurationException Sheet name is not specified.	[Sheet name] is not specified.	Specify [Sheet name].
FileIsDirectoryException	The path entered in [File] is directory.	Enter a file path in [File].
FileIsLockedException	The file specified in [File] is being locked.	Unlock it from Explorer or CLI.
java.io.FileNotFoundException Some other process is using the file.	The file specified in [File] is being used by some other process.	Make sure that it is not being used by some other process.
SheetNotFoundException The sheet name that does not exist is specified.	The sheet specified in [Sheet name] does not exist.	Specify an existing sheet name in [Sheet name].

Notes

When placing inside loop processing, refer to Notes of Loop by Number of Data.