Write to Excel Template not updating specific cells with formula

[trafalgarlaw]

Hello, I am using Write to Excel Template node and in my template, there is already a formula.

So when I run the workflow, the formula is not updating because the excel is not reading data pasted from write to excel template node. As you can see in the screenshot, the formula is just sum the 2115 and 9, but still showing 0 as total.

To fix it, I really need to manually click the cell with formula and hit F2 + Enter to recalculate the formula.

Though in write to excel template, the recalculate box is already ticked.

[trafalgarlaw]

@takbb Hello, maybe you can help.

[takbb]

Hi @trafalgarlaw ,

I’m not a big user of that node, useful though it is. Could you upload a small demo workflow that exhibits the problem where the formula is not being recalculated. I would have expected it to recalculate if the option to recalculate all existing formulas is selected, which you say is already ticked, unless I am misunderstanding what is happening.

Which version of KNIME are you using?

The developer of this node is @AnotherFraudUser , so any additional info/examples you can give will assist with troubleshooting.

further reading:

Excel writer does not evaluate excel formulas

Hi @rohdchri, do you have the lastest update? In this screen it should show the new option: [grafik] *if this is not the case even with the latest update - try to start knime with the knime.ini parameter -clean (sometimes it does not want to show the updates): [grafik] Hope this fixes your problem - at least with my Test-Excels it works. (should it not work - could you please provide an Test Excel - so I can check in detail?)

[mlauber71]

@trafalgarlaw I have toyed around with trying to bring knime and Phyton to update Excel formulas.

You could check out the two approaches using openpyxl. One resetting every formula one trying to add a blank to each formula and by that triggering a recalculation. Not sure if this will work.

Excel - Formula Evaluation in KNIME – mlauber71

[takbb]

@trafalgarlaw I’ve tried to create a java equivalent for recalculating an xlsx file. If you are using KNIME 5.x, this component might work:

Recalc Excel for KAP5.x – takbb

Notes: I have only tried it with 5.2.5 and 5.3.2 in a very limited test. It might work for 4.x and earlier 5.x releases too, but I cannot be totally certain. Unfortunately the java “Apache POI” libraries change with each release of KNIME AP, so the java code contained in this component needs to be supplied with the current version of those libraries from the plugins folder. It uses another experimental component I have written to do that, but again I cannot guarantee that the way it does this will work with all KNIME AP versions.

Let me know if it works for you. (It is located in my public “experimental” space on the hub, which gives some idea of how much it should be trusted, so please don’t use it for anything mission-critical until you are happy that it works for you)

[trafalgarlaw]

Thank you @mlauber71 and @takbb, let me check all of your suggestions and get back to both of you if I am facing any concerns. Again, thank you!!

[mlauber71]

@takbb currently this will only run on Windows it seems. The Java paths are different on a Mac. I modified it so as to extract a plugin path from the osgi.syspath with the help of Extract System Properties node:

Maybe you can take a look if this will work on a Windows machine (I can check on mine in a few days).

Excel - Formula Evaluation in KNIME – mlauber71

[takbb]

Many thanks for the feedback and suggestion @mlauber71. That looks like a good solution and I think works fine on Windows too.

The community hub won’t let me authenticate at the moment, but I’ll upload an updated version of the underlying “Collect Required Plugin Jars” component when I can.

I am now re-editing this on my 4.7.8 installation so that it can be used with earlier KNIME versions. (I realised it had the new KNIME 5.x Column Renamer node which at a stroke rendered it incompatible with the earlier KNIME release).

Once that’s there, I’ll update and re-upload the Recalc Excel component for further testing.

[takbb]

I’ve updated the java-based component using @mlauber71 's suggestion for finding the required java libraries in the KNIME plugin folder, so hopefully this is now compatible with all KNIME AP installations rather than just Windows.

The component I uploaded before has been updated, but having now re-created it using KNIME 4.7.8, I have also re-uploaded it under the more general name “Recalculate Excel”

Recalculate Excel – takbb

---

For anybody interested in the internals… the additional component it uses for finding (attempting to find) the required java libraries from the plugin folder is this:

Collect Required Plugin Jars – takbb

The idea here is that the java snippet requires particular libraries to be made available in the form of java archive (jar) files. In this case it is the Apache POI library that it uses. However, in different releases of KNIME, the libraries change so it is difficult to handle this directly in the java snippet config.

For example in KNIME AP 5.3.2 the bundled poi libraries are:

whereas in KNIME AP 4.7.8 they are:

and in 5.2.5 they are:

Not all of them are required, but from one version to the next, the bundled jars can change. To (try to ) handle this, I wrote the “Collect Required Plugin Jars” component. This is configured with the “wildcarded” names of all the plugin libraries that I have seen mention “poi” in their name:

It searches the plugins folder for the current installation (now cross-OS compatible thanks to @mlauber71), and finds all the files that match the supplied wildcard specification, and returns them as a String List flow variable “JarFileList”

This is then passed to the Java Snippet (or in this case Java Edit Variable) via the jarFiles configuration

With that done, and all other things being equal, the java code can work across different versions of KNIME, provided that the same basic java libraries/plugins continue to be bundled, and have compatible file naming. Obviously if that changes in future, an update to the component may be required, but for now it seems to work.

The code contained within the Java Edit Variable node that performs the Excel recalculation was generated by chatGPT (see conversation here) and then manually modified to work with a Java Edit Variable node.

[mlauber71]

@takbb this is great. Thank you. One problem might be that if I provide a (string) path with a .xlsx extension the node will complain that this is not a valid extension. It only works once I remove the setting to have .xlsx extensions in the configuration node. Which is strange but seems to be the case.

[takbb]

@mlauber71

One problem might be that if I provide a (string) path with a .xlsx extension the node will complain that this is not a valid extension. It only works once I remove the setting to have .xlsx extensions in the configuration node

hmmm… indeed… there does seem to be something problematic with the “List File Browser Configuration” node.

If I restrict the list of extensions to .xlsx, then it complains if the component has the file path configured only by flow variable, and nothing has been physically entered in the textbox on the component config

The error is cleared if I just type in a nonsensical “.xlsx” into the “Path to Excel xlsx file” texbox!

I’ve updated the component (“Recalculate Excel”) so there is no longer a restriction on Valid File Extension and whilst that means that any file can now be selected, it gets rid of the silly error message. (I hope!). Do let me know if an error such as this still occurs,

I’ve also updated the way the Collect Required Plugin Jars works and interacts with the Java Edit Variables. Just supplying jar files turned out to be insufficient in 4.7.8, ao it now tries to include “Bundles” (the packaged java libraries) to the java node.

I’m not exactly sure how the bundles work internally in KNIME but my “hack” is working at the moment. It’s a bit of a minefield it seems, and possibly more than I bargained for. That said, my simple test, of recalculating an xlsx file with a single sheet has been successful in KNIME 4.7.8, KNIME 5.2.5 and KNIME 5.3.2.

KNIME 6.x? Bring it on… !!

[trafalgarlaw]

Thank you @takbb and @mlauber71. Now trying with our KNIME 4.x version, will let you know if all good Really appreciate all of your efforts and expertise. Really loved the community of sharing knowledge!!!