Write Specified Cell

Operation Name

Function Overview

Write input data to a specified cell of 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 that appears on the script canvas.
Input data	Required	Not available	Select a component on the script canvas.

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.
Cell list	Required	-	Specify cell. 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.
Cell list/Sheet name	Required	Available	Select or enter the sheet name.	Case-insensitive. When there is no sheet name in the write destination, create a sheet. You can get sheet names from Property action [Get sheet names].
Cell list/Cell address	Required	Available	Specify by entering the cell address to be written.	Cell address can be specified as the A1 format or R1C1 format. To specify the column in A1 format Ex: "A1" "B2" To specify the column in R1C1 format Ex: "R1C1" "R5C12"
Cell list/Cell label	Optional	Not available	Enter the label of the cell.	Values set in [Cell Label] will be displayed in Mapper schema. It is not used during execution.
Cell list/Type	Required	Not available	Select the type of the cell. [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.
Cell 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.	When [Use Number of cell] is selected, you cannot use the number format set in [Column list/Number].
Cell 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].

Write setting

Item name	Required/Optional	Use of variables	Description	Remarks
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.
- The number of columns differs depending on the [Cell list] settings.
- Refer to "Table model type schema" for schema structure.

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>
</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 [Cell 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 will be loaded automatically.

For details, please refer to "Edit schema".

Transaction

Transaction is supported.

PSP Usage

PSP is not supported.

Available Component variables

Component variable name	Description	Remarks
filePath	The file path is stored.	The 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 errors will be stored.	Default value is null. The contents that will be stored may vary depending on DataSpider Servista versions and client applications.

Specifications Limits

When you enter null in the column element of input schema, it is written with empty characters.
When input data has more than 2 rows, the Input data in the first row will be written.
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.
InvalidPropertyConfigurationException The specified cell address is invalid.	The cell address entered in [Cell list/Cell address] is invalid.	Check [Cell list/Cell address] and enter a valid cell address.
InvalidPropertyConfigurationException The cell address exceeds the range.[<cell address>]	The row number specified in [Cell list/Cell address] exceeds the maximum value.	Check values of [Cell list/Cell address].

Notes

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