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

Update 16 Jul 2025 - moved to using my "Collect Named Java Bundles" component in place of the older "Collect Required Plugin Jars" as this seems to be more stable for ongoing 5.x releases

@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