Excel Writer / Sheet Appender with Excel Formulas

[KunalB86]

Hello, I’m using Table Creator to write excel formulas (vlookups to existing sheets) and would like to output them to Excel. Though when I use the existing excel writing nodes, the cells with formulas don’t execute, they are stagnant data values.

Has anyone been able to export excel formulas??

Thank you

[izaychik63]

Kunal, even though you can save formulas to Excel as text to be run they need to be changed to the type general. You can do necessary connections between data in KNIME directly. Or, create data and formula sheets in excel and populate from KNIME only data sheet.

[KunalB86]

Hi izaychik63, thanks for your response. Once I output to Excel the type is already General, however the formulas don’t execute. I’m not sure I understand “Or, create data and formula sheets in excel and populate from KNIME only data sheet.” this solution, could you walk me through an example?

[izaychik63]

It is not clear why you use KNIME to populate formulas in Excel? You can use your car as nut cracker too but over tools are cheaper. KNIME by itself cannot update Excel sheets just replace them. So, if you have permanent calculation but changing data you can place formulas referencing data on one sheet and data on the different one. For lookup you can use Join node in KNIME and export ready result to Excel. Just my recommendation.

[KunalB86]

Thanks for the response, however after all the manipulation of the data in Knime, the report is going to another employee who will manually change the data in one sheet. I’m trying to pull in his changes to an area in the sheets I’ve created in knime. Because of this second user manually updated data, I need to be able to write formulas and have them execute…

[izaychik63]

Could you provide an example of the data you receiving and the final result with formulas?

[KunalB86]

So for example, the formula I’ve written in the cell of the table creator is =VLOOKUP($D$8,SHEETNAME!$B:$X,3,FALSE), however when writing this to excel, it simply shows this formula in the cell vs executing the formula. This is the issue

[mlauber71]

In this entry I discussed several possibilities how to bring formulas from knime to excel. Unfortunately they involve using Python. But it is possible.

Excel Reader dumps formulas

The KNIME import and Export function cannot deal with Excel functions themselves. You can only get the results. If you want to preserve or restore Excel functions you have to either: use them on a sheet you would not touch with KNIME, Formulas that might refer to a ‘simple’ data sheet you would im- and export to and from KNIME use a Python package like openpyxl or R readxl Python Excel manipulation Python Excel table object

[KunalB86]

Thanks for this reply, I will look into the solution