Password Protect Workbooks or Sheets

Operation Name

Function Overview

Add protection to the book or sheet with modifying password specified, given an Excel book format file.

Properties

For information about using 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 path of the Excel workbook file. If [View] is checked, file chooser starts, and file selection will be available.	Specify file path of Excel book format with extensions ".xlsx" and ".xlsm". Specify file path as absolute path of DataSpider file system. Constrained Characters of DataSpider File System except for path separator "/" can not be used.

Workbook protection settings

Item name	Required/Optional	Use of variables	Description	Remarks
Set password to modify	Optional	Not available	Specify password to modify and choose whether the workbook will be protected. [Checked]: Specifies password to modify and protects workbook. [Not checked]:(default) Does not protect workbook that specified password to modify.
User name	Required	Available	Enter user name which protects workbook.	Enabled if [Set password to modify] is checked.
Password to modify	Required	Available	Enter password to modify which protects workbook.	Enabled if [Set password to modify] is checked.

Sheet protection settings

Item name	Required/Optional	Use of variables	Description	Remarks
Protect the content of the sheet and locked cells	Optional	Not available	Select whether to protect the content of the sheet and locked cells. [Checked]: protects sheet and the locked content of cell. [Not checked]:(default) Does not protect sheet and the locked content of cell.
Sheet name	Required	Not available	Enter or select protection target sheet in the Excel file specified in [File].	Enabled if [Protect the content of the sheet and locked cells] is checked.
Password to unprotect sheet	Optional	Available	Enter password for unlocking sheet protection.	Enabled if [Protect the content of the sheet and locked cells] is checked. If omitted, the content will be protected without password.
Operations all users of this worksheet are allowed to perform	Optional	-	Select operation which allows all sheet users specified in [Sheet name].	Enabled if [Protect the content of the sheet and locked cells] is checked.
Operations all users of this worksheet are allowed to perform/Permission	Optional	-	Select operations to permit from [Operation] in the protected sheet. [Checked]: Allows operation. [Not checked]:(default) Does not allow operation.
Operations all users of this worksheet are allowed to perform/Operation	-	-	Operation to select whether to permit or not will be shown in the protected sheet. [Select locked cells] [Select unlocked cells] [Format cells] [Format columns] [Format rows] [Insert columns] [Insert rows] [Insert hyperlinks] [Delete columns] [Delete rows] [Sort] [User 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 to perform transaction processing. [Checked]: Perform transaction processing. [Not checked]:(default) Do not perform transaction processing. Refer "File Type adapter transaction" for more information.

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

Transactionis supported.

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

PSP Usage

Not supported.

Available Component Variables

Component variable name	Description	Remarks
message_category	Stores the category of message code corresponding to the occurred error.	The value defaults to null.
message_code	Stores the code of message code corresponding to the occurred error.	The value defaults to null.
message_level	Stores the severity of message code corresponding to the occurred error.	The value defaults to null.
error_type	Stores the error type of occurred error.	The value defaults to null. Error is represented in the format depicted below. Example:java.io.FileNotFoundException The value may vary depending on the DataSpider Servista version.
error_message	Stores the error message of occurred error.	The value defaults to null. The value may vary depending on the DataSpider Servista version.
error_trace	Stores the trace information of occurred error.	The value defaults to null. The value may vary depending on the DataSpider Servista version or the client application used.

Specification Limits

This operation only supports Excel 2010/2007 workbooks.

If [Set password to modify] and [Protect the content of the sheet and locked cells] are not checked, no operation will be performed on the Excel book format file specified in [File].

If [Select range of locked cell] is checked in [Operations all users of this worksheet are allowed to perform], it is also required to check [Select range of unlocked cell].

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

If the sheet specified in [Sheet name] is protected, it will be overwritten with the value specified in [Password to unprotect sheet] and [Operations all users of this worksheet are allowed to perform].

The characters permitted in [Password to modify] and [Password to unprotect sheet] are as follows.
- Half width alphabet
- Half width digit
- "!" "@" "#" "$" "%" "^" "&" "*" "(" ")" "+" "="
You cannnot protect an Excel workbook which is encrypted with a password.

Main Exceptions

Exception name	Cause	Solution
InvalidPropertyConfigurationException File path is not specified.	[File] is not specified.	Specify [File].
InvalidPropertyConfigurationException The specified book is not supported.	Book which format does not correspond with [File] is specified.	Specify book which format corresponds with [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	Path specified in [File] is directory.	Specify a file path in [File].
FileIsLockedException	The file specified in [File] is locked.	Unlock from explorer or CLI.
java.io.FileNotFoundException The file is being used by another process.	The file specified in [File] is being used in another process.	Confirm whether it is being used or not.
SheetNotFoundException A non-existent sheet name is specified.	The sheet specified in [Sheet name] does not exist.	Specify the name of a existing sheet in [Sheet name].

Notes

If placing this operation in a loop operation, refer to Notes for Loop (by Number of data).