Excel-Reader and Excel-Writer reevaluate

In the advandec tab of excel-reader and excel-writer nodes are settings to reevaluate the selected excel-sheet. As I tested the function it seems it will only work on xls-files, not on xlsx-Files. Furthermore it would be usefull do add a function to recalculate / reevaluate all excel-sheets contained in a file, compareable to the excel-shortcut Strg-Alt-F9.

I tried to read in one sheet of a complex excel-xlsx-file. Most sheets depending each other and in this case it is necessary to reevaluate all sheets before reading the results into KNIME. In actual only the selected sheet will be reevaluted, but as I described before, there is no chance to get the results until all sheets of the file will be reevaluated.

Maybe there’s a topic for the next update?

1 Like

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

1 Like

Hello @ingo_knime ,
the problem with the re-evaluation of formulas in xlsx files is known (AP-17559) and will be fixed.
I have created a feature request (AP-18146) for the re-evaluation of the formulas in all sheets.
Have nice holidays.
Bye
Tobias

7 Likes

Thanks a lot to Markus and Tobias. Yes, as describes under AP-17559, that’s the problem. But especially only the Excel-Writer will be involved. In my case it’s important to reevaluate the complete Excel-File i want to read into KNIME. The Problem occures in Excel-Reader-Node too.

Well, I think you’ll know about this behavior at all. So, I’m looking hopefully forward you’ll be able to find a solution or maybe a work arround.

Thanks a lot.

2 Likes

I updated my environment to the latest Version 4.5.2 and was suprised with a lot of Errors in processing Excel files. As those workflows and Excel files worked since month without troubles I needed quite some time to find out what happens. I suggest that the Excel reader and writer have been update (at least under the hood) and are now doing a deep analysis of the Excel file. Concequence of this is, that the node is now failing if any named area in the Excel is not correctly assigned (depite of used and relevant for the functionality). I needed to split all combined fields in Excel to find such Zombies to make the import work again. In the opposite, the writers performance has “crashed”. A batch production of a large number of Excel files took about some (3-4) minutes till now, but with the new version this lasts forever (20+ minutes). So don’t know what exactly is the benefit of the new versions, but on my side this was not a success.