Official |[Excel(POI)] Write Sheet

Operation name

Write Sheet

Function overview

Writes input data to an Excel file.

Data model

The data model of this component is table model type.

If Specify format/comment in Mapper to write is selected, it's XML type.

Properties

= Remarks =

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 a name that is used 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. The Browse button launches a file chooser that allows you to select a file.	Note Specify an absolute path of HULFT Square Storage for the file path. With the exception of the path separator "/", the Restricted characters of HULFT Square Storage can't be used.
Sheet name	Required	Available	Select or enter the sheet name.	Case-insensitive. You can get sheet names using the property action Get sheet name list.
Column list	Required	-	Specify the columns. Each row can be operated with the following buttons: Up Moves the selected row upward by one row. Down Moves the selected row downward by one row. Add Adds a row. Delete Deletes the row.	It can be set automatically using the property action Get the column list.
Column list/Write target	Required	Not available	Select whether to write in this column.
Column list/Columns	Required	Available	Specify the column to be written.	Columns can be specified in 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 selected, the set values are 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 types, refer to Internal data types. When you select Time, the value of date isn't used.
Column list/Number settings	Required	Not available	Select the setting method of the number format. Specify Number Specify the number format of the cell. Use Number of cell (Default) Use the number format of the write target cell. Use Number of start row Use the number format of the start row for writing.	When Use Number of cell or Use Number of start row is selected, the number format set in Column list/Number isn't used.
Column list/Number	Optional	Available	Select or enter the number format to 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 the input schema. Selected Elements to write format/comment are set in the input schema. Not selected (Default) Elements to write format/comment aren't set in the input schema.	When this field is selected, the input schema will be in XML type. For details, refer to Input schema.

Property actions

Item name	Description	Remarks
Get sheet name list	Get sheet names from the file specified in File path.	Disabled when the specified file doesn't exist or script variables are set in File path.
Get the column list	Get the column information from Sheet name of the file specified in File path and set it to Column list.	Disabled when the specified file doesn't exist or script variables are set in File path/Sheet name. The column information is obtained from the cells of the first row, and the cell values are set in Heading. When a row number is set in Start condition, the column information is obtained from the cells of that row. When the target cell is blank, that column won't be set. Note 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 the start condition settings. Selected Enable the start condition settings. Not selected (Default) Disable the start condition settings.
Start condition	Optional	-	Specify the condition to start writing. Each row can be operated with the following buttons: Up Moves the selected row upward by one row. Down Moves the selected row downward by one row. Add Adds a row. Delete Deletes the row.	Enabled when Enable start condition settings is selected. When the start condition is satisfied, data writing is started. When the start condition isn't satisfied, data isn't written.
Start condition/Column(Heading)	Required	Not available	Select or enter the columns to check for the condition to start writing.	Columns set in Column list of Required settings are displayed. When Heading is set, <column>(<heading>) is displayed. In Columns, columns that aren't set in Column list can also be set.
Start condition/Row number	Required	Available	Enter the number of the row to check for the condition to start writing. When (Do not specify row number) is selected, all rows are checked for the condition to start writing.	Targeting the row whose row number was entered, the cell values are compared with Condition value.
Start condition/Condition value	Required	Available	Enter the condition value of the condition to start writing. 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 isn't blank.	When the type of the cell is "Numeric value", "Date", or "Time", comparison with the condition value isn't performed.
Rule for multiple conditions	Required	Not available	Select the rule for determination of fulfillment when multiple conditions are set in Start condition. Set as start row if any of the conditions is satisfied (Default) Set the row as the start row if any one 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. For the start conditions that you set, when the top condition is satisfied, the next condition below it is used to perform the check.	Details about when you select Judge in any order, set as start row if all conditions are met Once the start condition is satisfied, it's thereafter assumed to be satisfied, and is not checked during the checks of the subsequent conditions. The detail when you select Judge from top, set as start row if all conditions are met Once the start condition is satisfied, it's thereafter assumed to be satisfied, and is not checked during the checks of the subsequent conditions. When the condition isn't satisfied, checks using the conditions below it aren't performed.
Number of rows to skip to start writing	Optional	Available	Specify the number of rows to skip from the starting row for writing that is set in Start condition, to the row from which writing actually starts.	If you set 0 or don't set any number, rows aren't skipped, and writing starts from the starting row for writing that is set in Start condition. If the starting row for writing that is set in Start condition is the 5th row and 2 is set to Number of rows to skip to start writing, writing starts 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 the heading in the first row where the writing is started. Selected The Heading is written in the first row in "General" format. Not selected (Default) The headings aren't written.
Overwrite	Optional	Not available	Select whether or not to overwrite when data exists in the specified file. Selected (Default) Overwrite the specified sheet. Not selected Don't overwrite. If the specified sheet exists, an error occurs.	When the sheet doesn't exist in the write destination, a sheet is created.
Insert	Optional	Not available	Select whether or not to insert data when writing data to the specified file. Selected Insert it to the row where writing starts. Not selected (Default) Overwrite from the row where writing starts.	Enabled when Overwrite is selected. If the start condition is set, it's 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. Selected Delete the sheet. It will be ignored when the specified sheet doesn't exist. Not selected (Default) Don't delete the sheet.	Enabled when Overwrite is selected.
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 connector.

Schemas

Input schema

When Specify format/comment in Mapper to write isn't selected
- It's table model type.
- The number of columns varies depending on the Column list settings.
= Remarks =
For schema structure, refer to Table model type .

When Specify format/comment in Mapper to write is selected

It's 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	-	Represents the value to be written in the cell.	Appears once for each column set in Column list.
format	-	Represents the format/comment to be written in the cell.	In this element, enter a string that is output from "Generate Excel Format" logic. Only the format/comment that is output from "Generate Excel Format" logic will be written. When format isn't written, the format of the write destination cell is used.

Output schema

None.

Loading schema in Mapper

The schema is loaded automatically.

= Remarks =

For details, refer to Edit Schema.

Transaction

Transaction is supported.

Parallel Stream Processing

When Specify format/comment in Mapper to write isn't selected
- PSP is supported.
When Specify format/comment in Mapper to write is selected
- As the input schema is XML type, it can't be used in PSP.

= Remarks =

For details on PSP, refer to Parallel Stream Processing.

Available component variables

Component variable name	Description	Remarks
count	The number of written data records is stored.	The default value is null.
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 error is stored.	The default value is null.
message_code	When an error occurs, the code of the message code corresponding to the error is stored.	The default value is null.
message_level	When an error occurs, the severity of the message code corresponding to the error is stored.	The default value is null.
error_type	When an error occurs, the error type is stored.	The default value is null. The format of the error type is as follows. Example: java.io.FileNotFoundException
error_message	When an error occurs, the error message is stored.	The default value is null.
error_trace	When an error occurs, the trace information for the error is stored.	The default value is null.

Message codes, exception messages, and limitations

Connector	Message code	Exception message	Limitations
Messages and limitations of the Excel(POI) connector

Notes

When this operation is placed in a loop operation, refer to Notes of Loop by Number of Data.