Hi @AnotherFraudUser ,
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.
Kind regards,
RK