I have been trying to use the Write to excel Template node to write each table into respective sheets by using sheet names as flow variable but each and everytime I am only able to get the data in the last sheet as each time its getting overwritten rather than appending like in normal excel writer. Is there an alternative for this so that I can get the data from each loop in respective sheets appended.
Hi @giffinromeo , I presume that in the above screenshot, the Write to Excel Template node is being used totally independently of the Excel Writer and XLSFormatter (plus the other components)?
Take a look at this thread which I think discussed a similar use case re writing multiple sheets.
If that doesn’t give any ideas, then please can you put together a small minimal workflow that demonstrates what you are trying to do (and without any nodes which aren’t part of the problem), as it’s really difficult offering advice based simply on a screenshot.
yeah of course I would like to give a minimal workflow to demonstrate my use case. IN the workflow shown below I am trying to write data into a template file containing equal number of sheets as the number of loop (name of the sheets is passed through in the variable loop start). So in each loop I would like to fill the corresponding sheet in the template and creat a new excel file. Since we dont have an option to append it gets overwritten and results in a file with only data on the last sheet (last variable in the loop)
Hi @giffinromeo , I think I understand, but it would still be much better to upload the workflow that demonstrates the issue rather than simply a screenshot, as this then allows others to try it, and hopefully tweak it or build on it to help you.
Did you get a chance to read the above thread that I posted. I feel it would probably answer your problem, but without having an example to try it out, I am making assumptions here. Which version of KNIME are you using, as that may make a difference to whether the component in that other post would work.
I would generally advise against looping through to Write to Excel Template in order to write into the individual sheets unless you have an ultra strange use case…
These templates tend to evolve and require adjustment over time. Reviewing and editing will be much more difficult within a loop. I would recommend writing to sheets in series either by position number or name to save a ton of maintenance hassles. I only use the loop approach for multiple file outputs.
It looks like you are filtering the rows of a larger table in a loop in order to split up the table and write those split tables into every sheet? If so, then I recommend writing the full table to a single sheet and using array formulas to reference and filter the main table for each sheet. That is a much easier approach on the KNIME side, and allows for a more interactive dashboard style design in Excel as well. Often you can have a single sheet with some interactive settings replace a large number of rigid sheets.
yeah I forgot to upload it. Here is an example workflow.
example.knwf (16.6 KB)
And I had already gone through the thread you shared there and from my understanding I don’t think copying same template for different sheets work for me.
But what I require is to have individual sheets based upon a flow variable because at the end I would want this report to be modified based on each sheets separate.
If you are going to upload it for testing, then you will need to put the excel template file into the workflow data folder and reference it in the Write to Excel Template node using “Relative To” - “Current Workflow Data Area”. That way it will bundle up and be shared with the workflow for us to have access to it in order to test.
The answer could be as simple as making sure that the Template file already has every sheet name (or position number) already existing that you are attempting to feed through as a flow variable. If you attempt to write it by sheet name, then they would need to match up exactly to avoid any issues. This node will fail when a sheet name doesn’t exist in the template file. I am betting that this is the issue as it appears your sheet names are codes such as “Druck 1,9 A”. You can use the flow variable to write to the sheets by position number alternatively. This would allow you to have flexible sheet names as well.
example-working.knwf (542.6 KB)
Here is a basic working example with the correctly matching sheet names. The template file and output file are in the workflow’s data folder. I added one of my components that I use to quickly manually access the data folder or launch excel files there.
Here the template sheets have exact same names as the flow variables. But the problem for me is that I am not able to provide any instructions to write it as a new file because sheet selection is not available there (as you can see in the area with blue marked)
The workflow you shared after editing is having some issue in my Knime. Its showing missing node eventhough I have this extension installed. Even then I tried to search and install it again but not working.
Regarding storing the excel template into workflow, I dont know if its possible to store images and template as such because from what I have used only table can be used. Please help me on how we can do it.
@takbb were you able to understand the situation like I mentioned each sheet is different?
You are using the old depreciated version of the node, and need to download the newer one.
Yours will likely work as well once you install and replace it with this up to date version.
I tried it even to install it but doesnt go to the next page even after pressing next as shown in the image here.
What version of KNIME? Any organization level restrictions on extensions?
here is my version of KNIME
untill now there is no organisation level restrictions
this node should work fine since you’re above version 4.0 as long as it isn’t being blocked by company, admin restrictions. You could try all three ways to install notes and see if you could get one to work. Drag and drop, install extensions on the platform, or download the note and install it direct.
You may also play around with the Settings that show which notes are already installed and which nodes are compatible with your version to see if it shows that node as available for you.
I tried the first two option and I tried to uninstall and install it but no luck. Eventhough I have the node when I try to drag and drop it ask whether i need to install the extension resulting the same issue above (stuck with next button). The option to download the node I dont get it. How can I do it
