I know this topic was mentioned several times yet I think I need a different suggestion.
My workflow looks like this:
read data from files
transform
write data to a new excel file → file (append), sheet (overwrite)
formatting with XLS Nodes
Apply format
My issue: the user should be able to write some more data (manually) into a second sheet in the same file.
However everytime the flow executes I get the error message.
I could change my settings to: file (overwrite), sheet (overwrite)
But that would delete the data in the second sheet.
My only idea: read data from second sheet and append later on. However I would need to apply styling and so on…
My understanding from your post is that after the output xlsx has been manually modified by the user, you wish to then execute the workflow again, producing the same output xlsx but that with this sheet already existing, you get an error message " already contains style". Is that right?
Does the sheet that is entered manually reference the data in the other sheets that are generated by the workflow?
One thought is that at the start of the workflow you rename the existing file (if it exists) to a new name, and then the workflow creates the file under the usual name again, so the error doesn’t occur.
After this the manually entered sheet is copied from the old (renamed) file back to the newly created file.
I have a component that may be able to assist with copying the manually created sheet back into the new output xlsx.
From memory though, this component is KNIME version-sensitive because of changes to the underlying java libraries used, and I haven’t tried it with the latest versions.
Is that a workable idea? If it is, let me know how you get on the the component. If you run into difficulties, let me know what version of KNIME you are using, and if you are able to upload a small sample workflow I can try to assist with making the sheet duplicator work.
sorry for getting back so late - something else came up
Yes you got it right!
This second sheet does not reference data from the first sheet.
Thank you for providing your workflow!
Unfortunately I get the error message:
Contains one node with execution failure (Java Edit Variable #1)
Java Edit Variable #1: No installed version of “org.apache.poi” matched version range [4.1.1.v20200604-1524, 5.0.0).
No installed version of “org.apache.poi.ooxml” matched version range [4.1.1.v20220617-knime, 5.0.0).
My component was written using a previous version of KNIME and was built using the version of certain java libraries (Apache POI) that were shipped with KNIME at that time, so I had a feeling it may not work directly with the later version.
I have done some work recently (on other components) that give them the ability to auto-update (if possible) to the currently shipped libraries, although it cannot be totally guaranteed, so thanks for the feedback and I will take a look to see I can make it work for this component too.
But glad to see you managed to build a workable solution using python, and thanks for sharing your solution.
