Write

Operation Name

Write

Function Overview

Write input data to an Excel file.

Data Model

Table model type.
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 to show 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

Required

Available

Enter the Excel file path.

You can click [Browse] button to launch file chooser and find the file.

A file with ".xlsx" or ".xlsm" extension is processed as an Excel workbook, and a file with other extension is processed as an Excel 2003 workbook.
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

Optional

Available

Select or enter a sheet of the Excel file specified in [File].

When it is omitted, if the file specified in [File] exists, data will be written in the first sheet. If it does not exist, a sheet named “Sheet1” will be created.
Case-insensitive.

Column list

Optional

Specify column names.

Each column can be operated with the following buttons.

[Add]:
Add a column.
[Up]:
Move the selected column upwards by one row.
[Down]:
Move the selected column downwards by one row.
[Delete]:
Delete the column.

Column list/Column name

Required

Available

Enter the column name.

Column list/Style

Required

Not available

Select whether or not to write type / format information.

[Specify format]:
Write type / format information.
[Use format of target cell]: (default)
Write data using type / format information of the cell to be written.
[Use format of write starting row]:
Write data using type / format information of the cell in the writing starting row.

When [Use format of target cell] or [Use format of write starting row] is selected, the type / format information set in [Column list/Type] and [Column list/Format] will not be used.

Column list/Type

Required

Not available

Select the type of [Column name].

[General]: (default)
[Text]:
[Number]:
[Date]:

Column list/Format

Optional

Available

Select or enter a format.

Symbol	Meaning	Available type	Example
yyyy	1900-9999 (A.D.)	Date	2005
yy	00-99 (A.D.)	Date	05
m	1-12 (Numeric representation of month)	Date	1
mm	01-12 (Numeric representation of month)	Date	01
mmm	Jan-Dec (Abbreviated month)	Date	Jan
mmmm	January-December (Month)	Date	January
mmmmm	J-D (The first letter of month)	Date	J
d	1-31 (Date)	Date	1
dd	01-31 (Date)	Date	01
ddd	Sun-Sat (Abbreviated day of week)	Date	Mon
dddd	Sunday-Saturday (Day of week)	Date	Monday
h	Hour (1-12)	Date	1
hh	Hour (0-23)	Date	01
h:m	Minutes	Date	1:1
h:mm	Minutes	Date	1:01
h:mm:s	Seconds	Date	1:01:1
h:mm:ss	Seconds	Date	1:01:01 AM
#,##0	Currency	Number	123,0
0%	Percentage	Number	50%
#	Display only the significant digits	Number	Format: ##.## Input value: 12.345 Output value: 12.35
0	Display only the specified digits and if the value is less than the number of digits, fill it with 0	Number	Format: 000.00 Input value: 12.345 Output value: 012.35

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
Open Excel Wizard	Open the Excel Wizard with the file specified in [File]. For how to configure, refer to "Excel Write Wizard".	Disabled when the specified file does not exist / when it is not an Excel file included in the supported versions / when a variable is set in [File]. When [Sheet name] is set, the sheet opens in Excel Wizard. When sheet name is not set, when the configured sheet does not exist, or when a variable is set in [Sheet name], the sheet displayed when saved in Excel application opens. In Studio for Web, 50MB is the maximum file size that can be displayed on the Excel Wizard. In Studio for Web, when the file size to be displayed exceeds 5MB, a confirmation dialog appears.
Update column list	Get column names and types from the first row values of the file specified in [File] and set them in [Column list].	Disabled when the specified file does not exist or a variable is set in [File]. When the value of a cell is empty, a type different from the cell's one might be set to [Column list/Type].

Write settings

Item name	Required/Optional	Use of variables	Description	Remarks
Insert column names in the first row	Optional	Not available	Select whether or not to insert column names in the first row of the specified file. [Checked]: Column names will be inserted in the first row. Format will be set as “General”. [Not checked]: (default) Column names will not be inserted.
Overwrite	Optional	Not available	Select whether or not to overwrite when data exists in the specified file. [Checked]: (default) Overwrite. When the specified sheet does not exist, a new sheet will be created to write to. [Not checked]: Do not overwrite. When the specified sheet exists, FileAlreadyExistedException is thrown and the processing fails.
Insert	Optional	Not available	Select whether or not to insert data. [Checked]: Insert to the first row of the specified file. When the specified sheet does not exist, a new sheet will be created to write to. [Not checked]: (default) Overwrite.	Enabled when [Overwrite] is checked. When the write position is specified, data will be inserted to the specified position. The format of the inserted position will be applied. Even when [Specify format] is specified in [Style], it will be ignored. When the specified sheet does not exist, the format will be set as “General”.
Delete target sheet	Optional	Not available	Select whether or not to delete the specified sheet before writing data to the specified file. [Checked]: Delete it. It will be ignored when the specified sheet does not exist. [Not checked]: (default) Do not delete it.	Enabled when [Overwrite] is checked.
Specify write position	Optional	Not available	Select whether or not to specify the position to start writing. [Checked]: Specify the writing position. [Not checked]: (default) Do not specify the writing position.
Condition to specify starting cell	Optional	Not available	Select a condition to specify a cell where writing starts. [Specify cell address]: (default) Start writing from the address specified in [Starting cell address]. [Specify cell value]: Start writing from the cell that matches [Value of starting cell].	Enabled when [Specify write position] is checked. Depending on selected item, the corresponding input field ([Starting cell address]/[Value of starting cell]) is displayed.
Starting cell address	Optional	Available	Enter the cell address to start writing. Example: A1	Enabled when [Specify cell address] is selected in [Condition to specify starting cell].
Value of starting cell	Optional	Available	Enter the cell value to start writing.	Enabled when [Specify value of starting cell] is selected in [Condition to specify starting cell].

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

When [Specify format/comment in Mapper to write] is not checked
- It is table model type.
- The number of columns varies depending on the [Column list] settings.
- Refer to “Schema of table model type” for more details.

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 in Mapper. Overwriting the format selected in [Column list/Style], the format/comment output from “Generate Excel Format” logic will be written. The format/comment that is not output from “Generate Excel Format” logic will not be written. As for format that will be written, the format selected in [Column list/Style] will be used as is. When nothing is input, format/comment will not be written. The format selected in [Column list/Style] will be used as is.

Output schema

None.

Loading schema in Mapper

Schema is loaded automatically.

Refer to “Edit Schema” for more details.

Transaction

Transaction is supported.

Transaction is enabled only when [Execute transaction processing] of [Transaction] is checked.

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.

Refer to “Parallel Stream Processing” for more details.

Available Component Variables

Component variable name	Description	Remarks
count	The number of written data is stored.	Default value is null.
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

When null is input in column element of input schema, the operation is as follows.
- When [Specify format/comment in Mapper to write] is not checked
  - If there is a value in the destination cell, it will not be written.
  - If there is no value in the destination cell, an empty string with “General” format will be written to the cell.
- When [Specify format/comment in Mapper to write] is checked
  - As the input schema is XML type, an empty string will be written to the cell.

When writing a string type input data to a cell with “Date” format, use data formatted with the following date format or one specified in “xmlfw.daterenderingformat” property of system.properties.

Refer to “Property Reference” for more details.

Format	Example
yyyy-MM-dd'T'HH:mm:ss.SSSZZ	2007-05-10T15:32:12.708+0900
yyyy-MM-dd'T'HH:mm:ss.SSS	2007-05-10T15:32:12.708
yyyy-MM-dd'T'HH:mm:ssZZ	2007-05-10T15:32:12+0900
yyyy-MM-dd'T'HH:mm:ss	2007-05-10T15:32:12
yyyy-MM-dd'T'HH:mm	2007-05-10T15:32
yyyy-MM	2007-05
yyyy/M/d K:m a	2007/5/10 3:32 PM
yyyy/M/d H:m:s	2007/05/10 15:32:12
yyyy/M/d H:m	2007/05/10 15:32
yyyy/M/d	2007/05/10

When [Use format of target cell] is selected in [Column list/Style] and the date format of destination cell is one of the following, the format will not be “Date” even if the data is written in date type.

Type of date format Example Remarks

[$-411]ge.m.d;@ h13.3.14

[$-409]yyyy/m/d h:mm AM/PM;@ 13/3/14 1:30 PM When the destination cell contains any data, it can be written as date.

Custom formats are not tested.

Input data formatted in hours:minutes:seconds will be written as not “Date” but “General” format.

After writing, cell format may be indeterminate.

You cannot write data to an Excel file that is encrypted with a password.

When a large Excel file is specified in [File], OutOfMemoryError may occur, or it might take some time to analyze the file contents and operations in the screen may be delayed.
In that case, you can prevent this issue by specifying a variable such as script variable in [File].

When writing large data to an Excel workbook in a network drive, “IOException: Insufficient system resources exist to complete the requested service.” error may occur.
In that case, you can prevent it by put a check in [Execute transaction processing].

Writing formula is not supported.

Writing to an Excel file with digital signature added is not supported.

When writing to an Excel file with sheet protection set, data will be written, and if a password is set, the password will be maintained.

When specifying [Background color] in "Generate Excel Format" logic, items in Fill tab other than background color will be set as default value.

In an Excel 2003 workbook, when writing a number to a cell of “General” format, that number will be written as string and an error check will be displayed.
Even in a cell of “Number” format, if the cell contains no value, an error check may be displayed.
In these cases, you can write it as number by putting a value in the destination cell in advance.

In an Excel 2003 workbook, when writing to a cell which contains no value, a value may be written with “Arial” font and “10” font size as the cell's font information cannot be acquired.
In that case, you can write it using the font information of the destination cell by putting a value in the cell in advance.

In an Excel 2003 workbook, when all of the following four conditions are satisfied, even if a format is specified with “Generate Excel Format” logic, it will not be reflected in the destination cell.
- Any of the following conditions is satisfied
  - [Use format of target cell] or [Use format of write starting row] is selected in [Column list/Style]
  - [Insert] is checked
- Comment is inserted to the destination cell
- No value is contained in the destination cell
- No format is set to the destination cell
When writing to an Excel 2003 workbook, if a password to modify is set to the workbook, writing will be performed and the password will be deleted.

When writing to an Excel 2003 workbook, the following formats of all cells in the file specified in [File] will be changed to the format of whole cell.
- Font name
- Font style
- Font size
- Underline
- Font color
- Effects
When writing to an Excel 2003 workbook, the following settings of the file specified in [File] disappear.
- Text box
- Auto shape
- Pivot table
- Label
- Group box
- Check box
- Option box
- List box
- Scroll bar
- Spin button
- Word art
- Auto filter
- Diagonal border specified in cell format
- Tab color
- Allow users to edit ranges
When writing to an Excel 2003 workbook, when “Center across selection” is set to a cell in the file specified in [File], the format will be changed to “General”.

When writing to an Excel 2003 workbook, data cannot be inserted beyond the maximum number of rows (65536).

When writing to an Excel workbook, if a password to modify is set to the workbook, writing will be performed and the password will be retained.

When writing to an Excel workbook, if a string is written to a cell, the format of that cell will be “Text”.

When writing to an Excel workbook, you need to specify columns in [Column list] to execute with [Insert column names in the first row] being checked.

When writing to an Excel workbook and if a number starting with “+” (plus symbol), it will be written as string and the workbook will be corrupted.

Type of date format	Example	Remarks
[$-411]ge.m.d;@	h13.3.14
[$-409]yyyy/m/d h:mm AM/PM;@	13/3/14 1:30 PM	When the destination cell contains any data, it can be written as date.

Major Exceptions

Exception name	Cause	Solution
InputDataNotFoundException	[Input data] is not specified.	Specify [Input data] or draw a data flow.
InputDataReadException Failed to read a format from input data.	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.
InvalidPropertyConfigurationException <property name> is not specified.	[<property name>] is not specified.	Specify [<property name>].
FileIsDirectoryException	The path entered in [File] is directory.	Enter a file path in [File].
FileAlreadyExistedException	The specified file does not exist in [File].	Change the file name of [File], or put a check in [Overwrite] of [Write settings].
IllegalFormatException	[Format] for [Type] specified in [Column list] is invalid.	Check the combination of type and format.
ColumnIndexOutofBoundsException	This is an exception that occurs when attempting to write more than the columns specified in [Column list].	Check the number of columns of the list and the input source.
InvalidStartCellAddressException: The specified starting cell address is invalid.	The cell address entered in [Starting cell address] is invalid.	Check [Starting cell address] and enter a valid cell address.
IllegalTargetWorkbookException	The specified workbook is in a state cannot be written to.	Another program might be using the file or the format of the workbook might be corrupted.
FileIsLockedException	The file specified in [File] is being locked.	Unlock it from Explorer or CLI.
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.
jxl.read.biff.PasswordException	A password to open is set in the Excel 2003 workbook specified in [File].	Enter an Excel 2003 workbook in which a password to open is not set in [File].
jxl.write.biff.RowsExceededException	The row number specified in [Starting cell address] exceeds the maximum value.	Check the value of [Starting cell address].
jxl.read.biff.BiffException Unable to recognize OLE stream	The Excel 2003 workbook specified in [File] is in an invalid state.	Check the following. Whether the file specified in [File] is an Excel 2003 workbook included in Supported Versions Whether a file that is not Excel 2003 workbook is specified with ".xls" extension in [File] Whether the file is corrupted
jxl.read.biff.BiffException The file format is corrupt	The Excel 2003 workbook specified in [File] might be corrupted.	Check the following. Check whether the file specified in [File] is corrupted.
jxl.read.biff.BiffException The input file was not found	The Excel 2003 workbook specified in [File] is in an invalid state.	Check the following. Whether the file with 0 bytes is not specified.
IndentExceedsMaxException The specified indent exceeds the maximum value that can be specified.	The value of [Indent] specified in “Generate Excel Format” logic exceeds the maximum value that can be specified for an Excel 2003 workbook.	Check the value of [Indent] specified in “Generate Excel Format” logic.
ZipEntryBrokenException: The entry of the operating ZIP file is broken.	The Excel workbook specified in [File] is in an invalid state.	Check the following. Whether the file specified in [File] is an Excel workbook Whether a file that is not Excel workbook is specified with ".xlsx" / ".xlsm" extension in [File] Whether the file is corrupted
ZipEntryNotFoundException: Entry of the specified name does not exist in the zip file	The Excel workbook specified in [File] is protected with password to open or is in an invalid state.	Check whether password to open is set to the Excel workbook specified in [File], and whether the file is corrupted.

Notes

When placing inside loop processing, refer to Notes of Loop by Number of Data.