A while ago you’ve helped me out with an update of the “Write To Excel Template” (by AF Utilities) and I once more would like to ask you for a favor.
Currently I’m developing a WebPortal workflow which is able to create one Excel file which contains an x number of sheets. This Excel file will be created from an Excel template file which contains an empty data sheet only containing some header information fed by formulas (1) and a parameter sheet which contain parameters filled by the workflow used for the header information (2). This sheet template contains a colored layout, company images, dates and a set of parameters coming from the KNIME workflow. However, I’ve experienced some issues with copying these two template sheets between Excel workbooks.
The process I’ve created so far is using the following nodes are used:
Write To Excel Template
This node will copy the Excel template to a new output file. The new output file will include a dataset of
parameters on a sheet called “parameters”. This parameter sheet is used to update the header information for the “empty sheet” using basic formulas (e.g. =‘parameters’!B2). After the Excel template is copied to a new location the used formulas are correctly updated and point to the sheet “parameter” of the newly created file as expected.
Excel - Rename Worksheets
The newly created file now contains a sheet with updated header information which must be copied to any newly created sheet (the number of sheets will dynamically change). The only way to copy Excel sheets to an existing Excel Workbook I found so far is with Python. However, the linked component cannot copy an Excel sheet from an Excel Workbook as a new sheet in the Excel Workbook itself, as the source and target Excel Workbook must be different. As a result the formulas on the copied Excel sheet (target Excel Workbook) will point to the Excel sheet of the source Excel Workbook. This is causing issues with the header formulas and thus this method cannot be used (so far). Preferably I can replace this Python coding with a new feature within the “Write To Excel Template” node.
Excel Writer node
Furthermore the data for each sheet can be appended with the “Excel Writer” node.
This node appends the data from the KNIME workflow below the header data as expected.
Currently the “Write To Excel Template” node can copy an existing sheet to a new file. Where the sheet name in the new file is identical to the sheet name of the input file.
My question: Would it be possible to add a feature in the “Write To Excel Template” to copy existing sheets (existing option “Sheet Name” within the “Template Sheet Name Selection” section) to the output file with a different sheet name (new option in within the “Output File Selection” section)?
My goal is to loop through a list of new sheet names and create these sheets dynamically with the use of the “Write To Excel Template” node, where every new sheet is a copy of the sheet. Afterwards I can use the existing “Excel Writer” node to append data in the existing sheets.
sure will try to add this to the extension
Sadly there was no standard copy a sheet to another workbook function availible in the POI library (had thought about this feature before ).
So I would have to add a copy logic for each different content in the sheet: formats, values, borders, pictures,floating pictures, hidden status,…
Could you provide an example file with the layouts/contents you need? Just replace texts/pictures with dummys if you want to hide them
So I can try to at least cover the content types you have in your template
However I am currently moving without access to my pc - so there will be a delay of 1-2 weeks for me to get to this
Thank you so much for your quick response. It would be awesome to have this feature included, so your help is much appreciated. For now I will use another user unfriendly workaround which I will replace as soon as the new feature is ready.
In the workflow I’ve shared in my previous comment there is an Excel template included. It is very simple with few formulas, an image and few text objects. I will prepare a more detailed version for you and reply on this topic either Friday or early next week.
Hi @RKrom
maybe a quick update on the timeline.
There was a bit of a delay due to non-working internet in my new appartement.
However this weekend I should be able to make the changes
very basic node is created and available.
However still fighting with the Excel-shapes. Pictures are already copied… shapes not
Also open is the re-use of excel styles to reduce file size.
So might still take a bit until everything works as indended
I’ve tested your node with a template containing only data (no formatting), but I get the following error: “Execute failed: Reason: Error while writing null”. I’m not sure what is causing the issue.
In the attachment you will find the workflow containing a simple Excel file, the “Write to Excel Template” node and the “Copy Excel Sheet” node of AF Utilities.
Thanks for your response. I understand my request is not as easy as I assumed myself, sorry for that.
When I opened my KNIME this morning I was not able to see an update on the AF Utilities nodes. Is this correct? For me the test workflow still gives error. I will keep an eye on this topic in case you need me to test something with you.
just checked with a clean 4.5 installation and the update should be available
Are you on the lastest version of 4.5?
At least on 4.5.1 it does show up and works
I’m using 4.5.2 and just re-installed AF Utility Nodes (1.0.0.v202206192051).
Unfortunately the same error remains:
Execute failed: Reason: Error while writing null
I also tested the “Copy Excel Sheet” with a different file (bigger and including colored text formatting) but after 1 minute (slowly increasing the progress it crashed with the same error:
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
Could you try to add the -clean option to your knime .ini
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 copies all the data and formats from the template workbook to the new one (which takes time with large workbooks) and afterwards it copies all the pictures/shapes.
Here there was an issue that the process expected at least 1 image in the sheet
Most likely if you add any picture to your template sheet you copy then it works even with the first version
The issue was between my seat and the keyboard. My knime.log was similarly set as yours, so I checked the Available Software Sites and I missed a small detail… Anyway… now it works.
I did a test on a big Excel sheet with many formatted cells and few thousand lines of data (no images yet). So far it looks very perfect. Tomorrow I’ll perform some additional testing. Thanks a lot for your assistance. This is already very helpful for me.
).
