Hi all,
My workflow reads in an Excel workbook which has “read” sheet protection for all sheets, transforms one sheet of those a bit and overwrites the old sheet with the adjusted one in that workbook. But now, obviously the sheet protection for that specific sheet does not apply anymore. Is there any way to restore that? I tried the Excel Password Manager node but that one puts a password on the whole workbook which is not what I need.
Thanks a lot in advance!
BR,
Tim
@tstolle94 you can do this with the help of OpenPyxl wich is integrated in the bundled Python version.
from openpyxl import Workbook
from openpyxl.worksheet.protection import SheetProtection
# create a new workbook
wb = Workbook()
# get the active worksheet
ws = wb.active
# set the protection property to True
ws.protection.sheet = True
# save the workbook
wb.save('example.xlsx')
Hi @mlauber71,
thanks for the quick reply!
I am not familiar with OpenPyxl or Python in general at all, but tried to read the “code” for your two scenarios. The upper one apparently implements a workbook protection, the lower one a sheet protection as I understand it. But I cannot seem to see where I enter the password for the sheet protection in the lower scenario?! The same with your shorter example in this post that is probably customized to my question.
Thanks!
BR,
Tim
Hi @mlauber71,
I tried to implement the option of selecting the sheet protection password myself now based on the links that you also provided in the Python script node. Looks like this now for me:
And the workflow looks like this:
I assume that the imported path into the Excel write node has to be a full path. So if I want to write the Excel on my desktop, then “C:.…”. In your example I could not get my head around your collect local metadata node and what path in the end ends up in the Excel write node and then gets forwarded to the Python node.
When trying to execute the Python node then, I get this error message though:
Anyone knows how to proceed here?
Thanks!
BR,
Tim
@tstolle94 you can extract the path of the Excel file you have written in KNIME and provide the full path to Python.
The path of the file you saved can be converted to a Flow Variable
You can extract the absolute path from there for example:
Hi @mlauber71,
thanks for that, did not know about that one. 
Could you also please check my script in the python node that I posted? And maybe you have an idea why the error message in the python node pops up?
Thanks!
BR,
Tim
@tstolle94 you may want to check if you have set up the bundled Python environment. Also I have updated the workflow to incorporate to capture the File path and Sheet name you might have provided. Also you might want to check out the several options you have in securing an Excel file (password to open, to protect the structure, …) - also keep in mind the security might not be very strong so it is more a way to secure your file against accidental changes.
hi @mlauber71,
I was trying back and forth to get Python running as I am a total newbie to this. I understand now the topic of Python with different environments such as conda etc. and also technically how to set it up in Knime. But no matter whether I try it through the “manual” setting in KNIME or the conda environenment where I created a new conda environment as suggested by KNIME, I always get the error message now that the execution has failed due to no module being named “knime”.
I also installed then the package knime but this then gives me the error message “No module named ‘knime.scripting’; ‘knime’ is not a package”. This is getting really frustrating 
Please help! Thanks!
BR,
Tim
@tstolle94 as suggested you can just use the bundled python version. You will just have to install the necessary (KNIME) extension. You will not need the whole conda thing for this
@mlauber71 I have installed both the Python Integration and the Python Integration (Labs). I cannot upgrade to KNIME 4.7 as our server is not yet ready for that. I have selected and edited the paths to where Python is installed on my computer. So really dont know what else to do, sorry…
I mean, I saw somewhere that you can select the bundlet enviroment thing where there is currently the option of selecting “conda” and “manual” at the top of the window. But as I said, I think I installed everything for that… Or maybe that is just a function for KNIME 4.7.
@tstolle94 the integrated Python version is around since 4.6
Though OpenPyxl has only been integrated into the bundled version since 4.7 I think.
The other thing you could do is follow my article about the conda setup with Miniforge.
Hi @mlauber71,
I finally got it running, thanks for all the support so far! I adjusted your code a tiny bit and it worked as well
In addition to that, I wanted to create a code now that does pretty much the same, so putting a sheet protection on one sheet, but before doing that replacing one sheet within a file with an adjusted sheet, both having the same sheet name.
I tried my luck with this:
Could you maybe guide me correcting this one? That would basically make me finalise my use case
Thanks!
BR,
Tim
I don’t think one Excel file can have two sheets with the same name. You have the elements in the code
wb = workbook
ws = worksheet
You can create additional objects (the names are just there to make a distinction). I do not think you can just interact between objects from different workbooks but I have never tried.
You might want to plan out what you want to do. And if you wan to add another sheet you could have that in another KNIME node or do this via an imported table.
Hi @mlauber71,
sorry, I dont want to have two sheets in the same workbook. I want to have the sheet in workbook 1 (sheet1) replaced by the sheet in workbook2 (sheet2) (both have the same name for the sake of replacing it) and then apply the worksheet protection. We could also say that we could append the data of sheet2 to the data in sheet1 (with sheet1 being a blank sheet).
I agree, that I could do the replacement part with the Excel Writer node and then apply the worksheet protection with the code in Python you provided. But trust me when I say that this is the only way that I can get everything I need for my use case. Maybe you have read my other post about having issues with formatting an Excel sheet and append it to a preformatted Excel file.
The problem I explained in that post actually leads to the fact that I would need this workaround in Python as described to get it all done in my workflow, as mentioned already above.
Thanks!
BR,
Tim
@tstolle94 OK how about this.
This program is designed to apply the formatting from one Excel sheet (‘default_1’) to another sheet (‘default_2’) without copying the content. It takes the formatting from the header (first row) of ‘default_1’ and applies it to the header of ‘default_2’. Then, it applies the formatting from the first data row (second row) of ‘Sheet1’ to all other rows in ‘default_2’. The content of ‘default_2’ remains unchanged, only the formatting is updated.
The program is implemented using the openpyxl library in Python, which is widely used for handling Excel files. It provides various functions and classes for working with Excel files, such as loading workbooks, accessing sheets, iterating through rows and columns, and modifying cell styles and formatting.
Built into a KNIME workflow. You can switch out the names an path obviously:
“default_1” has the formats you want for your header and the program will apply them to the header of “default_2” and all the lines it has
The format would then be applied to all lines (you could autosize when exporting from KNIME or later)
The other code would just have a sheet that has full formats and transfer that to the whole other sheet which data has been written out from KNIME.
And yes, the code has mostly been written by ChatGPT. So if you want to modify it there are also options to do that.
I unfortunately need to focus on other topics for the moment (probably until beginning of May) and cannot continue on that specific Use Case, but checked your code and seems like this will help me a lot. So thanks again for all your support on this, I hope I do not have to bother you again, even when I get back to it in May. 
Have a good one!
BR,
Tim
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
