Maintaining Formulas when Writing Data to Excel

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

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

Th node Write To Excel Template – KNIME 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.

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.

4 Likes

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.