Write Specified Cell

Operation Name

Write Specified Cell

Function Overview

Data is written to the specified cell of the 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 ".xlsx" and ".xlsm" expansion are processed as Excel book, and files with other expansions are processed as Excel 2003 book
  • 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.
Cell settings Optional
-
Specify the cell to which data is written.

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.
 
Cell settings / Sheet name Required Available Select or input Sheet of Excel file specified in [File].
  • If there is no sheet with the specified name exists, a new sheet will be created at the beginning of the book.
  • Does not distinguish on upper/lower cases.
Cell settings / Cell address Required Available Specify the cell address to which data should be written to.
Example: A1
 
Cell settings / Cell name Optional Available Specify the names of the cells.
  • [Cell name] will be used in Mapper schema. This value is not used when writing.
Cell settings / Style Required Not available Select whether to write the type/format information.
  • [Specify format]:
    Writes set type/format information.
  • [Use format to the cell for writing]: (default)
    Writes using the cell type/format information of the target cell to write.
  • If [Use format to the cell for writing] is selected, neither the [Cell settings / Type] nor [Cell settings / Format] is used.
Cell settings / Type Required Not available Specify the type of cell.
  • [General]: (default)
  • [String]:
  • [Number]:
  • [Date]:
 
Cell settings / 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
J - D (the first letter of month) 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 Currency
# Show only valid digits Number
  • Format:
    ##.##
  • Input value:
    Format:
  • 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:
    Format:
  • 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".
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

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

PSP is not supported.

Available Component variables

Component variable name Description Remarks
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

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
No file path is specified.
[File] is not specified. [File] needs to be specified.
InvalidPropertyConfigurationException
No target cell is specified.
[Cell settings] is not specified. [Cell settings] need to be specified.
InvalidPropertyConfigurationException
No sheet name is specified.
[Cell settings / Sheet name] is not specified. [Cell settings / Sheet name] needs to be specified.
InvalidPropertyConfigurationException
No cell address is specified.
[Cell settings / Cell address] is note specified. [Cell settings / Cell address] need to be specified.
InvalidCellAddressException
The cell address specified is invalid.
The cell address specified in the [Cell settings / Cell address] is invalid. Ensure the specified cell address in the [Cell settings / Cell address] is valid.
FileIsDirectoryException Path input in [File] is directory. Input file path in [File].
IllegalFormatException Either [Format] or the input data that corresponds to [Type] specified in the [Cell settings] is invalid. Ensure the combination of the input data, types, and format are correct.
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 Specify an excel 2003 workbook without a read password in the [File].
jxl.write.biff.RowsExceededException The row number specified in the [Cell address] exceeds the maximum row number allowed. Make sure the value [Cell address] is smaller than what is allowed.
jxl.read.biff.BiffException
Unable to recognize OLE stream
Excel 2003 workbook specified in [File] is not correct. Check the following.
  • Whether file specified in [file] is the Excel book included in support version
  • 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:
Operating ZIP file entry is broken
Excel book specified in [File] is under false condition. Confirm the following.
  • Whether the file specified in [File] is Excel book
  • Whether file that's not Excel book is set in ".xlsx" or ".xlsm" and specified to [File]
  • Whether file is not broken
ZipEntryNotFoundException:
Entry of the specified name does not exist in zip file
Reading password is set to Excel book specified in [File] or is under false condition. Confirm whether Reading password is not set in Excel book specified in [File] and whether the file is not broken.

Notes