On this Page

Snap type:

Format

Description:

This Snap reads the documents, converts them into .XLSX format using a specified sheet and writes out binary data.

Prerequisites:

If you are using OpenJDK 11 in the Snaplex on which you want to execute Excel Formatter Snaps, you must install Fontconfig on the Snaplex.

if you execute a Pipeline on a Snaplex that does not have Fontconfig installed, you may see the following null pointer exception: java.desktop/sun.awt.FontConfiguration.getVersion(FontConfiguration.java:1262.

To install Fontconfig, run the following command:

On Ubuntu/Debian deb based systems:
apt install fontconfig
On Fedora/CentOS rpm based systems:
yum install fontconfig

Support and limitations: Does not work in Ultra Pipelines.

Known Issues:

None.

Account:

Accounts are not used with this Snap.

Views:

Input	This Snap has exactly one document input view, where it gets the document data to be formatted.
*Output*	This Snap has exactly one binary output view, where it provides the Excel binary data stream.
Error	This Snap has at most one document error view and produces zero or more documents in the view. Each document contains input data that has caused an error while processing.

Settings

Label

Required. The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.

Sheet name

The name of the Excel sheet.

Example: Members
Default value: {[None]

Write header

Required. This option specifies whether or not the Snap should write the header to the output.
Default value: Selected

Ignore empty stream

If the property is unchecked, an empty array will be written to the output view in case no documents were received on the input view. If the property is checked, then the Snap will write nothing to the output view in case no documents were received on the input view.

Default value: Not selected

Convert numeric strings to numbers

This property sets the behavior of the Snap when a value in the input document is a numeric string.

If selected, the Snap converts the value to a numeric-type cell.

If not selected, the Snap sets the value to a string-type cell.

Default value: Selected

Translate date and time types

This property sets the behavior of the Snap when a value in the input document is a date and/or datetime type.

If selected, the Snap does not convert the date and/or datetime types to text/string and instead converts it to Excel date numeric type with date format style.

Default value: Not selected

Convert formula strings to formulas

This field sets the behavior of the Snap when a value in the input document is a formula string.

Select this checkbox to write string values that begin with an equal sign (=) as an Excel formula. If you want a value that begins with an equal sign (=) to not be written as a formula with this option selected, then the value must begin with a single quote (').

Default value:Not selected.

If the Convert formula strings to formulas checkbox is selected, the Snap writes the formula, but will not evaluate it.

Before you pass a worksheet as an input to the Excel Parser Snap, open the file in any spreadsheet application (such as Microsoft Excel or LibreOffice Calc), select the option to execute the full workbook, save the file, and then use this saved file in the Excel Parser Snap. Otherwise, the Excel Parser Snap will display a blank value or 0.

Include all columns

The Snap uses the first input document to write the header. If subsequent input documents have additional fields, truncate rows or columns in between, they are not included in the output.

If this property is selected, the Snap detects those additional fields in all the input documents, updates the header and includes all values in the output.

If not selected, and the subsequent input document has any truncate values, an empty output data preview is displayed.

Default value: Not selected

Snap Execution

Select one of the following three modes in which the Snap executes:

Validate & Execute: Performs limited execution of the Snap, and generates a data preview during Pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during Pipeline runtime.
Execute only: Performs full execution of the Snap during Pipeline execution without generating preview data.
Disabled: Disables the Snap and all Snaps that are downstream from it.

Default Value: Execute only
Example: Validate & Execute

Example

Updating Excel Sheets with the Headers Including All the Values

In the below pipeline, the Excel Formatter Snap uses the setting Include all columns, which enables the Snap to update the Excel sheet with the headers including all the values:

Excel Formatter example with including all columns

Writing to Multiple Excel Sheets in the Output

This example demonstrates how you can use the Excel Formatter Snap to classify Excel data and route it to different worksheets in the output file based on the values in a specific field.

Download the Pipeline.

In the sample Pipeline, the JSON Generator Snap output contains records with two values for the country field, Canada and United States.

In the Excel Formatter Snap, if you select the value $country in the Sheet name field, the input data is organized into two sheets based on each unique value in the country field.

The Snap's output preview shows records for Canada grouped together in one sheet:

The final output shows an Excel file containing two worksheets, one for Canada and the other for United States.

Similarly, you can select any field in the Sheet name drop-down list to create an output file with separate sheets for each unique value of the field.

Downloads

Important steps to successfully reuse Pipelines

Download and import the pipeline into the SnapLogic application.
Configure Snap accounts as applicable.
Provide pipeline parameters as applicable.