PhixFlow Help

File Exporter Excel Multi-input

Owned by Gary Parden
Last updated: Apr 23, 2019 by Fiona Sargeant (Unlicensed)Version comment
6 min read

Overview

When a File Exporter's Type is set to Excel Spreadsheet (Multi Input), it uses a template file to guide which values from which input Pipes are written to which cells of cell ranges in the template file.

The Excel Template (Expr) is an expression, and  is re-evaluated inside the Input Multiplier loop - it is possible for successive evaluations of the Template Expression to give different results. This is ok provided each result is a valid Template file.

Each pass of the input multiplier loop generates a new output file.

Sections on this page

Excel Template

Any cell in the template may be set to a template instruction.

A template instruction looks like:

{keyword: value, keyword: value, ...}

Valid keywords are:

Keyword
Value
Meaning
sourcepipe

This marks the top left cell of a multi-row region of cells to be overwritten by multiple rows read from the nominated pipe.

The source keyword always occupies the top left cell of the region.

If there is a corresponding column:last to the right on the same row, that marks the right-most column of the region. If not, the region extends to the last cell on the row or the last cell before the start of another region.

If there is a corresponding row:last below on the same column, that marks the last row of the region. If not, the region extends to the last row on the worksheet.

valuepipe.attribute

The value of this cell should be set to the value of the indicated pipe attribute.  We don't need to specify the 'pipe.' part of the value entry where this is inside a defined region since the pipe is already defined at the start of the region.  

We only need to specify the pipe when this is a standalone value - this is effectively then a one column, one row region.

Note that this is currently NOT a JEP expression, though I wouldn't rule it out in future.

rowlast

This is the last row of a region.

Each row:last must be matched to a source above it in the same column.

columnlast

This is the last column of a region.

Each column:last must be matched to a source to the left of it in the same row.

excludepipe.attribute | true | falseThis keyword is used to exclude (delete) a tab from the output excel file. Whether or not the tab is deleted is determined by the evaluation of the referenced value (true deletes, false does not).

Where a cell is not formatted as text, it will be necessary to prefix-quote the contents in order to store a text annotation.

Definitions

Multi-row Source

A multi-row source means a pipe that will be used to return zero or more records.

Any value reference to an attribute of a Multi-row Source returns the value of that attribute on the current row, or null if there are no more records. The current row is advanced once for each row in the Multi-cell Region from which it is referenced.

Single-row Source

A single-row source means a pipe that will return zero or one records.

Any value reference to an attribute of a Single-row Source returns the value of that attribute on the first row read, or null (i.e. it leave the cell blank) if no rows were read.

Multi-row Region

A multi-row region is a rectangular group of cells whose contents will be overwritten by multiple records read from a pipe.

A multi-row region is defined by a source directive in its top-left cell, along with various row and column directives which determine the right and bottom boundaries.

The row and column directives allow for a region to be a single row/column, a specific number of rows/columns, or to extend to the end of the row/column.

The source directive defines the source (pipe) to be a Multi-row Source, and defines the Multi-row Source as the source of records for the region.

When data is written using the template:

One row of the region will be populated for each record read from the source pipe.

The value to be written into each populated cell is determined by the value directive in the corresponding cell in the first row of the region.

A value directive can refer to any attribute of the Multi-row Source that is linked to the region.

Where the region contains more rows than the source, the remaining rows will be set to blank or zero (depending on whether they are string or number cells).

Where the source contains more rows than the region, additional rows will be inserted below the region using the type, format and other properties of the last row of the region.

It is an error for a Multi-cell Region to overlap any other Multi-cell Region.

It is an error for a row to contain more than one Multi-cell Region.

Single-cell region

A single-cell region is defined by a cell that is outside any multi-row region and contains a value directive.

The value directive can refer to any single-row source.

When data is written using the template:

The value written is the value of the nominated attribute of the first row returned by the single-row source.

If there is no value to be found in the attribute, a blank cell will be written.

Example

Template

This Template assumes that the file exporter has 2 input Pipes, Pipe p2, with attributes abc and def, and Pipe p3, with attributes a4, a5 and a6.

Cells are coloured green for single-cell regions and pink for multi-row regions - this is purely to make it easier to visualise how the template is organised; the colouring is not part of the region definitions.


A
B
C
D
E
F
G
1

{exclude:false}




2
{value:p3.a4}




3


{value:p3.a5}


4

{source:p2,
value:p2.abc}		{value:p2.def}text, number or expression

{value:p3.a6,

column:last}


5






6
something{row:last}



7






Notes

Cell(s)
Description
B1This is an exclude tab, with value of false. This tab will NOT be deleted from the output file.
B2

This is a single-cell region, and will be overwritten with the value of attribute a4 from the first record read from p3 (or with a blank space if the value is null or empty).

D3

This is a single-cell region, and will be overwritten with the value of attribute a5 from the first record read from p3 (or with a blank space if the value is null or empty).

C4

This is the top left cell of a multi-cell region, and will be overwritten by multiple rows read from pipe p2.

This cell should be set to the value of attribute abc from p2.

All cells in following rows down to the row tagged as '{row: last} should be overwritten by successive read from p2.

F4

This is the top right cell of the region starting at C4 (the column:last notation denotes this.

This cell, and the cells below (one for each row read from p2) should be set to the value of attribute a6 from the first record read from p3.

C6

This is the bottom left cell of the region starting at C4.

This cell should be set to attribute abc from the corresponding row read from p2, or null of there are too few rows.

D4

This cell should be set to the value of attribute def from p2.

All cells in following rows down to the row tagged as '{row: last} will be overwritten by successive rows read from p2.

E4

This cell has no instruction, so whatever is in this cell (blank, text, number, expression), will be copied down into newly inserted rows.

Result

This result Excel spreadsheet assumes that 5 records read from p2.

Where p2(row3).def means the value of attribute def on the 3rd row read from pipe p2.


A
B
C
D
E
F
G
1
{exclude: false}




2
p3(row1).a4




3


p3(row1).a4


4

p2(row1).abcp2(row1).deftext, number or expressionp3(row1).a6
5

p2(row2).abcp2(row2).deftext, number or expressionp3(row1).a6
6
somethingp2(row3).abcp2(row3).deftext, number or expressionp3(row1).a6
7blankblankp2(row4).abcp2(row4).deftext, number or expressionp3(row1).a6blank
8blankblankp2(row5).abcp2(row5).deftext, number or expressionp3(row1).a6blank
9(was A7)(was B7)(was C7)(was D7)(was E7)(was F7)(was G7)

Notes

Evaluated Cell(s)
Meaning
B2A non-executed exclude tab remains. You could set this cell (either it's row or column) as hidden if you don't wish to see/print it.
E4:E8These cells are copied down from E4 because there was no value instruction in the Template in E4.
Rows 7:8

Because there were 2 more rows in p2 than in the Multi-cell Region (starting at) C4, 2 rows were inserted below row 6, each populated by copying row 6 then overwriting the new cells with values from the multi-row source.

Each inserted row copies the format of the row above.

A7:B8
G7:G8

These cells have been inserted, and have no counterpart in the Template, so are set to blank.
A9Cells are marked to show their original positions and contents, so A9 contains the value from A7 in the Template.

Please let us know if we could improve this page feedback@phixflow.com