Copying Excel sheets with header and formula to new sheets

Community Extensions
,
#1

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

1 Like
#2

For your reference, this is a simplified example of the worfklow
Excel Workbook - Copying sheets with a new name to another Workbook.knar (45.1 KB)

#3

Hi @RKrom,

sure will try to add this to the extension :slight_smile:
Sadly there was no standard copy a sheet to another workbook function availible in the POI library (had thought about this feature before :see_no_evil:).
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 :slight_smile:
So I can try to at least cover the content types you have in your template :stuck_out_tongue_winking_eye:

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

1 Like
#4

Hi @AnotherFraudUser,

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.

Thanks again!

Best regards,
RK

1 Like
#5

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

1 Like
#6

Hi @RKrom,

very basic node is created and available.
However still fighting with the Excel-shapes. Pictures are already copied… shapes not :see_no_evil:
Also open is the re-use of excel styles to reduce file size.

So might still take a bit until everything works as indended :sweat_smile:

2 Likes