Write Excel Cell Formula

Writes a simple formula into a named cell in an existing XLSX spreadsheet.

Note that it will not be able to write to a spreadsheet that is currently open in Excel. If this occurs, it may appear that no error has occurred, but nothing will be written.

Requires KNIME 4.7 or above

@takbb Brian Bates 2 June 2023

Options

Excel filename: Enter the full absolute path to the excel xlsx file
Sheet Name: Enter the name of the sheet to be updated in the XLSX file
Cell Reference: Enter the cell Reference in A1 format.%%00010%%00010To refer to Cell A3, you would simply write%%00010A3%%00010%%00010The value of a flow variable may also be included in the reference (see Formula field help for more detailed info)%%00010%%00010To refer to the cell in Column A that has a value of currentrow + 2, you could write the formula:%%00010%%00010A[$${Icurrentrow}$$+1]%%00010%%00010Note that there must be square brackets surrounding the calculation.%%00010%%00010It is not possible to refer to columns here directly by name as this component is not passed details of the actual data table.%%00010However,if you wish to include a variable column name, and have the required Excel column letter for a given column stored in a variable such as ProductColumnLetter, you could then include this in the cell reference as:%%00010%%00010$${SProductColumnLetter}$$%%00010%%00010Note the S prefix that appears because this is a string variable. This notation is the same as the notation used by String Manipulation for including flow variables.%%00010%%00010
Formula: Enter the formula to be written to the cell. NB Do NOT start the formula with "=". This will be added automatically.%%00010%%00010e.g. to place the formula %%00010=SUM(A2:A10) %%00010into a cell, just write%%00010SUM(A2:A10)%%00010%%00010Keep in mind that when writing data to Excel, the first data row will be row 2 in Excel, whereas in KNIME the "row number" of the first row is typically referred to as 0 and therefore you need to take this into account when writing an excel formula.%%00010%%00010For example, to sum the contents of column E, consisting of 5 rows of data, you would need to write SUM(E2:E6), so the first row is generally 2 and the final row is generally (row count+1), but if you are referring by KNIME row index (row number), the final row in Excel terminology would be rownumber+.2%%00010%%00010The current value of a flow variable may be included by including its name in the same format that you would see in the String Manipulator (Variable)%%00010e.g. to include the Integer variable "Number Rows" in an expression, you can write%%00010$${INumber Rows}$$%%00010%%00010Additionally, to add or subtract by a specific offset, you can include simple addition or subtraction, and place within square brackets.%%00010[$${INumber Rows}$$+1]%%00010%%00010Inclusion within a formula such as SUM(E2:En+1) can then be performed as follows:%%00010%%00010SUM(E2:E[$${INumber Rows}$$+1])

Write Excel Cell Formula

Options

Input Ports

Output Ports

Nodes

Extensions

Links

Download