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.

---

Was this page helpful?

×

Yes No

What is the reason for your feedback?

Tell us more:

Submit Privacy Policy