Excel Cell Formula Expander

This is used to create an instruction table for use by the "Write Excel Formula (multi-cell) component.


Takes a table consisting of

CellReference|Formula |FirstRow|LastRow|FontSize|Alignment|FontColour|BackColour|ColumnWidth|CellFormat|BoldItalic
D[#R] | B[#R]*C[#R] | 2 | 10 | 14 |LEFT |BLUE | WHITE | 100 |0,000.00|BI

and returns an expanded table of formula that applies to all the specified rows

e.g.
CellReference|Formula|FirstRow|LastRow|FontSize|Alignment|FontColour|BackColour|ColumnWidth|CellFormat|BoldItalic
D2 | B2*C2 | 2 | 10 | 14 |LEFT |BLUE | WHITE | 100 |0,000.00|BI
D3 | B3*C3 | 2 | 10 | 14 |LEFT |BLUE | WHITE | 100 |0,000.00|BI
D4 | B4*C4 | 2 | 10 | 14 |LEFT |BLUE | WHITE | 100 |0,000.00|BI
D5 | B5*C5 | 2 | 10 | 14 |LEFT |BLUE | WHITE | 100 |0,000.00|BI
and so on to
D10 | B10*C10 | 2 | 10 | 14 |LEFT |BLUE | WHITE | 100 |0,000.00|BI

The following have special meaning:

[#R] means "current row" and is used if FirstRow and LastRow are specified in the table. If they are, the table is expanded to include all the specified rows and #R is replaced on each row with the appropriate row number.
[#ROWSTART] or [#ROWFIRST] means "first data row"
[#ROWEND] or [#ROWLAST] means "last data row"

It will attempt to calculate anything written in square brackets using basic arithmetic. This means you can include offsets,
so [#ROWEND+2] mean 2 rows after the last data row


$columnname$ means "turn this into the Excel A-Z reference for the column with the given name"
Columns may also be referenced directly using Excel letter notation
In addition:
[#LASTCOL] will return the last column as an Excel column letter
[#NEXTCOL] will return the next column following the last column as an Excel column letter
It is also possible to do basic arithmetic on these column references
[#LASTCOL+5] will return the column this is 5 columns beyond the last column as an Excel column letter
[#NEXTCOL+1] will return the column following the "next column" as an Excel column letter

Colour/Color names are any named colour from the standard palette used by Excel (typically see html colour names.
Bold/Italic for a cell are set on if this entry contains a B and/or an I respectively

Alignment may be set to L, C, R or LEFT, CENTER, CENTRE, RIGHT

This can then be used as the input table to the

Write Excel Cell Formula (multi cell)

component

Input Ports

Icon
The data that was written to the Excel spreadsheet, by the Excel Writer
Icon
The formula/markup table that is to be expanded into an instruction table to be passed to the Write Excel Formula (multi cell) component.

Output Ports

Icon
No description for this port available.

Nodes

Extensions

Links