Hi everybody, Currently, I am using Knime version 4.6.0. I have a question that I hope someone can answer. The “Write to Excel Template” node is great for preserving the Excel format, but it is not as effective as expected when it doesn’t allow selecting the sheet to write to. For example, I have an Excel file with multiple sheets, and I want to use the “Write to Excel Template” node to output a new preformatted Excel file with the same number of sheets that were read in. I hope the next version of this node will be upgraded and improved. If anyone has any ideas, please let me know. Thanks a lot.

Hi @quangdatitc , I had a little talk with chatGPT :wink: which these days is my go-to code writer to produce the base code which I then tweak… Anyway together we’ve come up with the following: [image] XLSX Sheet Duplicator – takbb Takes a single sheet from a source Excel XLSX file and clones it to a new XLSX file. Multiple named destination sheets may be supplied in which case the origin… You give this component the absolute location string of an existing xlsx file, along with the name of the sheet contained within it that you want to clone. You also supply the location of an output file, and a comma-separated list of sheet names, and it will (in theory!) clone the sheet to the output destination. See the following demo workflow [image] Excel - clone worksheet – takbb This demo workflow uses a sample XLSX file in the workflow's data folder and clones the "demo" sheet into three new sheets "Sales", "Contracts" and "Invoices" … [image] In the workflow data area of the demo is a spreadsheet containing the following “demo” sheet: [image] After execution, it will create the file c:\temp\my-demo-sheet.xlsx (For the demo, you can change the destination in the variable creator): [image] This sheet contains the following cloned sheets: [image] As @AnotherFraudUser points out, there may be limitations in what can be cloned, although for the demo it has cloned foreground and background colours, plus an orange rectangle. Give it a try and see if it can help. I am not overly familiar with the Apache POI java api that this code uses and I don’t know all its limitations. It is possible that some new features have been recently, or that I have simply not spent long enough with it to discover all the limitations, which is very possible! :slight_smile: If it does what you need, you can use this to initially clone the template into a new output file which you would then use within your loop where each sheet can be updated by the Write to Excel Template node. The options on the component are hopefully relatively straightforward to understand. In the workflow, I am setting all of these using flow variables: [image] Let us know how you get on. NB Please treat this component as experimental, and make sure you test that it does what you need, but if it works, that’s great!

Write to Excel Template can't select sheets to write

KNIME Analytics Platform

iCFO May 29, 2023, 2:28pm 14

Take a look at this thread as well. It looks like @AnotherFraudUser may have created a copy excel sheet for this use case already.

3 Likes