Continuing my series on Spreadsheet Automation, this time I’ll show you how to insert and calculate Excel Formula into your spreadsheet, so it would be easy for you to automate your spreadsheet by simply copying the formula into this KNIME workflow.
Full Walkthrough will be available later
Spreadsheet Automation - Creating Excel Pivot Tables
Here is my Full Walkthrough of the KNIME Workflow, enjoy!
Description: Using KNIME Analytics Platform to replicate and automate one of the commonly used Excel Functions – Inserting and Calculating Formula.
What makes Excel so versatile and able to meet all the demands of its users, is its ability to insert and calculate Formula, but as we are undergoing digital transformation across all industries, we should find a way to simplify and automate our tasks and reports. The largest hindrance in completely automating Excel files are those that contain Formula. What user need is to replicate the formula that they used in their Excel files at the same time automate to provide time saves for the user. So I have created a KNIME Workflow with a ready-made Python Script to automate spreadsheet that contains any Formula by simply copying them and pasting it to the KNIME Workflow or from an External File that would be fed into the workflow. This KNIME Workflow has functionalities as listed below
Users can reuse the workflow or its nodes in their respective projects without any Python programming background, but they should take note of the requirements that need to be installed first before they can run the KNIME workflow.
Please take note that there will be special handling for Dynamic Array Formula which requires knowledge on Column Expression Node Syntax.
Requirements:
How to Set Up the KNIME Python Extension
Note: You may require to install KNIME extensions to use some of the nodes in the workflow.
Python Libraries Required:
To Install Python Libraries, Go to Start Menu, Select Anaconda3 Folder, Select Anaconda Prompt (Anaconda3), Type: pip install name_of_python_library
Note: You may require additional modules if the listed libraries have pre-requisite modules needed to install first in order to run such as (Ast, Jpype, Numpy, Pickle, etc.) if this did not came with the Anaconda3 installation.
Author: Daniel Si
Nice! Thanks for the detailed writeup. 
Updates in my blog post:
-Replaced Typo errors
-Replaced Column Expression Node Syntax for Dynamic Array Formula
Update on the workflow
-Fixed bug related to autofilling formula with variable string
Update on the workflow
-Fixed bug, missing AutoFill column when there are no assigned dynamic formula/cell
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.