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 fileSheet Name
Enter the name of the sheet to be updated in the XLSX fileCell 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
Created with KNIME Analytics Platform version 4.7.4
- Go to item
- Go to item
Legal
By using or downloading the component, you agree to our terms and conditions.