Write to Excel Template can't select sheets to write

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:

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

In the workflow data area of the demo is a spreadsheet containing the following “demo” sheet:

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:

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!

3 Likes