I have been trying to use the XLS formatter to do some modifications in each excel sheets. But this works for all the iteration just before the last one. Only for the last one I am recieving the error:
Execute failed: The input file already contains formatting styles. This is currently unsupportet in our extension beyond a degree that KNIME’s XLS Writer and sheet Appender nodes would utilize.
As mention as result I see all the changes until the last sheet where the changes are not there because its not completed (the iteration)
It’s a bit difficult to work out exactly what you are doing from just looking at the screenshot. A lot will depend on the configuration and flow variables which we cannot see.
Are you attempting to write to different sheets within the same XLSX and then apply formatting to each sheet within the loop, or is each sheet in a different XLSX?
I can make the error occur in a demo workflow if I am writing to multiple sheets in the same XLSX and have the XLS Formatter (apply) node attempting to write formatting to the same file that it is reading. (i.e. the source and destination files are the same). This is not allowed as it needs to write to a file that does not already contain formatting.
Can you confirm if this is what you are attempting to do?
I am formatting the individual sheets in an XLSX file wich I choose using the flow variable inserted into the XLS Sheet selector. And its in a loop that means in every iteration only one sheet is being formatted. Additionally in the above I a m writting the excel data in the same loop and just to make sure I have connected the both (XLS Formatter and Excel Writer) so that only after its done written it gets formatted.
And what I am wondering is its working till the last sheet (last iteration). Only in the last sheet its not formatted and on the rest its clearly formatted.
As in the image I have two loop start (group loop starting with the same set of groups).I am using 4.5.2 version. I will try creating a component but I dont understand how it helps. Regarding the debug mode I dont really know what you mean.
I have made some changes such as instead of two loop start I am using a tablw row to variable loop start to control two parallel flows. Also I thought it was contridicting the flow variables from the two group loop. But even now I am facing the same issue (error)
What does the configuration of you XLS Formatter (Apply) node look like? Can you show a screenshot of it?
Are you attempting to write to the same xlsx that you are using as you are using as the source sheet?
Ideally I think you would want to put the XLS Formatter (Apply) outside of the loop. This will probably have you wondering how that can be achieved (I have a method in mind) but before I suggest anything, I still need to better understand your flow. I also don’t currently understand why you only get the error on the final iteration. I would expect it to error before that!
Yeah its the same. Iam writing the excel data just before using excel writer and then I am loading it again and formatting it. By the way I tried doing it outside the loop as shown below, but it results in the same error.
Hi @giffinromeo , I think I have a theory on why it errors when it does. I believe it’s a timing thing. In theory it should error on applying formatting to the second sheet, since at this point your file would already contain formatting. (The XLS Formatter (apply) is not designed to write to an xlsx file that already contains formatting).
If you were to have a loop with more sheets, you’d find it doesn’t error on the last sheet, but at some point before that. I think it is likely to be the time taken for the sheets to actually be written to xlsx file plays a part, and strangely the xls formatter is able to write all of the models up to that point. I downloaded KNIME 4.5.2 and wrote a demo workflow which failed as yours did when I had a couple of sheets, but when I increased the number of sheets to be written, it actually failed at around sheet 3… so I think it is simply down to how much of the xlsx has been physically written out to disk by the time the xlsx formatter attempts to do its thing. (This is just a theory but I think it’s likely to be on the right lines).
So how to make it work. I have had a similar question in the past about how to apply formatting that is generated in a loop. The way that I found to make it work is to get the formatting model that has been created inside the loop, out of the loop. Of course this “cannot be done”, since branches are not allowed to leave loops… so… I had to find a way of doing it anyway!
Attached is a demo workflow. The first half contains a demonstration of the problem you are having. Don’t worry about the additional “takbb” component it contains (that’s there to make it easier for me to demo).
In this demo, it attempts to create an xlsx of 10 sheets. It fails at around iteration 3 on my pc.
… is an alternative solution, making use of the following components:
and
(KNIME will tell you that these components were written in a later version of KNIME, but I have tried them out and they seem to work ok with KNIME 4.5.)
What you do is add them into your workflow and move the XLS Formatter (Apply) node outside of the loop.
I used the components you gave its working without any problem. It was so funny I had 8 sheets and only on the seventh sheet I was recieving this error. I tried changing the order of sheets even then I was recieving it again on the seventh. But like I said now its working fine.
