Excel-Reader and Excel-Writer reevaluate

[ingo_knime]

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?

[mlauber71]

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

New Excel writer having problems with evaluate formulas

@DavidW indeed with your data there seems to be a problem with the evaluation of formulas. On my Mac this also exists with the old Excel writer. If you choose .XLS in the new writer it does work. Not sure if this provides any comfort. [image] @Iris / @ScottF this might be a problem with the Excel writer

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.

try to find a workaround to re-calculate Excel formulas on sheets other than...

[tobias.koetter]

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

[ingo_knime]

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.

[marcogertsch]

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.