Formatting Loop Fails On Fifth Iteration

[willdolezal]

Hi all!

My workflow loops through XLSX files collecting certain data into a new table. Each table is appended as a new sheet to an XLS file. A unique styling table is made for each table in this process. The last step of the loop is applying the styling via the XLS Formatter node.

On the fifth loop through, the workflow returns the error:

ERROR XLS Formatter (apply) 0:38 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.

The 5th table has been appended to the XLS file but has no visible formatting. The workflow completes successfully when only 4 XLSX files are looped through.

My troubleshooting and adjusting of flow variables haven’t fixed the issue yet. If anyone has ideas on how to fix this, or could link to a similar post, it’d be appreciated!

Here is a link to my workflow:

Site Health Crawl – willdolezal

Attached is a screenshot of the workflow.

[Brotfahrer]

Hello @willdolezal

welcome to the forum!
Is this error a specific error with the excel file or with the fifth loop? Have you tried to use your fifth excel file with three others?

Greetings
Brotfahrer

[willdolezal]

Good question. The fail isn’t file specific. I’ve tried several combinations of passing along the files with the same results. A file that errors on the fifth loop will pass if on the 1st, 2nd, 3rd, or 4th loop.

[willdolezal]

Hey all,

Following back up on this. I’ve reduced the workflow and data complexity and am still receiving errors. Interestingly the error now occurs after 7 loops and not the previous 5. Any thoughts would be appreciated!

Test Data - 8 XLSX files with one sheet, each file has a column of strings and a column of numbers.
Here is the reduced workflow: Format Loop Test – KNIME Hub
and a screenshot of workflow is attached.

[Brotfahrer]

Hello @willdolezal,

did you try to run your loop with the “Do one loop step” mode shown in screenshot?

After each step check the result of the XLS Font Formatter Node with a right click on it. Can you see here something wrong?

Brotfahrer

[willdolezal]

Thanks for the recommendation @Brotfahrer. I checked the results of the XLS Font Formatter Node after each loop iteration. No difference was visible in any of the loop steps, including the failing one. The variables were also the same for each step. (Excluding the changing sheet name and iteration number.)

I also checked a few other nodes to see if any variation existed with no luck.

Any other recommendations for troubleshooting would be appreciated. Or an example workflow where a similar task is accomplished. Thanks again!

[ipazin]

Hello @willdolezal,

welcome to KNIME Community!
Do you see any other messages from log that could help you? Log is located at ...\knime-workspace\.metadata\knime. Make sure to set log file level to DEBUG in preferences to obtain all entires.

Additionally maybe someone from @Continental_KNIME_Extension_Developer team can help.

Br,
Ivan

[arbe]

Hi @willdolezal and @ipazin ,
this is expected and documented behavior. Only few existing styles are allowed before executing the Formatter Apply node: this is necessary to avoid style cluttering but needs some tolerance for e.g. datetime styles generated by KNIME. That’s probably why your first loop iterations were still functioning within the tolerance limit.
The solution is to follow this example workflow:

23_XLS_MultipleSheetsViaLoop – knime

Remember that you should always only execute the Formatter Apply node once per file. Before, you need to collect the styling information of all sheets.
Best regards
Arne

[willdolezal]

Thanks @arbe !! The example workflow was extremely helpful. Moving the Formatter Apply node to the end of workflow and adding the Model to Cell node, along with the Merge Ports component for restacking the style models was the solution. Screenshot attached.

(Kudos to @ipazin for bringing more attention to this.

)