Copying Excel sheets with header and formula to new sheets

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

2 Likes

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)

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

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

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:

2 Likes

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:

4 Likes

Hi @AnotherFraudUser,

First of all sorry for the late response…

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.

Copy Excel Sheet AF.knar (18.6 KB)

I will be available for troubleshooting this week and next week. Thanks!

1 Like

Hi @RKrom,

Sorry for the trouble.
Will try to get this fixed in the next days. :see_no_evil:

2 Likes

Hi @RKrom,

I at least fixed the null issue.
(bug appeared when there were no shapes/pictures in the source file)
At least for me your example now works :slight_smile:

However I am still fighting with coping shapes (pictures work fine)
As well still there is an issue with not re-using cell styles.

Might still take a bit until everything is resolved :see_no_evil:

3 Likes

Hi @AnotherFraudUser,

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.

1 Like

Hi @RKrom,

which version of knime are you using?
4.5/4.6? :thinking:

No worries will get this resolved sooner or later :slight_smile:

Hi @AnotherFraudUser ,

We are still on 4.5 as we still have some work to do before we can upgrade to 4.6

1 Like

Hi @RKrom,

just checked with a clean 4.5 installation and the update should be available :frowning:
Are you on the lastest version of 4.5?
At least on 4.5.1 it does show up and works :thinking:

Hi @AnotherFraudUser,

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:

image
image

Execute failed: Reason: Error while writing null

Could it be some writing issue?

Hi @RKrom,

the problem is most likely that this is not the current AF-version with the fix :frowning:
It should be the version from 2022.07.24 :thinking:

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

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 :see_no_evil:
Most likely if you add any picture to your template sheet you copy then it works even with the first version :frowning:

1 Like

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.

RKrom

2 Likes