Icon

kn_​example_​python_​excel_​drop_​down

Write excel data file with column with drop down list

Write excel data file with column with drop down list

What to edit in the Python code
https://forum.knime.com/t/create-a-column-with-drop-down-menu-list/73652/13?u=mlauber71

Write excel data file with column with drop down listhttps://forum.knime.com/t/write-excel-data-file-with-column-with-drop-down-list/24937/2?u=mlauber71 import knime.scripting.io as knio# This example script simply outputs the node's input table.# KNIO input - convert to pandas dataframeinput_table = knio.input_tables[0].to_pandas()import osimport openpyxlfrom openpyxl import load_workbookfrom openpyxl.worksheet.datavalidation import DataValidation# determine the path of the data foldervar_workflow_path = knio.flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )# create the full path of the excel filevar_path_excel_file = knio.flow_variables['v_path_excel_file'] + "data1.xlsx"print("var_path_excel_file : ", var_path_excel_file )# open the excel filewb = openpyxl.load_workbook(filename = var_path_excel_file )# determine the active data sheet by name# https://stackoverflow.com/questions/41556378/openpyxl-set-active-sheetfor s in range(len(wb.sheetnames)): if wb.sheetnames[s] == 'data': breakwb.active = s# the active sheet just found is the data sheet object wsws = wb.active # https://openpyxl.readthedocs.io/en/stable/validation.html# Create a data-validation object dv with list validationdv = DataValidation(type="list", formula1='"yes, no, not sure"', allow_blank=True)# add the validation object to the sheetws.add_data_validation(dv)# define the are where the validation object should be used# in this case the colum B - and as many lines as there are rowsv_numer_of_lines = knio.flow_variables['number_of_lines']v_data_validation_area = 'B2:B' + str(v_numer_of_lines)# add the validation object to the areadv.add(v_data_validation_area)# define the text for the head of column Bv_head_txt = "extra info"# assign the value to the TOP rowws['B1'] = v_head_txt# save the excel file and closewb.save(var_path_excel_file)wb.close()output_table = input_table.copy()# KNIO outputknio.output_tables[0] = knio.Table.from_pandas(output_table) What to edit in the Python codehttps://forum.knime.com/t/create-a-column-with-drop-down-menu-list/73652/13?u=mlauber71 v_path_excel_filecount linesnumber_of_linesnumber_of_linesdata1.xlsxsheet "data"data1.xlsxsheet "data"number_of_linesinsert data validationlocate and create/data/ folderwith absolute paths Java EditVariable (simple) Table Creator GroupBy Math Formula Column Rename Excel Writer Excel Reader Table Rowto Variable Variable toTable Row Python Script Collect LocalMetadata Write excel data file with column with drop down listhttps://forum.knime.com/t/write-excel-data-file-with-column-with-drop-down-list/24937/2?u=mlauber71 import knime.scripting.io as knio# This example script simply outputs the node's input table.# KNIO input - convert to pandas dataframeinput_table = knio.input_tables[0].to_pandas()import osimport openpyxlfrom openpyxl import load_workbookfrom openpyxl.worksheet.datavalidation import DataValidation# determine the path of the data foldervar_workflow_path = knio.flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )# create the full path of the excel filevar_path_excel_file = knio.flow_variables['v_path_excel_file'] + "data1.xlsx"print("var_path_excel_file : ", var_path_excel_file )# open the excel filewb = openpyxl.load_workbook(filename = var_path_excel_file )# determine the active data sheet by name# https://stackoverflow.com/questions/41556378/openpyxl-set-active-sheetfor s in range(len(wb.sheetnames)): if wb.sheetnames[s] == 'data': breakwb.active = s# the active sheet just found is the data sheet object wsws = wb.active # https://openpyxl.readthedocs.io/en/stable/validation.html# Create a data-validation object dv with list validationdv = DataValidation(type="list", formula1='"yes, no, not sure"', allow_blank=True)# add the validation object to the sheetws.add_data_validation(dv)# define the are where the validation object should be used# in this case the colum B - and as many lines as there are rowsv_numer_of_lines = knio.flow_variables['number_of_lines']v_data_validation_area = 'B2:B' + str(v_numer_of_lines)# add the validation object to the areadv.add(v_data_validation_area)# define the text for the head of column Bv_head_txt = "extra info"# assign the value to the TOP rowws['B1'] = v_head_txt# save the excel file and closewb.save(var_path_excel_file)wb.close()output_table = input_table.copy()# KNIO outputknio.output_tables[0] = knio.Table.from_pandas(output_table) What to edit in the Python codehttps://forum.knime.com/t/create-a-column-with-drop-down-menu-list/73652/13?u=mlauber71 v_path_excel_filecount linesnumber_of_linesnumber_of_linesdata1.xlsxsheet "data"data1.xlsxsheet "data"number_of_linesinsert data validationlocate and create/data/ folderwith absolute paths Java EditVariable (simple) Table Creator GroupBy Math Formula Column Rename Excel Writer Excel Reader Table Rowto Variable Variable toTable Row Python Script Collect LocalMetadata

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: 2020-06-30
Last update: 2024-04-18
Created with KNIME version: v4.7.7
Tags: pythonopenpyxl

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

Try NodePit Runner!