Write Sheet

Operation Name

Write Sheet

Function overview

Write input data to an Excel file.

Data model

Data model of this component is Table Model type.
However, if [Specify format/comment in Mapper to write] is checked, it is XML type.

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 on the script canvas.
Input data	Required	Not available	Select a component on the script campus.

Required settings

Item name	Required/Optional	Use of variables	Description	Remarks
File path	Required	Available	Enter the Excel file path. You can click [Browse] button to launch file chooser and find the file.	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 name	Required	Available	Select or enter the sheet name.	Case-insensitive. You can get sheet names from Property action [Get sheet names].
Column list	Required	-	Specify the column. Each row can be operated with the following buttons. [Up]: Move the order of the selected row upwards by one. [Down]: Move the order of the selected row downwards by one. [Add]: Add the row. [Delete]: Delete the row.	It can be set automatically from property action [Get column list].
Column list/Write target	Required	Not available	Select whether to write in this column.
Column list/Columns	Required	Available	Specify by entering the column to be written.	Column can be specified as the A1 format or R1C1 format. To specify the column in A1 format Ex: "A" "B" To specify the column in R1C1 format Ex: "1” "2"
Column list/Heading	Optional	Not available	Enter the heading of the column.	Values set in [Heading] will be displayed in Mapper schema. When [Write heading in the first row] is checked, the set value is written.
Column list/Type	Required	Not available	Select a type of [Columns]. [String]:(default) Internal data type is String. [Numeric value]: Internal data type is BigDecimal. [Date]: Internal data type is Date. [Time]: Internal data type is Date. [Boolean]: Internal data type is boolean.	For more details on internal data type, refer to "Internal Data Types". When you select [Time], the value of date is not used.
Column list/Number settings	Required	Not available	Select the setting method of the number format. [Specify Number]: Specifies the number format of cell. [Use Number of cell]:(default) Uses the number format of write target cell. [Use Number of start row]: Uses the number format of start row of writing.	When [Use Number of cell] or [Use Number of start row] is selected, you cannot use the number format set in [Column list/Number].
Column list/Number	Optional	Available	Select or enter the number format set to the write target cell.
Specify format/comment in Mapper to write	Optional	Not available	Select whether or not to set elements for writing format/comment in input schema. [Checked]: Elements to write format/comment are set in input schema. [Not checked]: (default) Elements to write format/comment are not set in input schema.	When [Checked], the input schema will be in XML type. Refer to "Input Schema" for more details.

Property actions

Item name	Description	Remarks
Get sheet name list	Get sheet names from the file specified in [File path].	Invalid when the specified file does not exist or script variables are set in [File path].
Get the column list	Gets the column information from [Sheet name] of the file specified in [File path] and sets to [Column list].	Invalid when the specified file does not exist or script variables are set to [File path]/[Sheet name]. Gets the column information from first cell and sets the value of cell as [Heading]. When row number is set to [Start condition], gets the column information from the cell of that row. When the target cell is blank, that column will not be set. When a file with large size is specified in [File path], OutOfMemoryError may occur.

Start condition settings

Item name	Required/Optional	Use of variables	Description	Remarks
Enable start condition settings	Optional	Not available	Select whether or not to enable start condition settings. [Checked]: Enables start condition settings. [Not checked]:(default) Disables start condition settings.
Start condition	Optional	-	Specifies the start condition for writing. Each row can be operated with the following buttons. [Up]: Move the order of the selected row upwards by one. [Down]: Move the order of the selected row downwards by one. [Add]: Add the row. [Delete]: Delete the row.	Enabled when [Enable start condition settings] is checked. When start condition is satisfied, data writing is started. When start condition is not satisfied, data is not written.
Start condition/Column(Heading)	Required	Not available	Select or enter the [Columns] in which write start condition is to be checked.	[Columns] set in [Column list] of [Required settings] is displayed. When [Heading] is set, <column>(<heading>) is displayed. In [Columns], columns that are not set in [Column list] can also be set.
Start condition/Row number	Required	Available	Enter the row number in which start condition is to be checked. When [(Do not specify row number)] is selected, the write start condition is checked for all rows.	Targeting the entered row number, compares the cell value with [Condition value].
Start condition/Condition value	Required	Available	Enter the condition value of write start condition. When [(Do not specify condition value)] is selected, the condition is satisfied regardless of the cell value. When [(Empty cell)] is selected, the condition is satisfied if the cell is blank. When [(Not an empty cell)] is selected, the condition is satisfied if the cell is not blank.	When the type of the cell is numeric value, date, or time, the condition value is not judged.
Rule for multiple conditions	Required	Not available	Select the rule for determination of condition when multiple [Start condition] are set. [Set as start row if any of the conditions is satisfied]: (Default) Set the row as the start row if either of the multiple conditions is satisfied. [Judge in any order, set as start row if all conditions are met]: Set the row as the start row if all start conditions configured are satisfied. [Judge from top, set as start row if all conditions are met]: Set the row that satisfied all conditions as the start row. From the start conditions set, if the higher-level condition is satisfied, its lower-level condition becomes the target of judgment.	The detail when you select [Judge in any order, set as start row if all conditions are met] Once the start condition satisfies its condition, it is not judged in the subsequent determination of condition and it is assumed that the start condition is satisfied. The detail when you select [Judge from top, set as start row if all conditions are met] Once the start condition satisfies its condition, it is not judged in the subsequent determination of condition and it is assumed that the start condition is satisfied. When the higher-level condition is not satisfied, its lower-level condition is not judged.
Number of rows to skip to start writing	Optional	Available	Specify the number of rows to skip from the starting row of writing set in [Start condition], to the row actually to start writing.	If you set 0 or do not set any number, rows are not skipped and start writing from the starting row of writing set in [Start condition]. If you set the starting row of writing set in [Start condition] is 5th row and 2 is set to [Number of rows to skip to start writing], start writing from the 7th row.

Write settings

Item name	Required/Optional	Use of variables	Description	Remarks
Write heading in the first row	Optional	Not available	Select whether to write or not the heading in the first row from where the writing is started. [Checked]: Writes the [Heading] in the first row in "General" format. [Not checked]:(default) Heading is not written.
Overwrite	Optional	Not available	Select whether or not to overwrite when data exists in the specified file. [Checked]:(default) Overwrites the specified sheet. [Not checked]: It does not overwrite. If the specified sheet exists, it becomes an error.	When there is no sheet name in the write destination, create a sheet.
Insert	Optional	Not available	Select whether or not to insert data. [Checked]: Inserts it to the row where writing started. [Not checked]:(default) Overwrites from the row where writing started.	Enabled when [Overwrite] is checked. If the start condition is set, it is inserted to the starting row.
Delete target sheet	Optional	Not available	Select whether or not to delete the specified sheet before writing data to the specified file. [Checked]: Deletes the sheet. Ignores if the sheet does not exist. [Not checked]: (default) Sheet is not deleted.	Enabled when [Overwrite] is checked.
Position where you add a sheet	Required	Not available	When you create a sheet, select the position where you add it. [Append at the beginning]: (default) Append a sheet at the beginning. [Append at the end]: Append a sheet at the end.

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

When [Specify format/comment in Mapper to write] is not checked
- It is table model type.
- Numbers of column differs depending on the settings of [Column list].
- For schema structure, please refer to "Table Model Type Schema".

When [Specify format/comment in Mapper to write] is checked

It is XML type.

The schema structure is as follows.

<?xml version="1.0" encoding="UTF-8"?>
<table>
  <row>
    <column>
      <format></format>
    </column>
    <column>
      <format></format>
    </column>
       :
  </row>
  <row>
    :
  </row>
    :
</table>

Element name	Attribute name	Description	Remarks
table	-
row	-
column	-	Shows the value to be written in the cell.	Appears for each column set in [Column list].
format	-	Shows the format/comment to be written in the cell.	In this element, input a string output from "Generate Excel Format" logic. The format/comment that is not output from “Generate Excel Format” logic will not be written. The format of the write destination cell is used for the format that is not written.

Output schema

None.

Loading Schema on Mapper

Schema is loaded automatically.

For details, please refer to "Edit schema".

Transaction

Transaction is supported.

PSP Usage

When [Specify format/comment in Mapper to write] is not checked
- PSP is supported.
When [Specify format/comment in Mapper to write] is checked
- As the input schema is XML type, it cannot be used in PSP.

For details on PSP, refer to "Parallel Stream Processing".

Available Component Variables

Component variable name	Description	Remarks
count	Stores the number of written data.	The value defaults to null.
filePath	The file path is stored.	Default value is null.
message_category	When an error occurs, the category of the message code corresponding to the occurred error is stored.	Default value is null.
message_code	When an error occurs, the code of the message code corresponding to the occurred error is stored.	Default value is null.
message_level	When an error occurs, the severity of the message code corresponding to the occurred error is stored.	Default value is null.
error_type	When an error occurs, the type of occurred error will be stored.	Default value is null. The error type will be in the following format. Example: "java.io.FileNotFoundException" The contents to be stored may vary depending on DataSpider Servista versions.
error_message	When an error occurs, the message of occurred error will be 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 trace information of occurred error will be stored.	Default value is null. The contents that will be stored may vary depending on DataSpider Servista versions and client applications.

Specification Limits

When you enter null in the column element of input schema, it is written with empty characters.
It may be written to without being determined the number format of cell.
You cannot write data to a file that is encrypted with a password.
Writing formula is not supported.
Writing to a file with digital signature added is not supported.
When writing to a file with sheet protection and book protection set, data will be written, and if a password is set, the password will be maintained.
When [Background color] is specified in "Generate Excel Format" logic, items in Fill tab other than background color will be set as default value.
When writing to a file with write password set, writing will be done and the password will be maintained.
When a file with large size is specified in [File path], OutOfMemoryError may occur.

Main exceptions

Exception Name	Causes	Solution
InputDataNotFoundException	[Input data] is not specified.	Specify [Input data] or draw data flow.
ToFormatException	A value entered in format element of input schema is invalid.	Input a value output from "Generate Excel Format" logic in Mapper, or do not input any value if not necessary.
javax.json.JsonException
InvalidPropertyConfigurationException <Property name> is not specified.	[<Property name>]is not specified.	Specify [<property name>].
FileIsDirectoryException	The path entered in [File path] is a directory.	Enter a file path in [File path].
org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException	A password to open is set in the Excel file specified in [File path].	Enter an Excel file in which a password to open is not set in [File path].
org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException	The Excel file specified in [File path] is in invalid state.	Check the following. Whether a file which is not a Excel file is specified in [File path] Whether the file is corrupted
org.apache.poi.EmptyFileException
org.apache.poi.openxml4j.exceptions.InvalidFormatException
FileIsLockedException	The file specified in [File path] is locked.	Perform unlock from explorer or CLI.
java.io.FileNotFoundException Another processing is in use.	The file specified in the [File path] is being used by another process.	Check whether this is used in other process.
SheetAlreadyExistsException The specified sheet already exists.	The sheet specified in [Sheet name] already exists.	Change the sheet name of [Sheet name], or check [Overwrite] of [Write settings].
CannotStartException Could not start the operation as the start condition does not match.	Could not start the operation as the start condition does not match.	Check the setting of [Start condition settings].
InvalidPropertyConfigurationException The column number exceeds the range.[<column number>]	The column number specified in [Column list/Columns] exceeds the maximum value.	Check values of [Column list/Columns].

Notes

Refer to "Notes of Loop by Number of Data”, for placing in the Loop by Number of Data.