Show/Hide Sheets/Rows/Columns

Operation Name

Show/Hide Sheets/Rows/Columns

Function Overview

Show or hide sheets, rows and columns of Excel workbooks.

Properties

Basic settings

Item name	Required / Optional	Use of Variables	Description	Remarks
Name	Required	Not available	Enter the name 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. Click [Browse] button to activate the file chooser and Select the file.	Files with extension ".xlsx" and ".xlsm" are treated as an Excel workbook. Specify the absolute path of the DataSpider file system as the file path. Constrained characters of DataSpider File System except for path separator "/" cannot be used.

Sheet settigs

Item name	Required / Optional	Use of Variables	Description	Remarks
Sheet list	Optional	-	Specify the Show/Hide targeted sheet.
Sheet list/Sheet name	Required	Optional	Select or enter a target sheet to be shown or hidden contained in the Excel file specified in [File].	Does not distinguish upper/lower cases.
Sheet list/Visible/Hidden	Required	Not available	Select Visible or Hidden to a sheet specified in [Sheet]. [Visible]:(default) [Hidden] [VeryHidden]	Sheets set in [VeryHidden] is not shown on an Excel re-show dialog box.

Row settings

Item name	Required / Optional	Use of Variables	Description	Remarks
Row list	Optional	-	Specify target rows to be shown or hidden.
Row list/Sheet name	Required	Optional	Select or enter a sheet which contains a target row to be shown or hidden.	Does not distinguish upper/lower cases.
Row list/Starting row	Required	Optional	Enter a starting row number to be shown or hidden.	Available number is "1-1048576". Enter a value less than the row number specified in [Row list/Ending row].
Row list/Ending row	Required	Optional	Enter a ending row number to be shown or hidden.	Input available number is "1-1048576". Enter a value less than the row number specified in [Row list/Starting row].
Row list/Visible/Hidden	Required	Not available	Select Visible or Hidden to rows within [Row list/Starting row] and [Row list/Ending row]. [Visible]:(default) [Hidden]

Column settings

Item name	Required / Optional	Use of Variables	Description	Remarks
Column list	Optional	-	Specify target columns to be shown or hidden.
Column list/Sheet name	Required	Optional	Select or enter a sheet which contains a target column to be shown or hidden.	Does not distinguish upper/lower cases.
Column list/Starting column	Required	Optional	Enter a starting columns number to be shown or hidden by A1 reference format alphabets. Example:A	Available value is "A-XFD". Enter a value of former alphabets specified in [Column list/Ending column].
Column list/Ending column	Required	Optional	Enter a ending columns number to be shown or hidden by A1 reference format alphabets. Example:XFD	Available input value is "A-XFD". Enter a value of latter alphabets specified in [Column list/Starting column].
Column list/Visible/Hidden	Required	Not available	Select Visible or Hidden to columns within [Column list/Starting column] and [Column list/Ending column]. [Visible]:(default) [Hidden]

Transaction

Item name	Required / Optional	Use of Variables	Description	Remarks
Perform transaction	Optional	Not available	Select whether to perform transaction or not. [Checked]: Perform transaction. [Not checked]:(default) Not perform transaction For details, refer to "File type adapter transaction".

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 enables only if [Perform Transaction] in [Transaction] is checked.

PSP Usage

PSP is not supported.

Available Component variables

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

Specification Limits

Corresponds only to Excel workbooks.

If you set several settings to the same sheet in Row settings, the settings are sorted by ascending order of [Row list/Starting row]. If the values of [Row list/Starting row] is the same, then of [Row list/Ending row].

If you set several settings to the same sheet in Column settings, the settings are sorted by ascending order of [Column list/Starting column]. If the values of [Column list/Starting column] is the same, then of [Column list/Ending column].

If you show or hide a column without column width, some width different from the default is set.

You cannot show or hide them in an Excel workbook which is encrypted with a password.

Main exceptions

Exception name	Causes	Solution
InvalidPropertyConfigurationException File path is not specified.	[File] is not specified.	Specify [File].
InvalidPropertyConfigurationException Workbook with unsupported format is specified.	Workbook with unsupported format is specified to [File].	Specify the workbook format that corresponds to [File].
InvalidPropertyConfigurationException Starting row (Ending row) must be greater than or equal to 1 and less than or equal to 1048576.	Value of [Row list/Starting row] or [Row list/Ending row] is invalid.	Check the values of [Row list/Starting row] or [Row list/Ending row].
InvalidPropertyConfigurationException Ending row must be greater or equal to starting row.	A value less than the value specified in [Row list/Starting row] is specified to [Row list/Ending row].	Check the values of [Row list/Starting row] or [Row list/Ending row].
InvalidPropertyConfigurationException Starting column (Ending column) must be greater than or equal to A and less than or equal to XFD.	Values in [Column list/Starting column] or [Column list/Ending column] is invalid.	Check the values of [Column list/Starting column] or [Column list/Ending column].
InvalidPropertyConfigurationException Ending column must be greater or equal to starting column.	A value less than the value specified in [Column list/Starting column] is specified to [Column list/Ending column].	Check the value of [Column list/Starting column] or [Column list/Ending column].
InvalidPropertyConfigurationException Sheet name is not specified.	[Sheet name] is not specified.	Specify [Sheet name].
FileIsDirectoryException	Path input to [File] is directory.	Input file path to [File].
FileIsLockedException	File specified in [File] is locked.	Perform unlocking from explorer or CLI.
java.lang.IllegalStateException No visible sheet found.	Show available sheet is not found.	Check is show available file exists more than one, in the file specified in [File].
java.io.FileNotFoundException	File specified in [File] does not exist.	Check [File].
java.io.FileNotFoundException Different process is in use.	File specified in [File] is used by other process.	Check whether or not used in other process.
SheetNotFoundException Non-existing Sheet name is specified.	Sheet specified in [Sheet name] does not exist.	Specify sheet name existing in [Sheet name].

Notes

Refer to "Notes of Loop by Number of Data" for the case of placed in loop.