Excel Template

Owned by Gary Parden
Last updated: Aug 31, 2022 by Fiona Sargeant (Unlicensed)
6 min read

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:

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

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

KeywordValueMeaning
sourcepipe

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.

valuepipe.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.

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

TermDefinition
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.

Sections on this page


Learn More

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


Terminology changes in progress

As part of the redesign of PhixFlow, we are changing the following terms:

dashboard → screen   
stream → table
stream attributes → attributes
stream item → record
stream set → recordset
stream view → view
stream item action → record-action 
stream action → table-action
driver class → database driver

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.


ABCDEFG
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

CellDescription
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.

E4This 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).

ABCDEFG
1
{exclude:false}




2
p3(row1).a4




3


p3(row1).a4


4

p2(row1).abcp2(row1).deftext, number or expression

p3(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

blankblankp2(row5).abcp2(row5).deftext, number or expressionp3(row1).a6blank

(was A7)(was B7)(was C7)(was D7)(was E7)(was F7)(was G7)

Notes

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