Icon

Proof of concept - Excel Cell Formula writing -takbb

Proof of concept - Excel Cell Formula writing

PROOF OF CONCEPT / EXPERIMENTAL / BETA

(subject to change - feel free to try this out and have a play, but use at your own risk!)

Constructive Feedback and suggestions welcome

This is here just to get an absolutereference to the spreadsheet filenamein workflow data areainto a flow variable so that it can bepassed to the Write Excel Cell componentuses java to modifythe spreadsheetIMPORTANT - THIS WON'T WORKIF THE XLSX IS OPEN IN EXCELBUT IT WON'T SHOW ANY ERRORS EITHER!Takes the formula from the lowerdata input and builds it intoexcel formula to be applied to thesheet, based on the size of the datatable in the top inputdemo dataTry adding some extra rows tocheck the Excel Writer isn't cheatingwrites the demodata to the excel spreadsheetat this point, the excel is a table with just the data from the input portExecute this to openthe Excel Spreadsheetand take a look at the result:-)Formula to apply(syntax still to be documentedand possibly improved upon)At the moment [#R] means "current row[#ROWSTART] is first data row [#ROWEND] is last data row$Colname$ is a reference to named column, so $Product$ means the Product columnwhich the Excel Cell Formula Expander will turn into Excel column "A" in this caseOpen File or Folder Write Excel CellFormula (multi cell) Excel CellFormula Expander Table Creator Excel Writer Open File or Folder Table Creator This is here just to get an absolutereference to the spreadsheet filenamein workflow data areainto a flow variable so that it can bepassed to the Write Excel Cell componentuses java to modifythe spreadsheet IMPORTANT - THIS WON'T WORKIF THE XLSX IS OPEN IN EXCELBUT IT WON'T SHOW ANY ERRORS EITHER!Takes the formula from the lowerdata input and builds it intoexcel formula to be applied to thesheet, based on the size of the datatable in the top inputdemo dataTry adding some extra rows tocheck the Excel Writer isn't cheatingwrites the demodata to the excel spreadsheetat this point, the excel is a table with just the data from the input portExecute this to openthe Excel Spreadsheetand take a look at the result:-)Formula to apply(syntax still to be documentedand possibly improved upon)At the moment [#R] means "current row[#ROWSTART] is first data row [#ROWEND] is last data row$Colname$ is a reference to named column, so $Product$ means the Product columnwhich the Excel Cell Formula Expander will turn into Excel column "A" in this case Open File or Folder Write Excel CellFormula (multi cell) Excel CellFormula Expander Table Creator Excel Writer Open File or Folder Table Creator

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: BATEB031
Created at: 2023-06-02
On NodePit since: 2023-06-04
Last update: 2024-04-27
Created with KNIME version: v4.7.2

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

Try NodePit Runner!