PhixFlow Help
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.
Excel Template Instructions
Specify an Excel Sheet
From 8.3.2 onward, you can specify an Excel sheet name as a template instruction. PhixFlow reads an attribute from a pipe to construct the sheet name:
{pipe_name:attribute}
Where:
pipe-name
specify the pipeattribute
specify the stream attribute
For example, to use a value from the stream attribute called code, which is pulled from the pipe called "in", specify:
{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:
{keyword: value, keyword: value, ...}
Valid keywords are:
Keyword | Value | Meaning |
---|---|---|
source | pipe | The If there is a corresponding If there is a corresponding |
value | pipe.attribute | The value of this cell should be set to the value of the indicated pipe attribute using |
row | last | This is the last row of a region. Each |
column | last | This is the last column of a region. Each |
exclude | pipe.attribute | true | false | Use this keyword to delete a worksheet, using the exported data.
|
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 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
|
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 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:
The source keyword indicates the pipe:
A multi-row region can contain references to other pipes than the source for the region. References to:
When PhixFlow writes data into Excel using the template:
|
single-cell region | A single-cell region is defined by a cell that is outside any multi-row region and contains a
|
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.
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 |
---|---|
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:
|
D3 | This is a single-cell region, and will be overwritten with
|
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 ' |
F4 | This is the top right cell of the region starting at C4. The 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 |
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 | 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. |
Please let us know if we could improve this page feedback@phixflow.com