Write

Operation Name

Write

Function Overview

Writes input data to Excel file.

Data Model

Table model type.
However, if [Specify format/comment in Mapper to write] is checked, the type will be XML type.

Properties

For information about using 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 the component on the script canvas.

Required settings

Item name

Required / Optional

Use of Variables

Description

Remarks

File

Required

Available

Enter the path of the Excel file.

Click [Browse] button to activate the file Selectr and Select the file.

Files with extension ".xlsx" and ".xlsm" are treated as an Excel workbook. Files with other extension are treated as Excel 2003.
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 name

Optional

Available

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

If you omit the sheet name, [File] menu if the specified file is written to the first sheet, if it does not exist "Sheet1" sheet that will be created.
Does not distinguish on upper/lower cases.

Column list

Optional

The column name for each column.

Each column can be operated with the following buttons.

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

Column list / Column name

Required

Available

Enter a column name for each column.

Column list / Style

Required

Not available

Select whether to write the type/format information.

[Specify format]:
Writes set type/format information.
[Use format of the cell for writing]: (default)
Writes using the cell type/format information of the target cell to write.
[Use format of writing beginning row]:
Writes using the cell type/format information of the starting row to write.

If you Select [Use format of the cell for writing] or [Use format of writing beginning row], Type information specified at [Column list / Type] and format information specified at [Column list / Format] is not used .

Column list / Type

Required

Not available

Select [Column name] type.

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

Column list / Format

Optional

Available

Enter or select the format for each type.

Symbol	Meaning	Available types	Example
yyyy	1900-9999 (Year AD)	Date	2005
yy	00-99 (Year AD)	Date	05
m	1-12 (Numeric representation of the month)	Date	1
mm	01-12 (Numeric representation of the month)	Date	01
mmm	Jan - Dec (English abbreviation of month)	Date	Jan
mmmm	January - December (English name of month)	Date	January
mmmmm	J - D (the first letter of month)	Date	J
d	1-31 (Day)	Date	1
dd	01-31 (Day)	Date	01
ddd	Sun - Sat (English abbreviation for day of week)	Date	Mon
dddd	Sunday - Saturday (English of day of week)	Date	Monday
h	Hour (1-12)	Date	1
hh	Hour (0-23)	Date	01
h:m	Time	Date	1:1
h:mm	Time	Date	1:01
h:mm:s	Second	Date	1:01:1
h:mm:ss	Second	Date	1:01:01
#,##0	Currency	Number	123,0
0%	Percentage	Number	50%
#	Show only valid digits	Number	Format: ##.## Input value: 12.345 Output value: 12.35
0	Show only the specified number of digits, and fills with 0 when the value is short of digits	Number	Format: 000.00 Input value: 12.345 Output value: 012.35

Specify format/comment in Mapper to write

Optional

Not available

Select whether to set the element for writing format/comment to input schema.

[Checked]:
Sets the elements for writing format/comment to input schema.
[Not Checked]:(Default)
Does not set the elements for writing format/comment to input schema.

If [Checked], input schema will be XML type.
For details, refer to "Input Schema".

Property Action

Item name	Description	Remarks
Start Excel Wizard	Start Excel wizard with file specified at [File]. For information on setting, please refer to "Excel Write Wizard".	Version includes support for the specified file the specified file does not exist unless the workbook Excel [File] menu, if the variable is set to disabled. Opens the set sheet with excel wizard if [Sheet name] is set. If not set, or the set sheet does not exist, or any variable is set in [Sheet name], the sheet shown when the book was saved in Excel application is opened.
Update column list	Get column name and type value from the first line of file specified in [File], and set in [Column list].	Disables when specified file dose not exist, or variables are set in [File]. You can set different cell type in [Column list / Type] when the cell value is empty.

Write settings

Item name	Required / Optional	Use of Variables	Description	Remarks
Insert column name in the first row	Optional	Not available	Select whether to insert column names to the 1st line in specified file. [Checked]: Column names will be inserted into the first row.Format "General" are set. [Not Checked]: (default) Column names are not inserted.
Overwrite	Optional	Not available	Select whether to overwrite if specified file has data. [Checked]: (default) Overwrite.If there is no specified sheet, write to create a new sheet. [Not Checked]: Do not overwrite.If you have specified sheet, FileAlreadyExistedException write operation will fail is thrown.
Insert	Optional	Not available	Select whether to insert data when writing data to the specified file. [Checked]: Insert the first row of the specified file.If there is no specified sheet, write to create a new sheet. [Not Checked]: (default) Overwrite.	Enabled only when [Overwrite] is checked. If you specify a write position, data is inserted at the specified location. The format uses a form of the insertion point.Even if [Specify format] is selected at [Column list / Style], it is ignored. If specified sheet doesn't exist, "General" are set in the format.
Delete target sheet	Optional	Not available	Select whether you want to delete the specified sheet before writing data to the specified file. [Checked]: Remove.If there is no specified sheet will be ignored. [Not Checked]: (default) Do not delete.	Enables only when [Overwrite] is checked.
Specify writing position	Optional	Not available	Select whether you want to start position for writing. [Checked]: Specify the write position. [Not Checked]: (default) Does not specify a write-in position.
Specified conditions of cell that starts writing	Optional	Not available	Select the cell to start writing the specified criteria. [Specify cell address]: (default) [Starting cell address] typing and start the process of writing the address specified cell. [Specify value of starting cell]: [Value of starting cell] typing and start the burning process from the cell that matches.	Enabled if you check [Specify writing position]. By specified selecting method, switch corresponding input field ([Starting cell address] / [Value of starting cell]).
Starting cell address	Optional	Available	Enter the cell address as the condition to start reading. Example: A1	Enabled if you select [Specify cell address] at [Specified conditions of cell that starts writing].
The value of the starting cell	Optional	Available	Enter a condition to start writing the cell.	Enabled if you select [Specify value of starting cell] at [Specified conditions of cell that starts writing].

Transaction

Item name	Required / Optional	Use of Variables	Description	Remarks
Execute transaction processing	Optional	Not available	Select whether to perform transaction processing. [Checked]: Perform Transaction processing. [Not Checked]: (default) Do not perform transaction processing. Refer "File Type adapter transaction" for more information.

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

If [Specify format/comment in Mapper to write] has not been checked
- Will be table model type.
- Depending on [Column list] setting, number of columns differs.
- For schema structure, refer to "Table Model Type Schema".

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

Will be XML type.

For schema structure, it 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 write to the cell.	Appears per columns which is set at [Column List].
format	-	Shows the formats/comments writing to the cell.	For this element, input the string which was output from "Generate Excel Format" logic in Mapper. In the form of overwriting the formats selected in [Column list/Format setting], the formats/comments output from "Generate Excel Format" logic will be written. The formats/comments that are not output from "Generate Excel Format" logic will not be written. For the unwritten formats, the formats selected at [Column list/Format setting] will be used as they are. If nothing is input, the format selected at[Column list/Format setting] will be used as it is.

Output Schema

None.

Loading schema in Mapper

Schema is loaded automatically.

See "Edit Schema" for any details.

Transaction

Transaction is supported.

Transaction is valid only when [Execute transaction processing] in [Transaction] is checked.

PSP Usage

If [Specify format/comment in Mapper to write] is not checked
- PSP is supported.
If [Specify format/comment in Mapper to write] is checked
- PSP is not supported since input schema will be the XML type.

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

Available Component variables

Component variable name	Description	Remarks
count	Stores the count of the data.	The value defaults to null.
message_category	Stroes 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 occured error.	The value defaults to null.
message_level	Stores the severity of the corresponding message code of the occured 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

The operations when null is input to the column elements of input schema are as follows.
- If [Specify format/comment in Mapper to write] is not checked
  - If the writing cell has a value, will not operate writing.
  - If the writing cell does not have a value, will operate writing with writing an empty string of the display format "Standard".
- If [Specify format/comment in Mapper to write] is checked
  - Since input schema is the XML type, will operate writing with writing an empty string to the cell.

When writing the input data of string type to the cell of display format "Date", use the data with the following date formats or with the format specified at the property "xmlfw.daterenderingformat" of system.properties.

For system.properties information, refer to "Property reference"

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

[Column list / Style] menu to [Use format of the cell for writing] When that is selected, if the following format of the cells to be written will be written as a date format of the data type of date Can.

The 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 If the cell contains any data, it can be written as a date.

The user-defined formats and unverified.

HMS if the input value is written in the General be set at a Date column list.

After the write operation may be written without the final format of the cells.

You cannot write data to Excel file which is encrypted with password.

Specifying a large Excel file in the [File] may cause OutOfMemoryError. It may also impair the controllability of user interface.
To prevent this issue, specify a script variable dereferencing the workbook location in the [File].

When writing mass data in an Excel file on the network drive, an error "IOException: Due to the system resource shortage, cannot complete the requested service" may occur.
You can avoid it by checking [Execute transaction processing].

Formula is not supported.

Writing data to an Excel file with digital signatures is not supported.

If you write to an Excel file to which sheet protection is set, the writing will be done. Then, if password is set the password will be retained.

If you specify [Background color] in "Generate Excel format" logic, items in Fill other than background color will be set to default value.

Numeric data written to the cells in the Excel 2003 workbooks with their display types set to standard becomes string irrespectively and error check is displayed.
Error check is also displayed when there is no value found in the target cell whose display type is set to numeric.
Providing those cells numeric values prior to the write operation will prevent this problem.

Excel 2003 workbook, when writing is not in the cell, the cell can not get the font information, font "Arial" , font size "10" may be written.
In this case, can you put a value to be written to a cell, can be written using the font information to be written to cells.

If fulfills all following conditions at Excel 2003 book, no formats will be reflected to target cell even if it is specified at "Generate Excel Format" logic.
- Corresponds to any of the following conditions
  - [Use format of the cell for writing] or [Use the format of writing beginning riw] is selected at [Column list/Format setitng]
  - [Insert] is checked
- Comment is inserted to the writing cell
- Value is not set to the writing cell
- No format is set to the writing cell
Excel 2003 When writing a book, workbook and write operation to write the password is set, the password is written will be deleted.

If you write to an Excel 2003 book, the following formats will be set to the formats of the whole cells for all cells of the file entered in [File].
- Font Name
- Font Style
- Font Size
- Underline
- Font Color
- Effects
When dealing with Excel 2003 specified in the [File], the followings items will be removed from the Excel 2003 workbook to which data is written by this operation.
- TextBox
- AutoShape
- PivotTable
- Label
- GroupBox
- CheckBox
- OptionButton
- ListBox
- ScrollBar
- SpinButton
- WordArt
- AutoFilter
- Slash specified in the formatting
- Tab Color
- Allow Users to Edit Ranges
Excel 2003 When writing the book, click [File] in the specified file into a cell "centered in the selection" If that had been set, when writing "normal" will change.

The max row limit for the Excel 2003 workbook is 65536 and no further rows can be added.

When writing to the Excel workbook, the workbook and write operation to write the password is set, the password is written will be retained.

When writing to the Excel workbook, writing a string to a cell, the cell format is a string.

When dealing with Excel workbook, operation cannot be performed if no column is specified in the [Column list] and [Insert column name in the first row] is checked contradictory.

If you are writting in the Excel workbook, when you write the numbers with "+" (plus), it will be written as a character string, and thus the workbook will be in the damaged condition.

The 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	If the cell contains any data, it can be written as a date.

Main exceptions

Exception name	Causes	Solution
InputDataNotFoundException	[Input data] is not specified.	Specify the [Input data],or draw the data flow.
InputDataReadException Failed to read format from input data.	The value specified at format element of input schema is invalid.	Input the value output from "Generate Excel Format" logic in Mapper, or do not enter any value at all if not necessary.
InvalidPropertyConfigurationException <Property name> is not specified.	[<Property name>] is not specified.	Specify [<Property name>].
FileIsDirectoryException	Path input in [File] is directory.	Input file path in [File].
FileAlreadyExistedException	File specified in [File] already exists .	Put check in change file name of [File], or [Overwrite] of [Write settings].
IllegalFormatException	[Column list] specified in [Type] for [Format] is invalid.	Please check the type of formats.
ColumnIndexOutofBoundsException	[Column list] is an exception that occurs when you attempt to write a column or columns specified by number.	Please check the number of columns of the original number of columns and input columns list.
InvalidStartCellAddressException: Starting cell address you specified is invalid	[Starting cell address] invalid address entered in the cell.	[Starting cell address] address please check the correct cell.
IllegalTargetWorkbookException	The workbook is specified is not writable.	See other programs that may be corrupted or the format of the book.
FileIsLockedException	File specified in [File] is locked.	Please go to Windows Explorer or CLI to unlock.
java.io.FileNotFoundException Another processing is in use.	File specified in [File] is being used by another process.	Please check whether it is used by other processing.
jxl.read.biff.PasswordException	Excel 2003 workbook specified in [File] is set with read password	Please input Excel 2003 workbook whose read password is not set in [File]
jxl.write.biff.RowsExceededException	[Starting cell address] is greater than the maximum specified line number.	[Starting cell address] Please check the value.
jxl.read.biff.BiffException Unable to recognize OLE stream	Excel 2003 workbook specified in [File] is not correct.	Check the following. If the file specified in [File] is Excel 2003 workbook included in version support or not. If the extension of a non-excel 2003 workbook is set to [.xls] and is specified in [File]. If the file is damaged or not.
jxl.read.biff.BiffException The file format is corrupt	The Excel 2003 workbook specified in [File] may be damaged.	Check the following. If the Excel 2003 workbook specified in [File] is damaged or not.
jxl.read.biff.BiffException The input file was not found	Excel 2003 workbook specified in [File] is not correct.	Check the following. If the file whose size is zero bytes is specified.
ZipEntryBrokenException: Working ZIP files entries are corrupt.	Excel workbook specified in [File] is not correct.	Check the following. If the file specified in [File] is an Excel workbook. If the extension of a non-excel workbook is set to "xlsx" or "xlsm" and is specified in [File] If the file is damaged or not.
ZipEntryNotFoundException: Entry of specified name didn't exist in zip file.	Excel workbook specified in [File] is set with a read password or is not correct.	Please confirm if the Excel workbook specified in [File] is set with a read password or not, or if the file is damaged or not.

Notes

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