Excel Formula Knime

Hi,

I am new to Knime.

I currently have spreadsheet A with a formula in a specific cell.

I would like to run a workflow where the excel formula is input into the spreadsheet by Knime instead of excel. The formula also has to go into a specific cell ie not all rows or columns.

So we would :

1.Start with a blank template (File Reader)
2. Connect nodes to the file reader
3. Use node to input excel formula into output data in a specific cell

Can anyone please help ?

Thanks

@jasonk2022 you can use OpenPyxl to insert Excel formulas with the help of KNIME:

4 Likes

thanks ill take a look.
seems complex for someone like me however I will try

There is a chance that the package OpenPyxl will be part of the (new) Bundled Python integration (we are counting on you @carstenhaubold - KNIME Analytics Platform - 4.6.0 Preview - #11 by carstenhaubold :slight_smile: )

4 Likes

Yep @mlauber71, we want to do that for the December release :slight_smile:

6 Likes

I havent got access to Python therefore that solution will not work.

I need to transpose an excel formula from one spreadhseet ( from a specific cell) and then input that into a separate spreadsheet again into a specific cell

or

input that formula into a specific cell for my output table

I have tried using column expression, rule engine and math formula modes but have become stuck

Can anyone please assist ?

Or you could approach this the other way around and use the “write to excel template” node to update your data values for selected cells while leaving the existing formatting of the spreadsheet / formulas in other cells intact.

I believe that you will find this the much easier and robust approach.

2 Likes

Not quite sure if it is the same with the normal Excel Writer,
But if you start a string column with = you can also write formulas directly with the template writer.
=1+1 will put that formula into Excel (formula has to be in english format)

4 Likes

That is awesome @AnotherFraudUser! I will definitely be adding this trick to my current project today.

2 Likes

The idea is really interesting but my excel looks like this
save
Your calculation is working without any manual adjustment? Did not see any escape
br

1 Like

@Daniel_Weikert… you are correct :see_no_evil:
Tested it on my private computer on openoffice…which handles it differently it seems :frowning:
Well when I open it with Excel I get the same result as you do…sorry for the wrong info!

Well at least my Excel Template Writer works as I remember :partying_face:
KNIME_project9.knwf (17.2 KB)

4 Likes

Thanks for the clarification. I was struggling with the standard Excel Writer node as well, but your write to template node is working great.

1 Like