Write to Excel Template can't select sheets to write

[quangdatitc]

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.

[takbb]

Hi @quangdatitc , and welcome to the KNIME community!

I may be misunderstanding what you are saying, but I can use it to write to a specific sheet in an existing xlsx file, in fact I couldn’t see how it could be used it without specifying a sheet.
I am however using KNIME 4.7 so I don’t know if this is different in the version for 4.6.

This is the basic configuration options page I see:

I haven’t used it much but if you are creating a new output file, and have multiple sheets in a template, then I imagine you’d call it the first time specifying the new file (using an existing file as the template file) as in the screenshot above, and then subsequently call it, using the newfile as the template and telling it to write into the template file:

Can you give more info about what it is you are trying to do, and what configuration options and results you are seeing. Maybe upload a demo workflow that exhibits the issue you are having?

(cc @AnotherFraudUser )

[AnotherFraudUser]

Hi @takbb,

as you said into which tab data shall be written has to be always selected.
This functionality should be the same since 4.3.

Node always needs a source template - and it will output the same number of tabs it has.

So @quangdatitc could you please provide an example workflow / screenshots of the issue?
At least how you describe it does not sound how it should work. In case something has to be changed, then i will look into the node

[denisfi]

Hi guys,

Just for not forget, all nodes can use the flow variables to be used as a input information, so if you put the sheet name into a variable and go to flow variables tab, you can set it there!

Just a tip… try and give us your result please.

Tks,

Denis

[quangdatitc]

Sincere thanks for your support and detailed responses. I may have misunderstood my process, and I apologize for the delayed reply. Yesterday, I couldn’t access my computer.

I can describe the process of creating a new file as follows:

• I have a list of sheets to create.
• My INPUT is a pre-formatted and color-coded file.
• The OUTPUT is a file with multiple sheets that can utilize the list of sheets as a variable (while maintaining the same formatting as the original file).
  
  

[takbb]

Thanks @quangdatitc for the additional information. I think I understand a little better what your problem is.

You have a single sheet in the template xlsx with formatting that you wish to have replicated across a number of newly created sheets in the output xlsx, and so you wish to be able to specify a different source and destination sheet.

I don’t think that is a use case that this node was designed for so I’m thinking of possible workarounds that could be developed to assist here.

To further our understanding…
Do you know at the start of the loop how many sheets you will want to create? Will they always just be named sequentially numerically (1, 2, 3… as in your screenshot) or might you have a list of pre-defined names that you know beforehand?

[quangdatitc]

@takbb
Thank for support.
I have a list of pre-defined names that i know beforehand. With Excel Writer i can chose sheet with variable, but this node i can’t do that…how can i append more sheets in new excel file?
Do you have any advice?

[iCFO]

I haven’t tried to pass a sheet name to this node via variable yet, so I can’t comment on that. However, this node is designed to make changes to an existing template file, so it will not “append” new sheets. You would need to create and format the sheet in the template file in order to be able to select it.

[quangdatitc]

That is the reason why I want this node to be updated with new features.

[iCFO]

I think you missed my point. If it only over writes values into existing pre-formatted sheets, then how could it append a new sheet? I have done things like this by combining the regular excel writer with formatting nodes for appending sheets, and using the write to template for inserting values into pre-templated sheets. Both can be used together on the same file as long as the write timings are in series.

[denisfi]

Hi @iCFO ,

Could you print or bring a dummy example here using excel and templates to be more clear for all users? It can help to understand better what you are saying… ok?

Tks,

Denis

[iCFO]

Excel formatting and use cases are too variable and labor intensive for me to try and create something generic based on a short problem description like this. He wont be able to apply it to his problem except in general principal.

I agree with the original poster that it would be cool to be able to “append” a new sheet by copying the format from an already existing sheet, but that capability doesn’t exist at this point. Unless there is a way to duplicate an existing excel sheet (with formatting intact) in KNIME to create a new one in the template file first, then the only option is the standard Excel Writer and some formatting nodes.

[takbb]

@quangdatitc , I believe I can think of a way to achieve what you require using a Java snippet. I envisage building a component that creates a new template xlsx based on your existing one but with the required set of duplicated sheets. This could then be used with your existing flow using the Write to Excel template. I’m not around today but if nobody has a better solution for you in the meantime I will see what i can put together later.

(Edit This is effectively what @iCFO suggests at the end of latest post)

[iCFO]

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

Copying Excel sheets with header and formula to new sheets

Hi @RKrom, the problem is most likely that this is not the current AF-version with the fix It should be the version from 2022.07.24 When I download a fresh version of knime 4.5.2 and then add extensions I get the correct version [grafik] Could you try to add the -clean option to your knime .ini [grafik] Sorry for the trouble - not sure why the version does not show up for you Yes the shape issue I fixed was at the end of the process - basically first it …

[AnotherFraudUser]

Hi @quangdatitc,

now I understand what you want.
With the write to template node that is not possible…and most likely will not be supported in the future as well.
The java library on which the extension is build on sadly does provide limited support to copy sheets between different workbooks, so adding that is quite a bit of work.

A somewhat passable sheet copy can be done with the node @iCFO referenced https://nodepit.com/node/org.AF.ExcelUtilities.CopyExcelSheet.CopyExcelSheetNodeFactory however it also as a limited functionality .
But do not plan to merge these two in the near future (sorry about that)

[takbb]

Hi @quangdatitc ,

I had a little talk with chatGPT 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:

XLSX Sheet Duplicator – takbb

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

Excel - clone worksheet – takbb

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):

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!

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:

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!

[quangdatitc]

Thank you everyone for your enthusiastic help. Well, I’m certain that there is no other option but to upgrade to the latest version, but due to some reasons, I am unable to upgrade at this time. Yesterday, I came up with a solution to create multiple Excel files and then use VBA to merge them into one file. Once again, thank you all very much.

[takbb]

Hi @quangdatitc , I just installed a copy of KNIME 4.6.5 and unfortunately it seems that the version of Apache POI (the java library this uses for interacting with xlsx files) that is bundled with that is 3.9, whereas in KNIME 4.7 it is version 4.1.1 and this does offer better support for XLSX files. I’ll update the documentation on the component to say it requires KNIME 4.7.

In trying to see if the code could be made to work with the earlier version of Apache POI, I then hit the limitations that @AnotherFraudUser mentioned in terms of the limited ability to directly clone worksheets, so unfortunately it does mean it won’t work in KNIME 4.6 and earlier, but the better news is that at least we can see that there is improved support in the later release which opens up more scope for the future.

I’m glad to hear you’ve found a solution albeit having to use VBA for your current version, but hopefully you will be able to move to the latest KNIME version at some point.