Hii there, the XLS formatting node is failing on 5 th iteration…
Is there any solution for this .
Thanks in advance.
Hi @johnnybasha
Do you have a small sample workflow that can demonstrate how you are using the xls formatter and the problem you are having?
I have an excel sheet with 7 tabs in it
I have applied XLS formatting to all the sheets
When I execute the tab 5 formatting node it is failing.
When I execute the tab 5 sheet first then it is executing perfect.
@johnnybasha, When you say it is failing, are you getting an error message or is it just not applying the formatting? Also what formatting nodes are you using? How big is the spreadsheet in terms of numbers of rows on each sheet? And what KNIME version are you on?
I’ve not tried formatting 5 sheets on one xlsx so I don’t know if this is a known issue but the more information you can provide about it the easier it will be for somebody to help.
I’d definitely recommend uploading a demo workflow if you can but if that’s not possible then maybe at least a screenshot of how your workflow looks?
3 Likes
Please look at this work flow
I have resorted this for same 5 sheets 4 executed but last one is failing
1 Like
Hi @johnnybasha,
It’s a bit difficult to work out exactly what is going on from the screen shot. Is this in a loop, as I cannot see any loop nodes. Also where is the Sheet selector? By the way what version of KNIME are you using?Is there any chance you could upload the workflow (with dummy data) just so we can see the whole structure?
By the way, how complex are your formatting tags? Are they all operating on the sheet as a whole, or are you formatting specific small ranges?
Although not directly to do with your, I ask because I’ve been working on some components to make my life easier with formatting in general, and have today created a couple of new components based on some information in this thread, but handling looping in a slightly different way. I was getting mildly frustrated by how much “wiring” is required in a workflow to handle lots of formatting, so decided I’d “componentize” into my own “formatting templates”, especially as in general most of my spreadsheets are likely to use the same format! See my example demo workflow at, which shows formatting 20 tabs in a loop (just to see if it would work!).
If you are able to upload a demo workflow, I feel sure an extra few sets of eyes can help resolve your problem.
Hi @takbb
I cannot upload my workflow because I work in an cloud environment from which we can’t share anything…
I am using knime 4.2.2
I wll try to tell you what I am doing…
1.I have an excel sheet which contains 7 tabs in it.
2.Each tab i have designed in different workflow.
For 7 tabs i created 7 workflows in workflow group.
3.while applying formatting i have passed an flow variable from an excel writer(appender)
4.used excel sheet selector and at last formatting i have used formatting node.
5.each tab has different formatting to be done.
7.so i am pointing formatting node input and output to same file location.
8.it is executing only 2 3 sheets…
After that I get an error saying that formatting has been done …and could not do again
Hi @johnnybasha ,
When you say you are pointing the formatting node (presumably the XLS Formatter (apply) node) input and output to the same location… Have you tried specifying a new location for the output?
I’m wondering if that’s the problem as it isn’t designed for applying formatting to a spreadsheet that already contains formatting, and perhaps it gets tripped up/confused during its processing…
Tried, but still same error, after twoxls formatting node ,i think it is again reading the whole file from start ,so that the first tab in the file is formatted so it is getting an error i think.
Can u please suggest me a better approach for my workflow to implement formatting
Hi @johnnybasha , you appear to have accidentally marked one of your comments as the solution which is unfortunate as it may mean others miss it, thinking it is already resolved.
So you are still on KNIME 4.2.2? Oh my, that’s over 2 years old, and going to make it “fun” for me to to come up with any good answers, as it predates by nearly 6 months my first use of KNIME!! The first KNIME I used was 4.3, but there are considerable changes in the nodes in the more recent versions. I just downloaded a copy to see what nodes it has available, and was amazed by how much has changed, meaning it will be difficult to say whether something that works in a recent version will work as well in a version from September 2020. I don’t know how much the Continental (XLS Formatting) nodes have changed in this time.
The node names in your screenshot aren’t all that clear, and because it is only a partial view of what is going on it is difficult to get context of what is happening when.
What I would expect to occur is:
(1) In the latest KNIME, the spreadsheet would be written in it’s entirety with all N tabs written to FILE.xlsx by use of the Excel Writer. In KNIME 4.6, if the data is available, we can just write all tabs in a single call to an Excel Writer. Otherwise we can call an Excel Writer multiple times, with it appending the additional tabs as required.
In your version, the Excel Writer followed by multiple calls to Excel Sheet Appender is presumably necessary to achieve the same objective. I believe this is what you are doing.
(2) For each data set, you would generate a control table, and define the tags that you require.
(3) The first XLS Formatter node to call after generating a control table and tags is the XLS Sheet Selector which defines the sheet that the subsequent formatting will apply to. This gets called once and is followed by the XLS formatter nodes to provide the required formatting.
(4) The Formatter nodes would “chain” to each other in sequence for a given tab and then the final one would link to an XLS Format Merger which has one input per tab. You can also use multiple XLS Format Merger nodes chained together that effectively “funnel” the various format until you have a single output. That may be preferable to “declutter” the workflow a little. The final Format Merger would direct its output the the XLS Formatter (apply) node.
I still haven’t worked out whether you ARE using a loop somewhere. There aren’t any loop nodes present in your screen shot, so when you refer to iterations, are you referring actually to sequential processing. It sounds like it. If you are looping, you will need to utilise one of the techniques already mentioned for storing the formatting model between iterations in the loop.
I think though that you are not looping, and so hopefully the below will help…
Having downloaded 4.2.2, I have had to work at putting together a demo workflow for it (relearning some of the “old” ways!).
I have specifically NOT used a loop but instead have generated 10 tabs sequentially and then formatted the resulting sheet. I have also modified one of my components (The XLS Control Table Auto Tag Generator ) to work with Knime 4.2.2. This is here for demo purposes. It doesn’t do anything other than remove the need for me to work out how to create tags again! Life is too short! But this would be where your Tag generation node(s) would go, or you can make use of this generator instead if it is of use to you.
Even if you cannot “download” this workflow onto your cloud environment, presumably you have a PC somewhere where you could just install a local copy of Knime 4.2.2, to give this a try?
Excel Formatting- Knime 4.2 x.knwf (1.2 MB)
Without being able to see you workflow it really isn’t possible to tell you what you need to change to make it work, but maybe looking at this (which does update formats on all 10 tabs), might give some pointers.
3 Likes
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.