Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

In  this article

Table of Contents
maxLevel2
excludeOlder Versions|Additional Resources|Related Links|Related Information

Overview

Excel Multi Sheet Formatter is a Format-type Snap that reads the documents from multiple input views, formats the data to XLSX format and converts them into specified sheet for each input view and writes out binary data.

Prerequisites

None.

Support for Ultra Pipelines

Does not work in Ultra Pipelines.

Limitations and Known Issues

None.

Snap Input and Output

Input/OutputType of ViewNumber of ViewsExamples of Upstream and Downstream SnapsDescription
InputDocument
Min: 1
Max: 
  • Mapper
  • Structure
  • JSON Generator 
Document data to be formatted in to excel format. 
OutputBinary
Min: 1
Max: 1
  • File Writer
  • JSON Parser
This Snap provides the result of the formatted excel content in the binary output view.


Snap Settings

Field

Field Type

Description


Label*


String

Specify a unique 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.

Default Value: Excel Multi Sheet Formatter
ExampleExcel Multi Sheet Formatter

Sheet mapping


Use this field set to define the mapping of input view to excel sheet name. This field set contains the following fields:

  • Input view name
  • Sheet name
Input view nameString

Select the name of the input view mapped to the sheet.

Default Valueinput0
Exampleinput1

Sheet nameString/Expression

Specify the name of the Excel sheet to map the input view data.
This property accepts expressions to allow Pipeline parameters only.

Default Value: [None]
Example: Members

Write header*Checkbox

Select this checkbox to enable the Snap to write the header to the output.

Default Value: Selected

Ignore empty streamCheckbox

Select this checkbox to write nothing to the output view in case no documents were received on the input view.

If you deselect this checkbox, an empty array is written to the output view in case no documents were received on the input view.

Default Value: Not selected

Convert numeric strings to numbersCheckbox

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

Select this checkbox to enable the Snap to convert the value to a numeric-type cell.

When you deselect this checkbox, the Snap sets the value to a string-type cell.

Default ValueSelected

Translate date and time typesCheckbox

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

Select this checkbox to enable the Snap to convert the date and time type values to Excel date numeric type with date format style.

Default ValueNot selected

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 valueNot selected.

Note

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.


Snap Execution


Dropdown list
Multiexcerpt include macro
nameSnap_Execution_Introduced
pageAnaplan Read

Example


Reading

csv

CSV Files and Providing Input Data to the Excel Multi Sheet Formatter Snap

The following image shows a sample pipeline that reads two csv CSV files and providing the document data as input to Excel Multi Sheet Formatter Snap. 

The following image displays the Excel Multi Sheet Formatter Snap properties, where input0 maps to a sheet named sheet1 and input1 maps to sheet2

Example showing input mapping


Insert excerpt
Transform Snap Pack
Transform Snap Pack
nopaneltrue