Excel Writer

This node writes the input data table into a spreadsheet of an Excel file, which can then be read with other applications such as Microsoft Excel. The node can create completely new files or append data to an existing Excel file. When appending, the input data can be appended as a new spreadsheet or after the last row of an existing spreadsheet. By adding multiple data table input ports, the data can be written/appended to multiple spreadsheets within the same file.

The node supports two 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 and rows held by a spreadsheet of this format is 256 and 65536 respectively.
.xlsx format: The Office Open XML format is the file format used by default from Excel 2007 onwards. The maximum number of columns and rows held by a spreadsheet of this format is 16384 and 1048576 respectively.

If the data does not fit into a single sheet, it will be split into multiple chunks that are written to newly chosen sheet names sequentially. The new sheet names are derived from the originally selected sheet name by appending " (i)" to it, where i=1,...,n.
When appending to a file, a sheet may already exist. In this case the node will (according to its settings) either replace the sheet, fail or append rows after the last row in that sheet. This can be used to append data to an Excel file without having to create a new sheet name once the original sheet is full by just selecting the original sheet name. The data will be appendend to the last sheet in the name sequence. The original sheet name does not have to be changed.

This node does not support writing files in the '.xlsm' format, yet appending is supported.

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

Write to

Select a file system in which you want to store the file. There are four default file system options to choose from:

Local File System: Allows you to select a location on your local system.
Mountpoint: Allows you to write to 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 write timeout in milliseconds. In case it takes longer to connect to the host / write the file, the node fails to execute. Browsing is disabled for this option.

It is possible to use other file systems with this node. For this you have to enable the file system connection input port of this node by clicking the ... in the bottom left corner of the node's icon and choose Add File System Connection port .
Afterwards, you can simply connect the desired connector node to this node. 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 missing folders

Select if the folders of the selected output location should be created if 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.
Append: Will append the input table(s) to an existing Excel file, by either creating new sheets or by appending to existing sheets (see If sheet exists below).
Fail: Will issue an error during the node's execution (to prevent unintentional overwrite).

Sheet name

Name of the spreadsheets that will be created. The dropdown can be used to select a sheet name which already exists in the Excel file or a custom name can be entered. If "If sheet exists" isn't set to append, each sheet name must be unique. The node appends the tables in the order they are connected.

If sheet exists

Specify the behavior of the node in case a sheet with the entered name already exists. (This option is only relevant if the file append option is selected.)

Overwrite: Will replace any existing sheet.
Append: Will append the input tables after the last row of the sheets.
Note: the last row is chosen according to the rows which already exist in the sheet. A row may appear empty but still exist because it or one of its cells contains styling information or was not removed by Excel after the user cleared it.
Fail: Will issue an error during the node's execution (to prevent unintentional overwrite).

Write row key

If checked, the row IDs are added to the output, in the first column of the spreadsheet.

Write column headers

If checked, the column names are written out in the first row of the spreadsheet.

Don't write column headers if sheet exists

Only write the column headers if a sheet is newly created or replaced. This option is convenient if you have written data with the same specification to an existing sheet before and want to append new rows to it.

Replaces missing values by

If selected, missing values will be replaced by the specified value, otherwise a blank cell is being created.

Evaluate formulas

If checked, all formulas in the file will be evaluated after the sheet has been written. This is useful if other sheets in the file refer to the data just written and their content needs updating. This option is only relevant if the append option is selected. This can cause errors when there are functions that are not implemented by the underlying Apache POI library. Note: For xlsx files, evaluation requires significantly more memory as the whole file needs to be kept in memory (xls files are anyway loaded completely into memory).

Autosize columns

Fits each column's width to its content.

Portrait/Landscape

Sets the print format to portrait or landscape.

Paper size

Sets the paper size in the print setup.

Encryption

Password to protect files

Allows you to specify a password to protect the output file with. In case the "append" option is selected and the file already exists, the password must be valid for the existing file.

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 to write.
…: The file system connection.
…: Additional data table to write.

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-20

Tags: Streamable

KNIME versions: Since v4.3

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

Try NodePit Runner!