Hub
ComponentComponent

Write Excel Cell Formula

takbb profile image
Draft Latest edits on 
Drag & drop
Like
Use or download
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

Component details

  • 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. To refer to Cell A3, you would simply write A3 The value of a flow variable may also be included in the reference (see Formula field help for more detailed info) To refer to the cell in Column A that has a value of currentrow + 2, you could write the formula: A[$${Icurrentrow}$$+1] Note that there must be square brackets surrounding the calculation. It is not possible to refer to columns here directly by name as this component is not passed details of the actual data table. However,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: $${SProductColumnLetter}$$ Note 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.

  • Formula

    Enter the formula to be written to the cell. NB Do NOT start the formula with "=". This will be added automatically. e.g. to place the formula =SUM(A2:A10) into a cell, just write SUM(A2:A10) Keep 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. For 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 The 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) e.g. to include the Integer variable "Number Rows" in an expression, you can write $${INumber Rows}$$ Additionally, to add or subtract by a specific offset, you can include simple addition or subtraction, and place within square brackets. [$${INumber Rows}$$+1] Inclusion within a formula such as SUM(E2:En+1) can then be performed as follows: SUM(E2:E[$${INumber Rows}$$+1])

Used extensions & nodes

This component does not have nodes, extensions, nested components and related workflows