Marshal

The Marshal EIP converts the internal format (objects or structures) into an external format (such as JSON or XML) for communication or storage. For example, it can transform a Java object into a JSON or XML representation.

Difference between Marshal and Unmarshal

Marshal and Unmarshal are fundamental processes for data manipulation, enabling interoperability between systems.

Marshal: this is the process of transforming data from Camel’s internal format into a standardized external format, such as JSON, XML, CSV, or others. This conversion is essential to integrate the flow with external systems, send messages to APIs, or store information in files in a structured way.
Unmarshal: this refers to the reverse process, where data received in an external format (e.g., JSON, XML, CSV) is converted into the internal format used by Camel. This step is necessary for messages to be processed, handled, or routed within Camel’s integration flow.

Custom

In the list of available data format types in Data Format Type, there is the Custom option.

With it, you can use Excel as a reference, allowing the integration flow to process files in .xls and .xlsx formats.

This data format will convert the rows of the specified spreadsheet into an array of JSON objects. The JSON object generated for each cell will contain, in addition to the value, metadata about the data type.

Spreadsheet visual styles (cell color, font color, bold, borders, and others) are not imported.

To use the component, consider these guidelines:

The setHeader SensediaExcelFormat indicates whether the file is .xls or .xlsx.
- If the value provided is different, the component will throw an error.
- If the setHeader is not sent, .xlsx will be assumed by default.
The setHeader SensediaExcelSheetNames provides a list of sheet names, separated by commas.
- Only the sheets listed in the setHeader will be processed.
- If any listed sheet does not exist in the file, it will be ignored.
If the setHeader SensediaExcelSheetNames exists but none of the names match a sheet in the file, the component will throw an error.
The use of formulas is allowed. However, if the formula is incorrect, the cell in Excel will indicate an error.

JSON Format

After converting an Excel file to JSON, the data is organized as shown in the JSON Schema below:

{
  "type": "object",
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "Excel spreadsheet data schema",
  "description": "Schema for a data structure representing one or more spreadsheets with rows and cells.",
  "properties": {
    "sheets": {
      "description": "An array containing data for each sheet",
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "name": {
            "description": "The name of the sheet.",
            "type": "string"
          },
          "rows": {
            "description": "The rows within a single sheet",
            "type": "array",
            "items": {
              "type": "object",
              "properties": {
                "cells": {
                  "description": "The rows within a single row",
                  "type": "array",
                  "items": {
                    "type": "object",
                    "properties": {
                      "v": {
                        "description": "The value of the cell. Can be a string, a number, or a boolean.",
                        "oneOf": [
                          { "type": "string" },
                          { "type": "number" },
                          { "type": "boolean" }
                        ]
                      },
                      "addr": {
                        "description": "The address of the cell (e.g., 'A1').",
                        "type": "string"
                      },
                      "type": {
                        "description": "The data type of the cell.",
                        "type": "string",
                        "enum": [
                          "str",
                          "num",
                          "bool",
                          "blank",
                          "formula"
                        ],
                        "default": "str"
                      },
                      "fmt": {
                        "description": "The formatting string for the cell's value.",
                        "type": "string",
                        "default": "General"
                      }
                    },
                    "required": [
                      "addr"
                    ],
                    "additionalProperties": false
                  }
                }
              },
              "required": [
                "cells"
              ],
              "additionalProperties": false
            }
          }
        },
        "required": [
          "name",
          "rows"
        ],
        "additionalProperties": false
      }
    }
  },
  "required": [
    "sheets"
  ],
  "additionalProperties": false
}

Excel vs. JSON

Now, compare a spreadsheet with its version in JSON format:

Excel

planilha excel borda

JSON

{
  "sheets": [
    {
      "name": "Sheet1",
      "rows": [
        {
          "cells": [
            {
              "v": "Produto",
              "addr": "A1"
            },
            {
              "v": "Preço Unitário",
              "addr": "B1"
            },
            {
              "v": "Data/hora",
              "addr": "C1"
            },
            {
              "v": "Ativo",
              "addr": "D1"
            },
            {
              "v": "Quantidade",
              "addr": "E1"
            },
            {
              "v": "Desconto (%)",
              "addr": "F1"
            },
            {
              "v": "Preço Total",
              "addr": "G1"
            }
          ]
        },
        {
          "cells": [
            {
              "v": "Caneta",
              "addr": "A2"
            },
            {
              "type": "num",
              "v": 2.5,
              "addr": "B2"
            },
            {
              "type": "num",
              "v": 45915.4131373611,
              "addr": "C2",
              "fmt": "m/d/yyyy\\ h:mm:ss"
            },
            {
              "type": "bool",
              "v": true,
              "addr": "D2",
              "fmt": "\"TRUE\";\"TRUE\";\"FALSE\""
            },
            {
              "type": "num",
              "v": 10,
              "addr": "E2"
            },
            {
              "type": "num",
              "v": 0,
              "addr": "F2"
            },
            {
              "type": "num",
              "v": 25,
              "addr": "G2"
            }
          ]
        },
        {
          "cells": [
            {
              "v": "Caderno",
              "addr": "A3"
            },
            {
              "type": "num",
              "v": 15,
              "addr": "B3"
            },
            {
              "type": "num",
              "v": 45915.4131373611,
              "addr": "C3",
              "fmt": "m/d/yyyy\\ h:mm:ss"
            },
            {
              "type": "bool",
              "v": true,
              "addr": "D3",
              "fmt": "\"TRUE\";\"TRUE\";\"FALSE\""
            },
            {
              "type": "num",
              "v": 5,
              "addr": "E3"
            },
            {
              "type": "num",
              "v": 10,
              "addr": "F3"
            },
            {
              "type": "num",
              "v": 67.5,
              "addr": "G3"
            }
          ]
        },
        {
          "cells": [
            {
              "v": "Lápis",
              "addr": "A4"
            },
            {
              "type": "num",
              "v": 1.5,
              "addr": "B4"
            },
            {
              "type": "num",
              "v": 45915.4131373611,
              "addr": "C4",
              "fmt": "m/d/yyyy\\ h:mm:ss"
            },
            {
              "type": "bool",
              "v": true,
              "addr": "D4",
              "fmt": "\"TRUE\";\"TRUE\";\"FALSE\""
            },
            {
              "type": "num",
              "v": 20,
              "addr": "E4"
            },
            {
              "type": "num",
              "v": 5,
              "addr": "F4"
            },
            {
              "type": "num",
              "v": 28.5,
              "addr": "G4"
            }
          ]
        },
        {
          "cells": [
            {
              "v": "Borracha",
              "addr": "A5"
            },
            {
              "type": "num",
              "v": 3,
              "addr": "B5"
            },
            {
              "type": "num",
              "v": 45915.4131373611,
              "addr": "C5",
              "fmt": "m/d/yyyy\\ h:mm:ss"
            },
            {
              "type": "bool",
              "v": true,
              "addr": "D5",
              "fmt": "\"TRUE\";\"TRUE\";\"FALSE\""
            },
            {
              "type": "num",
              "v": 8,
              "addr": "E5"
            },
            {
              "type": "num",
              "v": 0,
              "addr": "F5"
            },
            {
              "type": "num",
              "v": 24,
              "addr": "G5"
            }
          ]
        },
        {
          "cells": [
            {
              "v": "Régua",
              "addr": "A6"
            },
            {
              "type": "num",
              "v": 4.5,
              "addr": "B6"
            },
            {
              "type": "num",
              "v": 45915.4131373611,
              "addr": "C6",
              "fmt": "m/d/yyyy\\ h:mm:ss"
            },
            {
              "type": "bool",
              "v": false,
              "addr": "D6",
              "fmt": "\"TRUE\";\"TRUE\";\"FALSE\""
            },
            {
              "type": "num",
              "v": 3,
              "addr": "E6"
            },
            {
              "type": "num",
              "v": 15,
              "addr": "F6"
            },
            {
              "type": "num",
              "v": 11.475,
              "addr": "G6"
            }
          ]
        },
        {
          "cells": [
            {
              "v": "Total Geral",
              "addr": "A7"
            },
            {
              "type": "num",
              "v": 156.475,
              "addr": "G7"
            }
          ]
        },
        {
          "cells": [
            {
              "v": "Média Preço Unitário",
              "addr": "A8"
            },
            {
              "type": "num",
              "v": 5.3,
              "addr": "G8"
            }
          ]
        }
      ]
    }
  ]
}

Code example

This flow exposes a REST endpoint (GET /teste-excel) that, when called, downloads an Excel file from an SFTP server, converts its content to JSON, and returns the result.

The processing occurs as follows:

Receives the REST request.
Logs the start of the file download via SFTP.
Downloads the file from the SFTP server.
Logs the completion of the download and the conversion to JSON.
Converts the content to byte[].
Defines in the setHeader the sheets to be processed (test1,test2).
Uses marshal with the excel (custom) data format to transform the file into JSON.
Defines the Content-Type setHeader to application/json for the response.

- from:
    uri: rest:get:/teste-excel
    steps:
      - log:
          message: "Iniciando o download do arquivo via SFTP."
      - pollEnrich:
          expression:
            simple: "sftp:eu-central-1.sftpcloud.io:22/download?username={{user}}&password={{pass}}&passiveMode=true&delete=false&fileName={{file}}&binary=true&knownHostsFile=/dev/null&strictHostKeyChecking=no"
      - log:
          message: "Download concluído. Convertendo para JSON."
      - convertBodyTo:
          type: byte[]
      - setHeader:
          name: SensediaExcelSheetNames
          expression:
            constant:
              expression: test1,test2
      - marshal:
          custom:
            ref: excel
      - setHeader:
          name: Content-Type
          constant: application/json

Excel can also be used in flows with the EIP unmarshal(to transform JSON files into Excel).

In this case, the addr field in the input JSON will not be considered.
Rows and columns will be built based solely on the order of rows and cells.
The result of the unmarshal will be a byte[].

Examples

See examples of other formats:

CSV
- The message will be converted to csv.
- No additional parameters are required.

- marshal:
    csv: {}

JSON
- The message will be converted to json.
- The Jackson library will be used to serialize the message body.

- marshal:
    json:
      library: Jackson

XML
- The message will be converted to XML.
- The jacksonXml format uses the Jackson library, but there is also a generic xml format.

- marshal:
    jacksonXml: {}

- marshal:
    xml: {}

See how to add a data format directly through the EIP form on the Diagram tab:

marshal json

When selecting the JSON format, the Library field is automatically filled with the default value "Jackson". Since it is the default, it is not displayed in the flow script in the Source tab, appearing only if changed to a different value.

Thanks for your feedback!

EDIT

Share your suggestions with us!
Click here and then [+ Submit idea]