Maintaining Formulas when Writing Data to Excel

[mlauber71]

@CKillick there is this thread about Excel and keeping formats etc. when writing new data:

Writer Node Issues

@helpmeplease if you want the structure of your Excel file unchanged and just change values there are these options, if you must change data on a pre-formatted sheet (otherwise you might just replace this sheet). The Excel Cell Updater can change values in specific cells. You can use a community node to write to an Excel file without changing the structure or formats: You can the use Python and OpenPyxl to do more complex editings of excel files. You will have to plan ahead what this d…

Th node Write To Excel Template (deprecated) – KNIME Community Hub can be used to recalculate formulas on the same page. I also toyed around with openpyxl to refresh the formula results but it is not exactly what I was hoping for.

Excel-Reader and Excel-Writer reevaluate

@ingo_knime not sure if this is related but there was a thread about formulas and evaluation and there also is a ticket for a bug (AP-17559) - @ScottF . I experimented with openpyxl and the AF nodes - "Write to Excel Template"also offers a re-calculation but again only of the sheet that has been filled it seems. But could not find a workaround.

Another option could be to delete and re-write the formula thru openpyxl and see if that would cause a recalculation.

Some time back Excel itself had problems with some recalculations on large files. I remember sending manual keystrokes thru VBA in order to force a full recalculation (not very elegant indeed). I am not sure if this is still a thing with Excel itself and how this might affect other software interacting with Excel.