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:


InputThis Snap has exactly one document input view, where it gets the document data to be formatted.
OutputThis Snap has exactly one binary output view, where it provides the Excel binary data stream.
ErrorThis 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

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