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.
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
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.
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:
@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.
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
@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.
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.
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.
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:
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.