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

Nodes

• String Configuration4 ×
• Component Input1 ×
• Component Output1 ×
• Java Edit Variable1 ×

Extensions

• FeatureKNIME Javasnippet
• FeatureKNIME Quick Forms

Links

• No links available