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.