{"title":"Excel Cell Updater","nodeType":"Sink","id":"*pfKae1pgEo_UWX2K","factoryName":"org.knime.ext.poi3.node.io.filehandling.excel.updater.cell.ExcelCellUpdaterNodeFactory","factoryId":"org.knime.ext.poi3.node.io.filehandling.excel.updater.cell.ExcelCellUpdaterNodeFactory","path":["IO","Write"],"owner":"knime","ownerAccountId":"account:team:b5f0848f-496d-4e6a-b869-fa3532248db7","description":"

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

\n \n

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

\n \n

The node supports two file formats chosen by file extension:

\n \n \n \n

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:

\n \n \n \n

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.

\n \n

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

\n \n

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.

\n \n AddressStringIntegerA5Ok?5:96?50OZ23914??\n \n

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.

\n \n \n ","shortDescription":"Updates single cells in an Excel file","keywords":[],"deprecated":false,"streamable":true,"tags":["IO","Write","Streamable"],"icon":"data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAABAAAAAQCAYAAAAf8/9hAAAACXBIWXMAAAsTAAALEwEAmpwYAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAExSURBVHgBnVK9TsNADLbTDp3SsKdN2VgqVqCCZ+EJAt269Toi1IE8AQ+QFXVFoMKM+gCkaZhJ8gAc9pGr1PyWfpJjX2x/PvuMrutKaIDneUhqSjIrOJlAo8zOwji5tFAbmsHJIrMLJNjUAl1/lpGUY88WBBzaApMgocqPVxfnlS2E0ZcgdUti5VwxMT8Gm8245fR6Ip/48vZONQ1OmpB0Srg7gHhmdbtoKLZdBJm+5s/neq1EI3e+acsfOaZyU0AYMJkEqZdFXft0OISP1Qru53MeCOh/OqZyOIN+fzsb27bhdblU9uVoBFEUbeMM+CdM09w577OJqrrv+5AmCTwtFqqFNE2Vr66Fb1KWHtix4yidO8d1BALqVvgPD60qT5wkz/TOR2SeQHEX+OnvgjCc/AJd35u+rIAA6gAAAABJRU5ErkJggg==","views":[],"inPorts":[{"objectClass":"org.knime.core.node.BufferedDataTable","color":"000000","dataType":"Table","optional":false,"name":"Input table","description":"The data table which contains the update information."}],"outPorts":[],"dynInPorts":[{"groupName":"File System Connection","groupDescription":"The file system connection.","types":[{"objectClass":"org.knime.filehandling.core.port.FileSystemPortObject","color":"178ba5","dataType":"File System"}]},{"groupName":"Sheet Input Ports","groupDescription":"Additional data tables which contain update data.","types":[{"objectClass":"org.knime.core.node.BufferedDataTable","color":"000000","dataType":"Table"}]}],"dynOutPorts":[],"additionalSiteInformation":{"url":"https://update.knime.com/analytics-platform/5.12","trusted":true,"enabledByDefault":true,"name":"KNIME Analytics Platform 5.12 Update Site","version":"5.12.0"},"dialog":[{"sectionName":"Options","fields":[{"name":"Input file","description":"Select the Excel file (.xls or .xlsx) to read from and update. The file system can be a local file system, a mountpoint, or a file system connected via an input port.","optional":false},{"name":"Password to protect files","description":"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.\n","optional":false},{"name":"Workflow credentials","description":"Select the workflow credentials to use.","optional":false},{"name":"Password","description":"Enter the password to protect the file.","optional":false},{"name":"Create a new file","description":"If selected, a copy of the original file is created and updated instead of the original file. The newly created file must have the same file type as the original file.","optional":false},{"name":"Output file","description":"Provide the path to the new file that should be created as a copy of the original file. The output file must have the same file type (.xls or .xlsx) as the input file.","optional":false},{"name":"Create missing folders","description":"If enabled, missing folders in the specified path will be created automatically.","optional":false},{"name":"If exists","description":"Specify the behavior of the node in case the output file already exists.\n","optional":false},{"name":"Input tables","description":"For each input table, specify which sheet in the Excel file should be updated.\n","optional":false},{"name":"Replace missing values by","description":"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.","optional":false},{"name":"Replacement value","description":"The value to write in place of a missing value.","optional":false},{"name":"Evaluate formulas","description":"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. Note: This can cause errors when there are functions not implemented by the Apache POI library.","optional":false}]}],"links":[],"kudos":[],"kudosCount":0,"bundleInformation":{"bundleVersion":"5.12.0.v202604281119","bundleName":"KNIME POI (Excel) Plug-in","bundleSymbolicName":"org.knime.ext.poi3","bundleVendor":"KNIME AG, Zurich, Switzerland","featureSymbolicName":"org.knime.features.ext.poi","featureName":"KNIME Excel Support","featureVersion":"5.12.0.v202605041104","featureVendor":"KNIME AG, Zurich, Switzerland","extensionId":"*ei_rc2H2e57n_qvV"},"frequentSuccessors":[]}