Appending a template to many excel files

Hello everyone,

I could use some help on a project I’m working on, I’m using Excel files as entires, I have a big DB on input that i split based on a column with a loop, then I create a directory that contains the output excel files for each “category” on that column.
Now I wanna move to the next step, which is to create a template file with 2 sheets, one called “Data” and the other “Graph results”, the idea would be that on the graph sheet, I would take data from the “Data” sheet.
What I’m trying to accomplish through knime is that everytime I would take the template file, overwrite the data sheet and create and rename the new file based on a column, it’s the same idea as in my first workflow, but I cant figure out how to do this using only 1 “template” file.

Can u please give me some hints to do this?

Thank you.

Hi @Groumayo,

To achieve this, you first have to create a template with a dynamic named range for the input data that you use to generate the plots in the Graph results sheet. Follow Method 1 of https://support.microsoft.com/en-us/help/183446/how-to-use-defined-names-to-automatically-update-a-chart-range-in-exce to achieve that. You can also take a look at my template: template.xlsx (14.4 KB)

The trick in the workflow then is to create a copy of the template in a temporary directory and use an Excel Sheet Appender (XLS) to replace the Data sheet in the template. As a last step copy the file with the replaced sheet to the desired location. See 11863.knwf (35.4 KB) for an example that works together with the aforementioned template. You will have to adapt your paths in the two Table Creator nodes.

Together with the workflow that you have already shown, you should be able to tackle this.

Best,
Stefan

3 Likes

Hello @stelfrich,

Much appreciated, this solved my problem, I have even used .xlsm files to convert to pdf later on.
Thank you very much!

G.A

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.