Hub
Pricing About
  • Software
  • Blog
  • Forum
  • Events
  • Documentation
  • About KNIME
  • KNIME Community Hub
  • Nodes
  • Excel Cell Updater
NodeNode / Sink

Excel Cell Updater

IO Write Streamable
Drag & drop
Like
Copy short link

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.

Node details

Input ports
  1. Type: Table
    Input table
    The data table which contains the update information.
File System Connection (Dynamic Inport)
The file system connection.
  1. Type: File System
Sheet Input Ports (Dynamic Inport)
Additional data tables which contain update data.
  1. Type: Table

Extension

The Excel Cell Updater node is part of this extension:

  1. Go to item

Related workflows & nodes

  1. Go to item
  2. Go to item
  3. Go to item

No known nodes available

KNIME
Open for Innovation

KNIME AG
Talacker 50
8001 Zurich, Switzerland
  • Software
  • Getting started
  • Documentation
  • E-Learning course
  • Solutions
  • KNIME Hub
  • KNIME Forum
  • Blog
  • Events
  • Partner
  • Developers
  • KNIME Home
  • KNIME Open Source Story
  • Careers
  • Contact us
Download KNIME Analytics Platform Read more on KNIME Business Hub
© 2023 KNIME AG. All rights reserved.
  • Trademarks
  • Imprint
  • Privacy
  • Terms & Conditions
  • Credits