JSON To Excel Converter
[ | version 3.0]
Connector
The connector is permitted for use of the platform background agent.
Connector processing type: Row by row!
This connector allows to create Excel file in xlsx format. Result file can have more sheets and also include headers based on input JSON.
JSON structure
Example json structure created by JS Mapper. One sheet named First sheet. Table header will be displayed on first line. It contains columns ID (int), Name (string), Created (datetime), IsActive (bool), Score (double) and Formula (formula).
const obj = {
"Sheets": [
{
"Name": "First sheet",
"DisplayHeader": true,
"Columns": [
{
"Name": "ID",
"DataType": "int"
},
{
"Name": "Name",
"DataType": "string"
},
{
"Name": "Created",
"DataType": "datetime",
"Format": "MM/dd/yyyy"
},
{
"Name": "IsActive",
"DataType": "bool"
},
{
"Name": "Score",
"DataType": "double"
},
{
"Name": "Formula",
"DataType": "formula",
"Format": "General"
}
],
"Rows": [
[
1,
"Jan",
"2020-12-08T15:37:00Z",
true,
3.7,
"A2+E2"
],
[
2,
"Petr",
"2021-12-08T15:37:00Z",
false,
1.23,
"A3+E3"
]
]
}
]
}
return [{"JSON": JSON.stringify(obj)}];
Data Types
Supported data types:
stringint- number typedatetime- display format can be specified in column definition, you can use placeholdersm, d, y, h, s, AM/PMand 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
bool- boolean value will be written asTRUE/FALSEdouble- dot as decimal separator (3.7)formula- string value of formula (A2+C2)
Restrictions
Sheet name cannot be null and max length is 31 characters and cannot contain special characters :\/?*[]. Two sheets cannot have same name.
Example Output
Output will be Excel file encoded in base64 and will have following sheet based on JSON created by JS Mapper above.
Special case is column Created which will have visible just date part according to Format definition.
Sheet: First sheet
| ID | Name | Created | IsActive | Score |
|---|---|---|---|---|
| 1 | Jan | 12/08/2020 | true | 2.2 |
| 2 | Petr | 12/08/2021 | false | 1.5 |
Input & Output Schema
Input
Input is JSON format which defines Excel structure and its data.
JSON (ver. 1.0.0)
| Column | Data type | Allow null | Description |
|---|---|---|---|
| JSON | JSON | No | Input in JSON format. |
Example output data
{
"Sheets": [
{
"Name": "Sheet 1",
"DisplayHeader": true,
"Columns": [
{
"Name": "Integer",
"DataType": "int",
"Format": null
},
{
"Name": "String",
"DataType": "string",
"Format": null
},
{
"Name": "Bool",
"DataType": "bool",
"Format": null
},
{
"Name": "Datetime",
"DataType": "datetime",
"Format": "MM/dd/yyyy HH:mm:ss"
},
{
"Name": "Double",
"DataType": "double",
"Format": null
}
],
"Rows": [
[
1,
"asd",
true,
"2021-12-08T15:37:00Z",
2.3
],
[
3,
"a44",
false,
"2021-12-08T15:37:00Z",
6.5
]
]
}
]
}
Output
Output is Base64 encoded Excel file in xlsx format which contains data.
Data (ver. 1.0.0)
| Column | Data type | Allow null | Description |
|---|---|---|---|
| Data | Base64 | No | Binary data encoded into Base64 string. |
Example output data
Data: Base64 |
|---|
| ewogICAgIlN1Y2Nlc3MiOnRydWUKfQ== |
Release notes
3.0.4
- Plugin binaries update as a result of included connector change
3.0.3
- Fixed shared nuget package versions.
3.0.2
- Fixed right processing of nullable properties.