File Exporter Excel Multi-input

This page is for data modellers who need to export to data to an Excel spreadsheet. It explains how to create an Excel Template expression, and an Excel template to format the data that is exported.

Overview

When you are setting the properties of a file exporter, setting the Type property to Excel Spreadsheet (Multi Input) adds an extra field, Excel Template (Expr) to the property tab; see File Exporter.

Use this option to add an expression that resolves to the name of a template file, which must exist. PhixFlow uses a template file to guide which values from which input pipes are written to which cells or cell ranges in the template file.

The Excel Template (Expr) is re-evaluated inside the input multiplier loop. This means it is possible for successive evaluations of the expression to give different values. Each value represents the name of a template file, which must exist.

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 this:

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

Valid keywords are:

Keyword	Value	Meaning
`source`	`pipe`	The `source` keyword is the top left cell of a region in an Excel spreadsheet. The region comprises multiple rows that will be overwritten by the rows read from the pipe. 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.
`value`	`pipe.attribute`	The value of this cell should be set to the value of the indicated pipe attribute. Specify `pipe.attribute` for a standalone value. This represents a one-column, one-row region. If the value is inside a defined region, the pipe is already defined at the start of the region. In this case, specify `attribute` only.
`row`	`last`	This is the last row of a region. Each `row:last` must be matched to a source above it in the same column.
`column`	`last`	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.
`exclude`	`pipe.attribute \| true \| false`	Use this keyword to delete a worksheet, using the exported data. `true`: delete the worksheet from the output Excel file if the attribute is exported to the worksheet. `false`: keep the worksheet in the output Excel file if the attribute is exported to the worksheet. This is the default when no keyword is set.

Where a cell is not formatted as text, prefix-quote the contents to store a text annotation.

Definitions

Term	Definition
multi-row source	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 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 rectangular group of cells whose contents will be overwritten by multiple records read from a pipe. A region can be a single row/column a specific number of rows/columns extend to the end of the row/column. A row in the Excel spreadsheet can contain only one multi-cell region. A multi-row region cannot overlap any other multi-cell region. PhixFlow will report an error if either of these arrangements arise. The region is defined by: a `source` keyword in its top-left cell a `row` keyword, to specify the bottom boundary a `column` keyword ti specify the right boundary. The source keyword indicates the pipe has multiple rows of data, so is a multi-row source is the origin or source of records to populate the region in the Excel spreadsheet. When PhixFlow writes data into Excel 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. The `value` keyword 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.
single-cell region	A single-cell region is defined by a cell that is outside any multi-row region and contains a `value` keyword that can refer to any single-row source. When data is written using the template: PhixFlow writes the value of the nominated attribute in the first row returned by the single-row source. If there is no value to be found in the attribute, PhixFlow writes a blank cell.

Example

Template

This template assumes that the file exporter has 2 input pipes:

pipe p2, with attributes abc and def
pipe p3, with attributes a4, a5 and a6.

In the following diagrams, cells are coloured green for single-cell regions and pink for multi-row regions to show how the template is organised.

	B	C	D	E	F
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
B1	This 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 Excel spreadsheet that PhixFlow creates using the spreadsheet assumes that 5 records have been read from the pipe p2.

Annotations in the form p2(row3).def mean:

data read from pipe p2
includes the value of the attribute def
on the 3rd row - (row3).

	A	B	C	D	E	F	G
1		{exclude: false}
2		p3(row1).a4
3				p3(row1).a4
4			p2(row1).abc	p2(row1).def	text, number or expression	p3(row1).a6
5			p2(row2).abc	p2(row2).def	text, number or expression	p3(row1).a6
6		something	p2(row3).abc	p2(row3).def	text, number or expression	p3(row1).a6
7	blank	blank	p2(row4).abc	p2(row4).def	text, number or expression	p3(row1).a6	blank
8	blank	blank	p2(row5).abc	p2(row5).def	text, number or expression	p3(row1).a6	blank
9	(was A7)	(was B7)	(was C7)	(was D7)	(was E7)	(was F7)	(was G7)

Notes

Evaluated Cell(s)	Meaning
B2	A 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:E8	These 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.
A9	Cells are marked to show their original positions and contents, so A9 contains the value from A7 in the template.