Excel Cell Updater

This node updates cells in an existing Excel spreadsheet. The cell addresses and their new content are supplied by an input data table.

Adding additional table input ports allows to update multiple spreadsheets in the same file.

The node supports two file formats chosen by file extension:

.xls format: This is the file format which was used by default up until Excel 2003. The maximum number of columns held by a spreadsheet of this format is 256 (address IV) and the maximum number of rows is 65536.
.xlsx format: The Office Open XML format is the file format used by default from Excel 2007 onwards. The maximum number of columns held by a spreadsheet of this format is 16384 (address XFD) and the maximum number of rows is 1048576.

Each input table must have one column which contains the addresses of the sheet cells which should be updated. This column has to have a string-compatible type. Two types of addresses are supported and can be used interchangeably:

Excel cell addresses, e.g. "A5", "E96" or "OZ23914"
Number addresses separated by a colon ":" with the first number being the number of the column and the second the one of the row. Both start counting with 1. You can use the COLUMN() function in Excel or enable Excel's R1C1 reference style to get the column number. Examples: "1:5", "5:96" or "413:23914"

The remaining columns contain the replacement values for the specified cells. A replacement value should appear in at most one column per row and the remaining cells should be left empty (i.e. only contain missing values). The column type should be the same as the (desired) column type in the updated sheet. If all cells in a row but the cell address contain missing values, the replacement value is a blank cell or the string specified in the “Replace missing values by” field.

The formatting of existing cells, rows and columns in the Excel sheet will be preserved.

In the following table ? stands for a missing value. This example table would write the string "Ok" to Excel cell A5 and the number 50 to cell E96 (in number address style). The cell OZ23914 will be cleared if no alternate missing value is defined.

Address	String	Integer
A5	Ok	?
5:96	?	50
OZ23914	?	?

This node can access a variety of different file systems. More information about file handling in KNIME can be found in the official File Handling Guide.

Options

Settings

Read from

Select a file system which stores the data you want to read from and potentially update. There are four default file system options to choose from:

Local File System: Allows you to select a file/folder from your local system.
Mountpoint: Allows you to read from a mountpoint. When selected, a new drop-down menu appears to choose the mountpoint. Unconnected mountpoints are greyed out but can still be selected (note that browsing is disabled in this case). Go to the KNIME Explorer and connect to the mountpoint to enable browsing. A mountpoint is displayed in red if it was previously selected but is no longer available. You won't be able to save the dialog as long as you don't select a valid, i.e. known mountpoint.
Relative to: Allows you to choose whether to resolve the path relative to the current mountpoint, current workflow or the current workflow's data area. When selected a new drop-down menu appears to choose which of the three options to use.
Custom/KNIME URL: Allows to specify a URL (e.g. file://, http:// or knime:// protocol). When selected, a spinner appears that allows you to specify the desired connection and read timeout in milliseconds. In case it takes longer to connect to the host / read the file, the node fails to execute. Browsing is disabled for this option.

To read from other file systems, click on ... in the bottom left corner of the node icon followed by Add File System Connection port. Afterwards, connect the desired file system connector node to the newly added input port. The file system connection will then be shown in the drop-down menu. It is greyed out if the file system is not connected in which case you have to (re)execute the connector node first. Note: The default file systems listed above can't be selected if a file system is provided via the input port.

File/URL

Enter a URL when writing to Custom/KNIME URL, otherwise enter a path to a file. The required syntax of a path depends on the chosen file system, such as "C:\path\to\file" (Local File System on Windows) or "/path/to/file" (Local File System on Linux/MacOS and Mountpoint). For file systems connected via input port, the node description of the respective connector node describes the required path format. You can also choose a previously selected file from the drop-down list, or select a location from the "Browse..." dialog. Note that browsing is disabled in some cases:

Custom/KNIME URL: Browsing is always disabled.
Mountpoint: Browsing is disabled if the selected mountpoint isn't connected. Go to the KNIME Explorer and connect to the mountpoint to enable browsing.
File systems provided via input port: Browsing is disabled if the connector node hasn't been executed since the workflow has been opened. (Re)execute the connector node to enable browsing.

The location can be exposed as or automatically set via a path flow variable.

Create a new file

If this option is selected, create a copy of the original file which is updated instead of the original file. The newly created file must have the same file type as the original file.

Write to

Provide the path to new file that should be used. This has the same file system and file options as "Read from".

Create missing folders

Select if the folders of the selected output location should be created when they do not already exist. If this option is unchecked, the node will fail if a folder does not exist.

If exists

Specify the behavior of the node in case the output file already exists.

Overwrite: Will replace any existing file.
Fail: Will issue an error during the node's execution (to prevent unintentional overwrite).

Excel sheet

Specify which sheet should be update by the corresponding input table.

Based on address column

Select the name of the column which contains the addresses of the cells which should be updated.

Replaces missing values by

If selected, the node will write the specified value as a string to the address if only missing values are found in that row. Otherwise a blank cell is created.

Evaluate formulas

If checked, all formulas in the file will be evaluated after the sheet's updates have been performed. This is useful if values that are used in formulas in the sheets have been updated.

Encryption

Password to protect files

Allows you to specify a password to decrypt existing files that have been protected with a password via Excel.

None: only files without password protection can be updated
Credentials: use a password set via workflow credentials
Password: specify a password

Input Ports

: The data table which contains the update information.
…: The file system connection.
…: Additional data tables which contain update data.

Output Ports

This node has no output ports

Popular Predecessors

Popular Successors

Views

This node has no views

Workflows

Developers

You want to see the source code for this node? Click the following button and we’ll use our super-powers to find it for you.

Installation

To use this node in KNIME, install the extension KNIME Excel Support from the below update site following our NodePit Product and Node Installation Guide:

v5.3

A zipped version of the software site can be downloaded here.

Plugin provider: KNIME AG, Zurich, Switzerland

Plugin version: 5.3.3.v202410291553

On NodePit since: 2024-07-09

Last update: 2024-11-15

Tags: Streamable

KNIME versions: Since v4.5

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!