Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Tellus at urna condimentum mattis pellentesque. In aliquam sem fringilla ut morbi tincidunt. Ac felis donec et odio pellentesque diam volutpat commodo. Pharetra diam sit amet nisl suscipit adipiscing bibendum est ultricies. Neque egestas congue quisque egestas. Id aliquet lectus proin nibh nisl condimentum id venenatis a. Urna duis convallis convallis tellus. Quam vulputate dignissim suspendisse in est ante in nibh. Elit eget gravida cum sociis natoque penatibus. Tellus orci ac auctor augue mauris augue neque gravida in.

SIDE PANEL

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Tellus at urna condimentum mattis pellentesque. In aliquam sem fringilla ut morbi tincidunt. Ac felis donec et odio pellentesque diam volutpat commodo. Pharetra diam sit amet nisl suscipit adipiscing bibendum est ultricies. Neque egestas congue quisque egestas. Id aliquet lectus proin nibh nisl condimentum id venenatis a. Urna duis convallis convallis tellus. Quam vulputate dignissim suspendisse in est ante in nibh. Elit eget gravida cum sociis natoque penatibus. Tellus orci ac auctor augue mauris augue neque gravida in.

Diam vel quam elementum pulvinar etiam non quam. Nisl nunc mi ipsum faucibus vitae aliquet nec ullamcorper. Praesent tristique magna sit amet purus gravida quis. Commodo quis imperdiet massa tincidunt nunc pulvinar sapien et ligula. Nisl rhoncus mattis rhoncus urna neque viverra justo nec. Feugiat pretium nibh ipsum consequat nisl vel pretium lectus quam. Nunc eget lorem dolor sed. Dolor sed viverra ipsum nunc aliquet bibendum enim facilisis gravida. Posuere sollicitudin aliquam ultrices sagittis. Consectetur adipiscing elit duis tristique sollicitudin nibh sit amet. Arcu ac tortor dignissim convallis aenean et tortor. Nibh cras pulvinar mattis nunc sed blandit libero. Pellentesque habitant morbi tristique senectus et netus. Gravida quis blandit turpis cursus in. Consequat interdum varius sit amet. Velit ut tortor pretium viverra suspendisse potenti. Sit amet nulla facilisi morbi tempus iaculis urna id volutpat.

Et magnis dis parturient montes nascetur. Turpis cursus in hac habitasse platea dictumst quisque sagittis purus. Elit pellentesque habitant morbi tristique senectus. Sed viverra ipsum nunc aliquet bibendum enim. At lectus urna duis convallis. Turpis egestas sed tempus urna et. Urna id volutpat lacus laoreet non curabitur gravida arcu. Tristique senectus et netus et malesuada fames ac. Viverra accumsan in nisl nisi scelerisque eu ultrices vitae auctor. Tincidunt augue interdum velit euismod. Sit amet consectetur adipiscing elit pellentesque habitant. Nibh nisl condimentum id venenatis. Enim praesent elementum facilisis leo vel fringilla. Suspendisse potenti nullam ac tortor vitae. Gravida rutrum quisque non tellus orci ac auctor augue. Suscipit tellus mauris a diam. Elementum tempus egestas sed sed risus pretium quam. Sed elementum tempus egestas sed sed. Malesuada fames ac turpis egestas sed tempus urna et.

Vitae tortor condimentum lacinia quis vel. Tempus urna et pharetra pharetra massa massa ultricies mi quis. Suspendisse potenti nullam ac tortor vitae purus faucibus ornare. Ornare arcu odio ut sem nulla pharetra. Duis at tellus at urna condimentum mattis. Id semper risus in hendrerit gravida. Gravida arcu ac tortor dignissim convallis aenean et tortor. Augue interdum velit euismod in pellentesque massa. Vivamus arcu felis bibendum ut. Odio morbi quis commodo odio aenean sed.

Pharetra pharetra massa massa ultricies mi quis. Massa sapien faucibus et molestie. Ut eu sem integer vitae justo eget magna. Risus viverra adipiscing at in tellus integer feugiat scelerisque varius. Diam volutpat commodo sed egestas. Donec enim diam vulputate ut pharetra sit amet aliquam. Convallis tellus id interdum velit laoreet id donec ultrices tincidunt. Viverra vitae congue eu consequat ac felis donec. Lorem ipsum dolor sit amet consectetur. Vel elit scelerisque mauris pellentesque pulvinar pellentesque. Ultrices in iaculis nunc sed augue lacus viverra. Pharetra massa massa ultricies mi quis hendrerit dolor magna eget. Eget nullam non nisi est sit amet. Pharetra et ultrices neque ornare aenean euismod elementum nisi

Insert excerpt

	_Banners
	_Banners
name	analysis
nopanel	true

This page is for data modellers who want to use a file exporter to export data to an Excel spreadsheet with a specific layout.

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.

Using an Excel Template

Specify an Excel Sheet

You can specify an Excel sheet name as a template instruction. PhixFlow reads an attribute from a pipe to construct the sheet name:

Code Block
{pipe_name.attribute}

Where:

pipe-name specifies the pipe
attribute specifies the attribute.

For example, to use a value from the attribute called code, which is pulled from the pipe called "in", specify:

Code Block
{in.Code}

Use Cells as Template Instructions

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

A template instruction looks like this:

Code Block
{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 using `pipe.attribute`. This represents either a one-column, one-row region or a value defined inside a region.
`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.

Tip
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 it 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. A multi-row region can contain references to other pipes than the source for the region. References to: the source pipe will read each record in turn from the pipe other pipes will behave as superimposed single cells, and will only ever return values from the first record read from the pipe. 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.

Live Search
spaceKey @self
additional none
placeholder Search all help pages
type page

Panel

borderColor	#00374F
titleColor	white
titleBGColor	#00374F
borderStyle	solid
title	Sections on this page

Table of Contents

maxLevel	3
indent	12px
style	none

Learn More

For links to all pages in this topic, see Analysis Models for Batch Processing Data.

Insert excerpt

	_terms_changing
	_terms_changing
nopanel	true

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	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
	blank	blank	p2(row5).abc	p2(row5).def	text, number or expression	p3(row1).a6	blank
	(was A7)	(was B7)	(was C7)	(was D7)	(was E7)	(was F7)	(was G7)

Notes

Evaluated Cell	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.

Versions Compared

Old Version 45

New Version Current

Key

Overview

Using an Excel Template

Specify an Excel Sheet

Use Cells as Template Instructions

Definitions

Live Search
spaceKey @self
additional none
placeholder Search all help pages
type page

Example

Template

Result

Page Comparison

Versions Compared

Old Version 45

New Version Current

Key

Overview

Using an Excel Template

Specify an Excel Sheet

Use Cells as Template Instructions

Definitions

Live SearchspaceKey@selfadditionalnoneplaceholderSearch all help pagestypepage

Example

Template

Result

Live Search
spaceKey @self
additional none
placeholder Search all help pages
type page