Excel To Json Converter
[ | version 3.1]
Connector
The connector is permitted for use of the platform background agent.
Connector processing type: Row by row!
This connector allows to create Json from Excel file in xlsx format. Json can be processed and than imported to Json to Excel connector from version 1.1.0 to create back Excel file.
Input Excel
Sheet: First sheet
| ID | Name | Created | IsActive | Score | Formula |
|---|---|---|---|---|---|
| 1 | Jan | 12/08/2020 | true | 2.2 | =A2+E2 (3.3) |
| 2 | Petr | 12/08/2021 | false | 1.5 | =A3+E3 (3.5) |
JSON structure
Example json structure created by this connector with one sheet named First sheet. Table header will be displayed on first line. It contains columns ID (double), Name (string), Created (double), IsActive (bool), Score (double) and Formula (formula).
{
"Sheets": [
{
"Name": "First sheet",
"DisplayHeader": true,
"Columns": [
{
"Name": "ID",
"DataType": "double",
"Format": "General"
},
{
"Name": "Name",
"DataType": "string",
"Format": "General"
},
{
"Name": "Created",
"DataType": "double",
"Format": "MM/dd/yyyy"
},
{
"Name": "IsActive",
"DataType": "bool",
"Format": "General"
},
{
"Name": "Score",
"DataType": "double",
"Format": "General"
},
{
"Name": "Formula",
"DataType": "formula",
"Format": "General"
}
],
"Rows": [
[
1,
"Jan",
44538.6506944444,
true,
3.7,
"A2+E2"
],
[
2,
"Petr",
44538.6506944444,
false,
1.23,
"A3+E3"
]
]
}
]
}
Data Types
Data types in json file:
stringbool- boolean value will be written asTRUE/FALSEdouble- dot as decimal separator (3.7) but internally is also used for integers and datetime (OLE Automation date)formula- string value of formula (A2+C2)
Restrictions
Sheets in excel must contain on the first cell header and in each column must be at least one cell to identify datatype. If no value is in column than default string is used.
Input & Output Schema
Input
Input 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 input data
Base64 encoded content of Excel file.
Data: Base64 |
|---|
| ewogICAgIlN1Y2Nlc3MiOnRydWUKfQ== |
Output
Output 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
Example json structure
{
"Sheets": [
{
"Name": "Sheet 1",
"DisplayHeader": true,
"Columns": [
{
"Name": "Integer",
"DataType": "double",
"Format": null
},
{
"Name": "String",
"DataType": "string",
"Format": null
},
{
"Name": "Bool",
"DataType": "bool",
"Format": null
},
{
"Name": "Datetime",
"DataType": "double",
"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
]
]
}
]
}
Release notes
3.1.4
- Plugin binaries update as a result of included connector change
3.1.3
- Fixed shared nuget package versions.
3.1.2
- Fixed right processing of nullable properties.