[ExcelExpression] Hi Guys, I just want to ask if you have other ways on applying the expression on excel through Knime? I know we can already use the Math formula node or Column expression Node for this but our client wants to see the expression used in calculating the data. Here`s my way of doing this, I used String replacer node to write the formula in the cells but I think this is not a practical solution because I need to do the expression repeatedly for every cell. I can use some tricks here but its not practical. If you have any solutions, suggestions or ideas, it will be a great help :blush: Thanks in advance:blush: Regards, Gambit

It is possible to manipulate Excel files in KNIME with the help of the Python package openpyxl like in this example: [image] Excel Writing Functions - Dynamic files export KNIME Analytics Platform Yes it is possible to write functions to an Excel sheet with Python’s OpenPyXL and KNIME: [image] [image] For further inspirations about KNIME and Python and Excel you might read this discussion . kn_example_python_excel_enter_formula.knwf (23.2 KB)

Hi @mlauber71 , Thank you for the information:blush: But after installing the Openpyxl package through AnacondaPrompt, the Python Script Node is still error. It says that there`s no openpyxl module. Is there any other things that I need to set up or install? Sorry but I am new to this field :sweat_smile: Thank you in advance. Regards, Gambit

To get KNIME and Python to work together is at least as (well) interesting as KNIME and R. There is a description about that, but you might have to try a few additional things. https://docs.knime.com/2018-12/python_installation_guide/index.html Also to keep various Anaconda channels (and their priorities) in sync can be challenging. I have written several entries about several aspects but not yet integrated them into a single article. In general, you would have to make sure the Openpyxl package is there in your environment (might not be the ‘base’ one) and then that you have activated it for the usage in KNIME.

Hi @mlauber71 , I think I installed the Openpyxl correctly because there’s no error on the Python Script node when I am executing the line. I always get an error on the xlsxwriter. I used the Jupyter to create the .py file but when I am installing it on the py36_knime environment through Anaconda Prompt, it always says that there’s no available channel for the xlsxwriter. Do I need to install something or change the file type of xlswriter? And also, after fixing this problem, I want to append the excel output of the python script node to the other excel output using excel sheet appender node. I have an example output that I want to achieve. Thank you very much for your support:blush: Regards, Gambit SampleOutput.xlsx (12.6 KB) ProcessFlow.docx (50.8 KB)

If you’re trying to apply a formula to every row you can try the constant value node - you can also likely replicate that formula with a combination of rule engine and rule based row splitter.

Hi @rightmirec , Can you give me an example workflow? Thanks in advance. best regards, Gambit

Or you convince the client to drop the request or you simply say how costly it is (obviously very costly :wink: because it’s complicated to do which you see is true. they might want the formulas so that they can d it themselves next time just another reason to charge generously. But then who am I? Not a consultant but that’s what i would to for questionable requests.

Hi there, [image] beginner: Or you convince the client to drop the request or you simply say how costly it is :+1: Can not understand this requirement. Why they want formulas from Excel in KNIME?? Br, Ivan

Hi ipazin, Actually, I already asked them about this :sweat_smile: But they just want to see the formula in excel. I’d rather try it, right? :wink: I know it’s hard but I believe there’s a solution for this. Regards, Gambit

does the formula in excel actually need to do a calculation or can you just add a column for each “calculated column with the formula / or explanation”? Either way I think you will need to look in the python lib mlauber71 suggested or then work with Java snippets to have that much control.

How to apply excel expression on excel through Knime?

KNIME Analytics Platform

mlauber71 November 11, 2019, 9:19pm 2

It is possible to manipulate Excel files in KNIME with the help of the Python package openpyxl like in this example: