Skip to content
background-image background-image

json_to_excel_converter

JSON To Excel Converter

  • Version

    3.0

  • Agent restriction

    None

  • Processing type

    Row by row

  • Multi-input step

    Not Supported

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:

  • string
  • int - number type
  • datetime - display format can be specified in column definition, you can use placeholders m, d, y, h, s, AM/PM and separators -, :, /
    • MM/dd/yyyy - visible just date part
    • MM/dd/yyyy HH:mm:ss - whole datetime visible
    • HH:mm:ss - time part visible
    • h:mm:ss AM/PM - time part with
  • bool - boolean value will be written as TRUE/FALSE
  • double - 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

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/Output 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.