Icon

kn_​example_​python_​excel_​password_​protection

KNIME - use openpyxl to set password on Excel, read-protect a sheet, freeze a top row and add a filter (and hind and unhide your worksheet)

KNIME - use openpyxl to set password on Excel, read-protect a sheet, freeze a top row and add a filter (and hind and unhide your worksheet)

There are limited ways to protect an Excel file using the Python package [ openpyxl](https://openpyxl.readthedocs.io/en/stable/protection.html). But it would only offer protection from some changes (not from opening the file) and the password would be stored hashed - so no real hurdle there.

In general I think most password protections for Office files are more there to prevent accidental editing to keep a fixed structure - but they are insufficient for real protection




https://forum.knime.com/t/add-password-in-knime-on-excel-file-output/16717/3?u=mlauber71https://openpyxl.readthedocs.io/en/stable/protection.html # set a password "a12345" to an excel sheet with several settingsimport knime.scripting.io as knioimport osfrom openpyxl import load_workbookfrom openpyxl.formula.translate import Translatorfrom openpyxl import Workbookfrom openpyxl.workbook.protection import WorkbookProtectionvar_workflow_path = knio.flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = knio.flow_variables['v_path_excel_file']print("var_path_excel_file : ", var_path_excel_file )# load the Excel workbookwb = load_workbook(filename = var_path_excel_file)# https://openpyxl.readthedocs.io/en/stable/protection.html# set a passwordwb.security = WorkbookProtection(workbookPassword='a12345', revisionsPassword = 'a12345', lockWindows = True, lockStructure = True, lockRevision = True)wb.save(var_path_excel_file)wb.close() KNIME - use openpyxl to set password on Excel, read-protect a sheet, freeze a top row and add a filter (and hind and unhide your worksheet) # protect excel sheet, freez the top row and first column and set a drop-down menueimport knime.scripting.io as knioimport osimport openpyxlfrom openpyxl import load_workbookfrom openpyxl.formula.translate import Translatorfrom openpyxl import Workbookfrom openpyxl.workbook.protection import WorkbookProtectionfrom openpyxl.worksheet.protection import SheetProtectionvar_workflow_path = knio.flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = knio.flow_variables['v_path_excel_file']print("var_path_excel_file : ", var_path_excel_file )# load the workbookwb = load_workbook(filename = var_path_excel_file)# activate the worksheetws = wb["default_1"]# activate the ws 'default_1'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = s# https://openpyxl.readthedocs.io/en/stable/protection.htmlprint(openpyxl.__version__)# set the protection property to Truews.protection.sheet = True# freeze the first rowws.freeze_panes = 'A2'# activate filters/drop-down menusws.auto_filter.ref = ws.dimensions# set the protection property to True# ws.protection = SheetProtection(locked=True)wb.save(var_path_excel_file)wb.close() import knime.scripting.io as knioimport osimport openpyxlfrom openpyxl import load_workbookfrom openpyxl.formula.translate import Translatorfrom openpyxl import Workbookfrom openpyxl.workbook.protection import WorkbookProtectionfrom openpyxl.worksheet.protection import SheetProtectionvar_path_excel_file = knio.flow_variables['file_path']print("var_path_excel_file : ", var_path_excel_file )# load the workbookwb = load_workbook(filename = var_path_excel_file)# extract the excel sheet namevar_excel_sheet = knio.flow_variables['v_sheet_name']var_excel_sheet = var_excel_sheet[0]# activate the worksheetws = wb[var_excel_sheet]# activate the ws 'default_1'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = s# https://openpyxl.readthedocs.io/en/stable/protection.htmlprint(openpyxl.__version__)# set the protection property to Truews.protection.sheet = Truews.protection = SheetProtection(password='mypassword')# freeze the first rowws.freeze_panes = 'A2'# activate filters/drop-down menusws.auto_filter.ref = ws.dimensions# set the workbook protection to lock changeswb.security = WorkbookProtection(workbookPassword='mypassword')wb.save(var_path_excel_file)wb.close() extract the file and sheet name you save the data withhttps://forum.knime.com/t/add-read-sheet-protection-to-excel-file/63860/5?u=mlauber71 Options to secure the Excel WorkbookworkbookPassword: This sets a password that is required to open the workbook. In this example, the password is 'a12345'.revisionsPassword: This sets a password that is required to track changes to the workbook. If this password is set, users won't be able to makechanges to the workbook without entering the password, and changes made to the workbook will be tracked with the user's name.lockWindows: This option prevents users from moving or resizing windows in the workbook.lockStructure: This option prevents users from adding or deleting sheets in the workbook.lockRevision: This option prevents users from turning off change tracking in the workbook. extract the file and sheet name you save the data withhttps://forum.knime.com/t/add-read-sheet-protection-to-excel-file/63860/5?u=mlauber71 import knime.scripting.io as knioimport osimport openpyxlfrom openpyxl import load_workbookvar_path_excel_file = knio.flow_variables['file_path']print("var_path_excel_file : ", var_path_excel_file )# load the workbookwb = load_workbook(filename = var_path_excel_file)# extract the excel sheet namevar_excel_sheet = knio.flow_variables['v_sheet_name_2']var_excel_sheet = var_excel_sheet[0]# activate the worksheetws = wb[var_excel_sheet]# activate the ws 'default_1'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = s# Hide the sheetws.sheet_state = 'hidden'wb.save(var_path_excel_file)wb.close() import knime.scripting.io as knioimport osimport openpyxlfrom openpyxl import load_workbookvar_path_excel_file = knio.flow_variables['file_path']print("var_path_excel_file : ", var_path_excel_file )# load the workbookwb = load_workbook(filename = var_path_excel_file)# extract the excel sheet namevar_excel_sheet = knio.flow_variables['v_sheet_name_2']var_excel_sheet = var_excel_sheet[0]# activate the worksheetws = wb[var_excel_sheet]# activate the ws 'default_1'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = s# Hide the sheetws.sheet_state = 'visible'# create a new Excel File pathvar_new_excel_file_path = knio.flow_variables['context.workflow.data-path'] + knio.flow_variables['file_name'] + "_unhide." +knio.flow_variables['file_extension']wb.save(var_new_excel_file_path)wb.close() v_file_pathto stringv_path_excel_filedemo1.xlsxlocate and create/data/ folderwith absolute pathsdemo1.xlsxv_path_*demo 1demo2.xlsxdemo 2v_path_*v_path_excel_filedemo2.xlsxdemo3.xlsxdemo 3demo 4hide an Excel Sheetdemo4.xlsxv_file_pathto stringdemo4.xlsxdemo 4aun-hide an Excel Sheet Path to String(Variable) URL to FilePath (Variable) Data Generator Java EditVariable (simple) Collect LocalMetadata Excel Writer String to Path(Variable) Python Script Excel Writer Python Script String to Path(Variable) Java EditVariable (simple) Excel Writer Python Script URL to FilePath (Variable) Python Script Excel Writer Path to String(Variable) Excel Writer Python Script https://forum.knime.com/t/add-password-in-knime-on-excel-file-output/16717/3?u=mlauber71https://openpyxl.readthedocs.io/en/stable/protection.html # set a password "a12345" to an excel sheet with several settingsimport knime.scripting.io as knioimport osfrom openpyxl import load_workbookfrom openpyxl.formula.translate import Translatorfrom openpyxl import Workbookfrom openpyxl.workbook.protection import WorkbookProtectionvar_workflow_path = knio.flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = knio.flow_variables['v_path_excel_file']print("var_path_excel_file : ", var_path_excel_file )# load the Excel workbookwb = load_workbook(filename = var_path_excel_file)# https://openpyxl.readthedocs.io/en/stable/protection.html# set a passwordwb.security = WorkbookProtection(workbookPassword='a12345', revisionsPassword = 'a12345', lockWindows = True, lockStructure = True, lockRevision = True)wb.save(var_path_excel_file)wb.close() KNIME - use openpyxl to set password on Excel, read-protect a sheet, freeze a top row and add a filter (and hind and unhide your worksheet) # protect excel sheet, freez the top row and first column and set a drop-down menueimport knime.scripting.io as knioimport osimport openpyxlfrom openpyxl import load_workbookfrom openpyxl.formula.translate import Translatorfrom openpyxl import Workbookfrom openpyxl.workbook.protection import WorkbookProtectionfrom openpyxl.worksheet.protection import SheetProtectionvar_workflow_path = knio.flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = knio.flow_variables['v_path_excel_file']print("var_path_excel_file : ", var_path_excel_file )# load the workbookwb = load_workbook(filename = var_path_excel_file)# activate the worksheetws = wb["default_1"]# activate the ws 'default_1'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = s# https://openpyxl.readthedocs.io/en/stable/protection.htmlprint(openpyxl.__version__)# set the protection property to Truews.protection.sheet = True# freeze the first rowws.freeze_panes = 'A2'# activate filters/drop-down menusws.auto_filter.ref = ws.dimensions# set the protection property to True# ws.protection = SheetProtection(locked=True)wb.save(var_path_excel_file)wb.close() import knime.scripting.io as knioimport osimport openpyxlfrom openpyxl import load_workbookfrom openpyxl.formula.translate import Translatorfrom openpyxl import Workbookfrom openpyxl.workbook.protection import WorkbookProtectionfrom openpyxl.worksheet.protection import SheetProtectionvar_path_excel_file = knio.flow_variables['file_path']print("var_path_excel_file : ", var_path_excel_file )# load the workbookwb = load_workbook(filename = var_path_excel_file)# extract the excel sheet namevar_excel_sheet = knio.flow_variables['v_sheet_name']var_excel_sheet = var_excel_sheet[0]# activate the worksheetws = wb[var_excel_sheet]# activate the ws 'default_1'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = s# https://openpyxl.readthedocs.io/en/stable/protection.htmlprint(openpyxl.__version__)# set the protection property to Truews.protection.sheet = Truews.protection = SheetProtection(password='mypassword')# freeze the first rowws.freeze_panes = 'A2'# activate filters/drop-down menusws.auto_filter.ref = ws.dimensions# set the workbook protection to lock changeswb.security = WorkbookProtection(workbookPassword='mypassword')wb.save(var_path_excel_file)wb.close() extract the file and sheet name you save the data withhttps://forum.knime.com/t/add-read-sheet-protection-to-excel-file/63860/5?u=mlauber71 Options to secure the Excel WorkbookworkbookPassword: This sets a password that is required to open the workbook. In this example, the password is 'a12345'.revisionsPassword: This sets a password that is required to track changes to the workbook. If this password is set, users won't be able to makechanges to the workbook without entering the password, and changes made to the workbook will be tracked with the user's name.lockWindows: This option prevents users from moving or resizing windows in the workbook.lockStructure: This option prevents users from adding or deleting sheets in the workbook.lockRevision: This option prevents users from turning off change tracking in the workbook. extract the file and sheet name you save the data withhttps://forum.knime.com/t/add-read-sheet-protection-to-excel-file/63860/5?u=mlauber71 import knime.scripting.io as knioimport osimport openpyxlfrom openpyxl import load_workbookvar_path_excel_file = knio.flow_variables['file_path']print("var_path_excel_file : ", var_path_excel_file )# load the workbookwb = load_workbook(filename = var_path_excel_file)# extract the excel sheet namevar_excel_sheet = knio.flow_variables['v_sheet_name_2']var_excel_sheet = var_excel_sheet[0]# activate the worksheetws = wb[var_excel_sheet]# activate the ws 'default_1'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = s# Hide the sheetws.sheet_state = 'hidden'wb.save(var_path_excel_file)wb.close() import knime.scripting.io as knioimport osimport openpyxlfrom openpyxl import load_workbookvar_path_excel_file = knio.flow_variables['file_path']print("var_path_excel_file : ", var_path_excel_file )# load the workbookwb = load_workbook(filename = var_path_excel_file)# extract the excel sheet namevar_excel_sheet = knio.flow_variables['v_sheet_name_2']var_excel_sheet = var_excel_sheet[0]# activate the worksheetws = wb[var_excel_sheet]# activate the ws 'default_1'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = s# Hide the sheetws.sheet_state = 'visible'# create a new Excel File pathvar_new_excel_file_path = knio.flow_variables['context.workflow.data-path'] + knio.flow_variables['file_name'] + "_unhide." +knio.flow_variables['file_extension']wb.save(var_new_excel_file_path)wb.close() v_file_pathto stringv_path_excel_filedemo1.xlsxlocate and create/data/ folderwith absolute pathsdemo1.xlsxv_path_*demo 1demo2.xlsxdemo 2v_path_*v_path_excel_filedemo2.xlsxdemo3.xlsxdemo 3demo 4hide an Excel Sheetdemo4.xlsxv_file_pathto stringdemo4.xlsxdemo 4aun-hide an Excel SheetPath to String(Variable) URL to FilePath (Variable) Data Generator Java EditVariable (simple) Collect LocalMetadata Excel Writer String to Path(Variable) Python Script Excel Writer Python Script String to Path(Variable) Java EditVariable (simple) Excel Writer Python Script URL to FilePath (Variable) Python Script Excel Writer Path to String(Variable) Excel Writer Python Script

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: m_lauber
Created at: 2018-11-23
On NodePit since: 2019-11-12
Last update: 2024-04-25
Created with KNIME version: v4.7.2
Tags: excelpasswordknimesavepythonopenpyxlhideunhidevisiblehidden

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!