Read an Excel "Template" file and dynamically output to new file with multiple sheets while retaining Template formatting

I am attempting to design a workflow in which I read in an excel “template” file and then output to a new file containing multiple sheets dynamically named. I was able to achieve this process but there’s one hiccup. The template file that I read in has formatting such as cells with borders and merged cells. When this file is read into KNIME, the formatting disappears. As a result, when the table is written to the new file, it contains the same information but completely unformatted.

I’m wondering if it is possible to read in the template file and output a new file with multiple dynamically named sheets containing the same format as the template file.

My current solution to this problem is having a template file with multiple sheets already named based on my data. For example, my data has 4 groups, “A”, “B”, “C”, and “D”. I then use an excel cell updater node to dynamically populate the template based on sheet name. However, if I run the workflow and a new group “E” pulls through, then I have to go to my template file and add a new sheet “E”. This can become extremely time consuming when there are lots of groups to keep track of.

I have my doubts of a solution due to the fact that the excel writer node needs a table input and any formatting is removed when KNIME reads in the template file. However, the KNIME community continues to impress me with solutions so I figured I’d give it a shot.

@JGebhard I think I built something similar once with the help of openPyxl (integrated into the KNIME Python extensions). Maybe you can have a look:

Hi Markus,
I’m hoping that will work. It’s going to take some time to digest and understand the process, but thank you for attaching that!

@JGebhard yes there might have to be some adaptions. I used ChatGPT to let it write and modify the code so maybe you can take it as a start.

Also there are KNIME ‘native’ tools to help you with that, they might need some more planning. I would have to think about something.

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