Show/Hide Sheets/Rows/Columns

Operation Name

Show/Hide Sheets/Rows/Columns

Function Overview

Show/Hide sheets, rows, and columns in an Excel workbook.

Properties

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.

Sheet settings

Item name	Required/Optional	Use of variables	Description	Remarks
Sheet list	Optional	-	Specify sheets to be shown/hidden.
Sheet list/Sheet name	Required	Available	Select or enter a sheet to be shown/hidden of the Excel file specified in [File].	Case-insensitive.
Sheet list/Visible/Hidden	Required	Not available	Select visible or hidden for the sheet specified in [Sheet]. [Visible]: (default) [Hidden] [VeryHidden]	A sheet set with [VeryHidden] will not be displayed in the Excel unhide dialog box.

Row settings

Item name	Required/Optional	Use of variables	Description	Remarks
Row list	Optional	-	Specify rows to be shown/hidden.
Row list/Sheet name	Required	Available	Select or enter a sheet which includes rows to be shown/hidden.	Case-insensitive.
Row list/Start row	Required	Available	Enter the starting row number to be shown/hidden.	Allowed values are 1-1048576. Enter a value not greater than the row number specified in [Row list/End row].
Row list/End row	Required	Available	Enter the ending row number to be shown/hidden.	Allowed values are 1-1048576. Enter a value not less than the row number specified in [Row list/Start row].
Row list/Visible/Hidden	Required	Not available	Select visible or hidden for rows from the starting row specified in [Row list/Start row] to the ending row specified in [Row list/End row]. [Visible]: (default) [Hidden]

Column settings

Item name	Required/Optional	Use of variables	Description	Remarks
Column list	Optional	-	Specify columns to be shown/hidden.
Column list/Sheet name	Required	Available	Select or enter a sheet which includes columns to be shown/hidden.	Case-insensitive.
Column list/Start column	Required	Available	Enter the starting column to be shown/hidden with alphabets of A1 reference style. Example: A	Allowed values are A-XFD. Enter a value equal to or former than the one specified in [Column list/End column].
Column list/End column	Required	Available	Enter the ending column to be shown/hidden with alphabets of A1 reference style. Example: XFD	Allowed values are A-XFD. Enter a value equal to or latter than the alphabets specified in [Column list/Start column].
Column list/Visible/Hidden	Required	Not available	Select visible or hidden for columns from the starting column specified in [Column list/Start column] to the ending column specified in [Column list/End column]. [Visible]: (default) [Hidden]

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.

In settings for rows, when multiple settings are performed for the same sheet, the settings will be sorted in the ascending order of [Row list/Start row]. When the value of [Row list/Start row] is same, it will be sorted in the ascending order of [Row list/End row].

In settings for columns, when multiple settings are performed for the same sheet, the settings will be sorted in the ascending order of [Column list/Start column]. When the value of [Column list/Start column] is same, it will be sorted in the ascending order of [Column list/End column].

When a column for which the column width is not set is shown or hidden, the column width will be different from the default value.

You cannot show/hide 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 Start row (End row) must be 1-1048576.	The value of [Row list/Start row] or [Row list/End row] is invalid.	Check the value of [Row list/Start row] or [Row list/End row].
InvalidPropertyConfigurationException End row must be not less than the start row.	A value less than the value specified in [Row list/Start row] is specified in [Row list/End row].	Check the value of [Row list/Start row] or [Row list/End row].
InvalidPropertyConfigurationException Start column (End column) must be A-XFD.	The value of [Column list/Start column] or [Column list/End column] is invalid.	Check the value of [Column list/Start column] or [Column list/End column].
InvalidPropertyConfigurationException End column must be not former than the start column.	A value former than the value specified in [Column list/Start column] is specified in [Column list/End column].	Check the value of [Column list/Start column] or [Column list/End column].
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.lang.IllegalStateException No visible sheet found.	No visible sheet is found.	Check whether one or more visible sheet exists in the file specified in [File].
java.io.FileNotFoundException	The file specified in [File] does not exist.	Check [File].
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.