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:
string
bool
- boolean value will be written asTRUE/FALSE
double
- 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/Output 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.