How to apply excel expression on excel through Knime?

KNIME Analytics Platform
#1

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

#2

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

1 Like
#3

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

#4

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.

2 Likes
#5

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)

#6

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.

#7

Hi @rightmirec ,

Can you give me an example workflow?
Thanks in advance.

best regards,

Gambit

#8

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.

1 Like
#9

Hi there,

:+1:

Can not understand this requirement. Why they want formulas from Excel in KNIME??

Br,
Ivan

1 Like
#10

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

#11

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.

1 Like
#12

The package should be available in conda-forge

https://anaconda.org/conda-forge/xlsxwriter

KNIME and python is a piece of work. Please have a look at this entry

It could be that it also works with a base environment without the .py file to start Python. You might have to try a few things.

#13

Hi there,

Ok. Then copy Excel file, format columns that contain formulas as text (this still shows values in my Excel version - maybe newer one automatically display formula so you are ready for KNIME here), if not double-click (or F2) on first row to show formula, click enter and drag it down till the column end. After importing into KNIME continue with manipulation according to customer requirements…

Br,
Ivan

#14

Hi @beginner,

It needs to do a calculation. And the very hard part of this is there’s already the calculated columns that I need to put the expressions. Actually, the formula/expression that I need to put is very simple. It’s just the way on how to do it. Worst case scenario is to add new sheet for the calculation, but it’s also very tough task. Currently I am able to write the expression on a new excel sheet. After figuring out how to apply this expression on multi rows and columns, I still need to figure out how to append this on an existing excel file. Because for I now, I can just write an expression on just one cell :sweat_smile: I apologize for my lack of knowledge on these things. But I really appreciate your help and suggestions. Thank you very much.

regards,

Gambit

#15

Hi @mlauber71,

Now, I am able to write the expression on the excel but on a new file. I am still figuring it out on how to write expression on an existing file. Maybe the file path setting is the one that I need to solve. Thank you very much for your help :blush:
I am reading this link now :blush:

Regards,

Gambit

#16

Hi @ipazin

That’s a good idea. The problem on this is I need to do this on an existing columns with calculated values. If I do this, it will overwrite the existing values. Unless I do this on different sheet. Also, if this works, I noticed that I still need to double click the cells for the expressions to become ok. Because at first it will show the strings you wrote, but after double clicking the cell, it will be ok and I don’t know the problem with this :sweat_smile: But thank you for this, It adds to my solutions :blush:

Regards,

Gambit

1 Like
#17

I have created another Example since the last one was a mixture of Openpyxl and xlsxwriter now just the first one.

You could add formulas just like that:

ws['B2'] = "=SUM(A1:A100)"

The thing is you have to load the Excel file in Python, load the Sheet (or create a new one) and then add the formulas. If you want to add a formula into a range of cells you would have to write additional Python code.

kn_example_python_excel_enter_formula_openpyxl.knwf (21.8 KB)

#18

Hi @mlauber71,

I made a sample Knime workflow earlier. It’s very simple but the problem is when I open the output file, I still need to double click every cell to show the value. Do you have any opinion regarding this? Thank you :blush:

_input.xlsx (8.8 KB)
_output.xlsx (3.6 KB)
Insert Expression on Excel.knwf (15.1 KB)

Regards,

Gambit

#19

There has been the suggestion to use xlwings to evaluate the results. Have not used it yet.

you can use xlwings to get the formula evaluated by excel:

import xlwings as xw
wbxl=xw.Book('to_erase.xlsx')
wbxl.sheets['Sheet'].range('B3').value
Excel Based Queries - Refresh through Knime
#20

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.