Maintaining Formulas when Writing Data to Excel

[CKillick]

Hello Everyone

I’m hoping I can once again gather the wisdom of the community for an issue I’m having with writing data to excel. I’ve searched the forums for a solution but was unable to find one. So if I’ve missed it, please redirect me.

I’m building a workflow that produces many individual excel reports based on a large excel ‘masterlist’. The data manipulation element is working well. What I need to do now is write the individual reports to pre-existing excel templates. What I’ve done is ensured that the excel templates ‘end’ in the correct place, and then use the ‘Write to Excel’ node and append the data to them. From that point I use conditional formatting rules to make sure everything is looking spiffy.

The problem is that the templates contain a few simple formulas to count the data and produce basic analytics. And even though the data is written to the excel document, within the range that they cover, the values do not update when the excel documents are opened. I have to do something like delete and replace a row, to make them read anything other than 0. I’ve attached a screenshot below.

Does anyone have any idea how I can get excel to automatically update these formulas when I write the data to excel? If not, is there a way that I can run equivalent operations in knime and write them into the template along with the rest of the info? Unfortunately I’m stuck with these templates, so any solution I come up with need to look the same at the end.

Hope that makes sense, thank you so much in advance.
Cam

[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.

[CKillick]

Thanks very much @mlauber71, I’ll definitely take a look at those. If anyone else has some advice or any other potential solutions I’d love to hear them as well.