XLSDataWriter
We assume that you have already learned what is described in:
If you want to find the right Writer for your purposes, see Writers Comparison.
Short Summary
XLSDataWriter writes data to XLS or XLSX files.
![[Important]](figures/important.png) | Important |
|---|---|
Since Clover 3.3., there are new powerful components available for spreadsheet reading/writing - SpreadsheetDataReader and SpreadsheetDataWriter. The preceding XLS components (XLSDataReader and XLSDataWriter) have remained compatible, though. |
| Component | Data output | Input ports | Output ports | Transformation | Transf. required | Java | CTL |
|---|---|---|---|---|---|---|---|
| XLSDataWriter | XLS(X) file | 1 | 0-1 | no | no | no | no |
Abstract
XLSDataWriter writes data to XLS or XLSX files (local or remote). It can also compress output files, write data to console, output port, or dictionary.
![[Note]](figures/note.png) | Note |
|---|---|
Remember that XLSDataWriter has high memory requirements and may store data in the memory (see the Disable temporary files (inMemory mode) attribute). When working with XLSX files, all data are stored in the memory. |
Icon
Ports
| Port type | Number | Required | Description | Metadata |
|---|---|---|---|---|
| Input | 0 | yes | For received data records | Any |
| Output | 0 | no | For port writing. See Writing to Output Port. | One field (byte,
cbyte, string). |
XLSDataWriter Attributes
| Attribute | Req | Description | Possible values |
|---|---|---|---|
| Basic | |||
| Type of parser | Specifies the formatter to be used. By default, component guesses according the extension (XLS or XLSX). | Auto (default) | XLS | XLSX | |
| File URL | yes | Attribute specifying where received data will be written (XLS or XLSX file, console, output port, dictionary). See Supported File URL Formats for Writers. | |
| Sheet name | 1) | The name of the sheet to which records are written. If
not set, a sheet with default name is created and inserted as
the last of all sheets. Can also be a sequence of field names,
each of them is prefixed by dollar sign, separated by
semicolon, colon, or pipe. Thus, to different values of such
sequence, different sheets are created. For example, $Country;$City. This way, different
countries along with cities create different sheets. Remember
that the sheets are created in the order of record values. For
this reason, first you should sort records on this field and
only then write the records to the output XLS(X) file. | Sheet[0-9]+ (The number in the name is the number of the last sheet with the same name structure: Sheet[0-9]+.) |
| Sheet number | 1) | The number of the sheet to which records are written. If not set, a sheet with default name is created and inserted as the last of all sheets. | 0-N |
| Charset | Encoding of records written to the output. | ISO-8859-1 (default) | <other encodings> | |
| Append to the sheet | By default, new records overwrite the older ones in one
sheet. If set to true, new records are
appended to the older records stored in the sheet(s). | false (default) | true | |
| Metadata row | Number of the row to which the field names should be written. By default, field names are written to the header of the sheet. | 0 (default) | 1-N | |
| Advanced | |||
| Create directories | By default, non-existing directories are not created.
If set to true, they are created. | false (default) | true | |
| Disable temporary files (inMemory mode) | Temporary files, which are created during the writing,
are stored on the disk by default. If you set this
attribute to true, you will
force storing those files in the memory.
Note: it can be applied to xls
files only.
| false | |
| Directory for temporary files | The target directory for temporary files
which are created during the writing.
If not set, the default system temp
directory is used. This attribute has no effect
as long as Disable temporary files (inMemory mode)
is true. Note: it can be applied to xls
files only.
| ||
| Start row | Row of the sheet starting from which the records are written. By default, records are written to the sheet starting from the first row. | 1 (default) | 2-N | |
| Start column | Column of the sheet starting from which the records are written. By default, records are written to the sheet starting from the first column. | A (default) | B-* | |
| Records per file | Maximum number of records to be written to one output file. | 1-N | |
| Number of skipped records | Number of records to be skipped. See Selecting Output Records. | 0-N | |
| Max number of records | Maximum number of records to be written to all output files. See Selecting Output Records. | 0-N | |
| Exclude fields | Sequence of field names separated by semicolon that should not be written to the output. Can be used when the same fields serve as a part of Partition key or when the field(s) is(are) selected as Sheet name as shown above. | ||
| Partition key | Key whose values define the distribution of records among multiple output files. See Partitioning Output into Different Output Files for more information. | ||
| Partition lookup table | 2) | ID of lookup table serving for selecting records that should be written to output file(s). See Partitioning Output into Different Output Files for more information. | |
| Partition file tag | By default, output files are numbered. If it is set to
Key file tag, output files are named
according to the values of Partition key
or Partition output fields. See Partitioning Output into Different Output Files for more
information. | Number file tag (default) | Key file tag | |
| Partition output fields | 2) | Fields of Partition lookup table whose values serve to name output file(s). See Partitioning Output into Different Output Files for more information. | |
| Partition unassigned file name | Name of the file into which the unassigned records should be written if there are any. If not specified, data records whose key values are not contained in Partition lookup table are discarded. See Partitioning Output into Different Output Files for more information. | ||
Legend:
1) One of these attributes can be specified. Sheet name has higher priority. Before creation of output file, only Sheet name can be set. If neither of these is specified, new sheet is created on each graph run.
2) Either both or neither of these attributes must be specified.
| Important |
|---|---|
Remember that if you want to write data into multiple sheets of the same file, you must write data to each sheet in a separate phase! |