Excel Serializer
[ | version 3.2]
Connector
The connector is permitted for use of the platform background agent.
Connector processing type: Both (Row by row & Bulk), Default type: Bulk!
This connector allows the creation of an Excel file in xlsx format. The resulting file can have more sheets and also include headers based on the input schema.
Data Types
Input schema is mandatory and contains rows that will be written to the created Excel file. Based on data type values will be written to an Excel file e.g boolean will be written as TRUE/FALSE.
The specific type is also DateTime and its format can be selected in configuration, cell will contain the whole value, but visible can be just the date part if in configuration will be format MM/dd/yyyy instead of default MM/dd/yyyy HH:mm:ss.
The DateTime display format can be specified by these placeholders m, d, y, h, s, AM/PM and separators -, :, /
MM/dd/yyyy- visible just date partMM/dd/yyyy HH:mm:ss- whole datetime visibleHH:mm:ss- time part visibleh:mm:ss AM/PM- time part with
Generating more than one sheet
If the Sheet tab definition configuration property is filled then the connector will search for the column by its name in the input schema. This column must be of String type and
cannot allow null values and the max length is 31 characters and cannot contain special characters :\/?*[].
Input data will be grouped by values from this column and pushed to a specific sheet in an Excel file which will have the same name as the value.
Example of data and output
The input example with the Sheet tab definition Team and in the configuration is the option header enabled.
Name String |
ID Number |
Team String |
|---|---|---|
| Jaroslav | 1 | Project 1 |
| Tomas | 2 | Project 1 |
| Jiri | 3 | Project 1 |
| Pavel | 4 | Project 2 |
| Libor | 5 | Project 2 |
The output will be an Excel file encoded in base64 and will contain following two sheets:
Sheet: Project 1
| Name | ID |
|---|---|
| Jaroslav | 1 |
| Tomas | 2 |
| Jiri | 3 |
Sheet: Project 2
| Name | ID |
|---|---|
| Pavel | 4 |
| Libor | 5 |
Excel Serializer configuration
Include header
Check if the first line contains the header based on the input schema.
Sheet tab definition
If the Sheet tab definition is empty, all rows will be written to the first Excel sheet on the spreadsheet. The sheet tab will have the default name. All the columns will be displayed on the respective sheet.
Example:
Input data in the column Project acquire just 1 value (value: Project 1).
return [
{ ID: "1", Name: "Adam", Project: "Project 1" },
{ ID: "2", Name: "Bruno", Project: "Project 1" },
{ ID: "3", Name: "Charlie", Project: "Project 1" },
{ ID: "4", Name: "David", Project: "Project 1" },
{ ID: "5", Name: "Ed", Project: "Project 1" }
];
If the Sheet tab definition is configured with the name of one of the columns, and the column data contain at least 2 different values, then the Excel sheet tabs will acquire names according to the values of the specified column in the Sheet tab definition. The column used to define the Sheet tab definition will not be part of the columns displayed on the sheet.
Example:
Input data in the column Project acquire more than 1 value (3 different values: Project 1, Project 2, Project 3).
return [
{ ID: "1", Name: "Adam", Project: "Project 1" },
{ ID: "2", Name: "Bruno", Project: "Project 1" },
{ ID: "3", Name: "Charlie", Project: "Project 2" },
{ ID: "4", Name: "David", Project: "Project 2" },
{ ID: "5", Name: "Ed", Project: "Project 3" }
];
If the column data of the specified column in the Sheet tab definition contain always the same value in each row of the table, all rows will be written to the first Excel sheet of the spreadsheet and the Sheet tab will acquire the name according to that single value. The column used to define the Sheet tab definition will not be part of the columns displayed on the sheet.
Example:
Columns
The configuration columns allow to specify which columns defined on the input schema will be written into the generated file.
Example: If the input schema has got columns A, B, C, and D and in configuration Columns, you only specify columns A and D, then the output data file will only include these 2 columns i.e. A and D.
If the Columns are not specified, the configuration will consider all columns from the input schema and include them in the generated file.
Directory string
The definition of the directory where the created Excel is intended to be located. The definition is included in the output schema.
You can use static/absolute definition, writing the exact path: e.g. Folder/Subfolder1 or you can use parameters from the input data if applicable e.g. Folder/${input.ID}_${input.Date}.
Filename string
The definition of the created Excel file Filename in the output schema.
You can use static/absolute definition, writing the exact file name: e.g. test.xlsx
or you can use parameters from the input data if applicable e.g. ${input.Date}.xlsx.
Do not forget to add the file extension .xlsx.
Exclusion of the columns from the output used as placeholders
If any input data column is used as a placeholder to define the directory string or the filename string, it will be automatically excluded from the output data. This does not apply, in case you define the Column configuration and define the exact columns you want to include to the output.
Excel Format configuration
DateTime Format
Custom date time format
Example
- 'MM/dd/yyyy'
- 'HH:mm:ss'
- 'MM/dd/yyyy HH:mm:ss'
- ...
Input & Output Schema
Input
Data schema is mandatory
The connector requires mandatory input or output data schema, which must be selected by the user from the existing data schema repository or a new one must be created. The connector will fail without structured data.
Output
The output shcema contains besides the item Data: base64, the Directory and the Filename.
Excel serializer output (ver. 1.0.0)
| Column | Data type | Allow null | Description |
|---|---|---|---|
| Directory | string | Yes | Directory composed from the configuration item 'Directory string'. |
| Filename | string | Yes | File name composed from the configuration item 'Filename string'. |
| Data | Base64 | No | Binary data encoded in Base64 string. |
Example output data
Directory: The name of the directory, where the excel file should be located (in case it would be writen to file after serializing step).
Filename: The Excel file name.
Data:Base64 encoded content of Excel file.
Data: Base64 |
|---|
| ewogICAgIlN1Y2Nlc3MiOnRydWUKfQ== |
Release notes
3.2.0
- Excel serializer configuration enhanced with 3 new configuration fields:
Columns,Directory stringandFilename string. - Enhanced input schema structure includes: Directory: string, Filename: string and Data: base6.4
3.1.5
- Plugin binaries update as a result of included connector change.
3.1.4
- Fixed nullable data values in input schema processing.
3.1.3
- Fixed shared nuget package versions.