Excel Formatter

Overview

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


Overview

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

Snap views

View Description Examples of upstream and downstream Snaps
Input This Snap has exactly one document input view, where it gets the document data to be formatted. File Reader
Output This Snap has exactly one binary output view, where it provides the Excel binary data stream. Mapper
Error

Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the pipeline by choosing one of the following options from the When errors occur list under the Views tab. The available options are:

  • Stop Pipeline Execution Stops the current pipeline execution when an error occurs.
  • Discard Error Data and Continue Ignores the error, discards that record, and continues with the remaining records.
  • Route Error Data to Error View Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Snap settings

Legend:
  • Expression icon (): Allows using pipeline parameters to set field values dynamically (if enabled). SnapLogic Expressions are not supported. If disabled, you can provide a static value.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons in the Snap settings dialog.
Field / Field set Type Description
Label String

Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline.

Default value: Excel Formatter

Example: Excel Format
Sheet name String/Expression Enter the name of the Excel Sheet

Default value: None

Example: Members
Write CSV header Checkbox This option specifies whether or not the Snap should write the header to the output.

Default status: deselected
Ignore empty stream Checkbox 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 status: deselected
Convert numeric strings to numbers Checkbox 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 status: Selected
Translate date and time types Checkbox 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 status: Deselected
Convert formula strings to formulas Checkbox 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 status: Deselected

Warning: 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 Checkbox 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 status: Deselected
Snap execution Dropdown list
Choose one of the three modes in which the Snap executes. Available options are:
  • 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: Validate and Execute

Example: Execute only

Examples

Update headers including all the values

Writing to Multiple Excel Sheets in the Output