My workflow writes an excel file, with a variable amount of sheets(Can be two sheets, but also seven, depending on the calculations).
As the amount of sheets is variable, I subsequently loop over the available sheets to apply an excel format. This works fine, as long as I have max 5 Sheets in the excel. Now I had the case that also a 6th sheet emerged and the XLS Formatter (apply) node throws this error:
ERROR XLS Formatter (apply) 0:288:13 Execute failed: The input file already contains formatting styles. This is currently unsupported in our extension beyond a degree that KNIME’s XLS Writer and Sheet Appender nodes would utilize.
I also tried shuffling the sheet chronology for formatting, to exclude the possibility that one sheet was defect. I also excluded and included the sheets in different combinations, step loop analysis etc… The result was always the same: the last iteration failed in the “formatting loop”
Is there a limitation to the XLS Formatting nodes at this point in time, so it can only handle max 5 sheets?
The workflow looks like this:
I have workflows that work using the Continental nodes with more than 6 sheets. I believe the problem is not the number of sheets, but the fact that your loop attemps to format an excel file which already contains a formating style, as indicated by the Execution Failed message displayed in your snapshot. Adding formatting to an already formatted excel file is so far not possible with the Continental nodes. I have never tried to dynamically add formatting within a loop for the same Excel file. The only solution I could imagine, it is to put the XLS formatter (apply) node outside a loop, but I wonder whether it exists a KNIME loop allowing this and if eventually this is possible to be done.
Hi @aworker
Thanks for clearifying that it can work for several sheets. Presumably, this is then a loop problem. What strucks me, is that it works for 5 or less sheets without problems. Somehow in the 6th iteration, it recalls the excel file and then recognizes that it already has a format from the 5 iterations before. Which is obviously not the case in the iterations before…
Unfortunately, I have to stick to a loop solution, as I have several files created (also a loop) with different combinations of excel sheets in it. So I am not able to add fixed “XLS sheet selectors” that point to the specific sheets, as in some runs, they may not even exist in an excel and the workflow will then run into void.
I’m going to research a bit more and share my findings if I find a solution to the problem.
