Icon

kn_​python_​excel_​manipulate

Manipulate Excel files in KNIME with Python openPyXL

Demonstrates several Python based manipulations for Excel

https://forum.knime.com/t/enabling-hyperlink-in-excel-through-knime/17109/4?u=mlauber71https://forum.knime.com/t/manipulate-tabs-inside-an-excel/14492Extended from:https://forum.knime.com/t/write-data-to-excel-sheet-on-row-10/12897/6?u=mlauber71 More inspirations for styleshttps://jingwen-z.github.io/how-to-munipulate-excel-workbook-by-python/ Manipulate Excel files in KNIME with Python OpenPyXLDemonstrates several Python based manipulations for Excel import knime.scripting.io as knioimport pandas as pd# https://openpyxl.readthedocs.io/en/stable/tutorial.html#loading-from-a-file# conda install -c conda-forge openpyxlfrom openpyxl import load_workbookimport openpyxlimport osvar_data_path = knio.flow_variables['v_path_data_folder']print("var_data_path : ", var_data_path )var_path_excel_file = var_data_path + "my_data.xlsx"print("var_path_excel_file : ", var_path_excel_file )wb = load_workbook(var_path_excel_file)ws = wb["data"]# activate the ws 'data'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = sws['D1'] = 'just wanted to write something here'ws['F1'] = 'An_new_header'ws['F19'] = 999.7655# enter a hyperlink into a cellws[knio.flow_variables['v_cell_for_link']].hyperlink = "https://forum.knime.com"ws[knio.flow_variables['v_cell_for_link']].value = 'KNIME Forum'ws[knio.flow_variables['v_cell_for_link']].style = "Hyperlink"# save the Excel filewb.save(var_path_excel_file)# close the workbookwb.close() import knime.scripting.io as knioimport pandas as pd# https://openpyxl.readthedocs.io/en/stable/tutorial.html#loading-from-a-file# conda install -c conda-forge openpyxlfrom openpyxl import load_workbookimport openpyxlimport osvar_data_path = knio.flow_variables['v_path_data_folder']print("var_data_path : ", var_data_path )var_path_excel_file = var_data_path + "my_data.xlsx"print("var_path_excel_file : ", var_path_excel_file )var_old_sheet_name = 'data'var_new_sheet_name = 'data_new'wb = load_workbook(var_path_excel_file)ws = wb[var_old_sheet_name]# activate the ws 'data'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = sv_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)# make sure there is no sheet with the new namefor i in v_list_sheetnames: if i == var_new_sheet_name: # wb.active = i wb.remove_sheet(var_new_sheet_name)# assign the new name to the sheetws.title = var_new_sheet_namev_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)wb.save(var_path_excel_file)# close the workbookwb.close() import knime.scripting.io as knioimport pandas as pd# https://openpyxl.readthedocs.io/en/stable/tutorial.html#loading-from-a-file# conda install -c conda-forge openpyxlfrom openpyxl import load_workbookimport openpyxlimport osvar_data_path = knio.flow_variables['v_path_data_folder']print("var_data_path : ", var_data_path )var_path_excel_file = var_data_path + "my_data.xlsx"print("var_path_excel_file : ", var_path_excel_file )# https://stackoverflow.com/questions/51128029/copying-to-a-specific-sheet-openpyxl-destination-sheet-ignored-when-using-copwb = load_workbook(var_path_excel_file)ws_souce_name = 'data_new'ws_target_name = 'data_new_copy'v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)# delete the new ws if it already existsfor i in v_list_sheetnames: if i == ws_target_name: std=wb.get_sheet_by_name(i) print("remove: ", i) wb.remove_sheet(std) ws = wb[ws_souce_name]v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)# activate the ws 'data'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = s# get Sheetwb.copy_worksheet(ws)#Get the position of the new sheetn = len(wb.sheetnames) #number of worksheetscopied_ws = wb.worksheets[n-1]# print ('Final sheet is:', wb.sheetnames[n-1])#rename worksheetscopied_ws.title = ws_target_name# save the workbookwb.save(var_path_excel_file)# close the workbookwb.close() # Importing necessary librariesimport knime.scripting.io as knio # KNIME scripting for workflow variablesimport pandas as pd # Pandas for data manipulationfrom openpyxl import load_workbook # For loading Excel workbooksfrom openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment # For cell stylingimport openpyxl # Openpyxl for Excel file operationsimport os # For operating system dependent functionality# Retrieve the data folder path from KNIME workflow variablesvar_data_path = knio.flow_variables['v_path_data_folder']print("var_data_path : ", var_data_path)# Construct the full path to the Excel filevar_path_excel_file = os.path.join(var_data_path, "my_data.xlsx")print("var_path_excel_file : ", var_path_excel_file)# Load the workbook from the specified filewb = load_workbook(var_path_excel_file)# Define the name for the new sheetws_new_sheet_name = 'new_sheet'# Print list of current sheet names in the workbookv_list_sheetnames = wb.sheetnamesprint("v_list_sheetnames: ", v_list_sheetnames)# http://www.pythonexcel.com/delete-sheet.php# Delete the new sheet if it already exists in the workbookfor sheet_name in v_list_sheetnames: if sheet_name == ws_new_sheet_name: wb.remove(wb[sheet_name]) print("Removed existing sheet:", sheet_name)# https://codoid.com/read-write-excel-using-python/# Create a new sheet with the specified namews = wb.create_sheet(ws_new_sheet_name)# Set the newly created sheet as the active sheetwb.active = wb.sheetnames.index(ws_new_sheet_name)# experiemnt with styles# https://openpyxl.readthedocs.io/en/stable/styles.html# Merge cells B2 to F4 in the new sheetws.merge_cells('B2:F4')# Set up styles for the top left cell after mergingtop_left_cell = ws['B2']top_left_cell.value = "My Cell"# Define border stylesthin = Side(border_style="thin", color="000000")double = Side(border_style="double", color="ff0000")# Apply styles to the top left celltop_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double)top_left_cell.fill = GradientFill(stop=("000000", "FFFFFF"))top_left_cell.font = Font(b=True, color="FF0000")top_left_cell.alignment = Alignment(horizontal="center", vertical="center")# Save the workbook with the changeswb.save(var_path_excel_file)# Close the workbookwb.close() locate and create/data/ folderwith absolute pathsdata1v_cell_for_link"G1" the cell where the link should be storedlist filetry todeletethe existing filemy_data.xlsxmanipulate excel filein Pythonrename a sheetcopy a sheetuse some stylesNode 46v_path_data_folder Collect LocalMetadata Data Generator Table Creator List Files/Folders DeleteFiles/Folders Excel Writer Table Rowto Variable Try (VariablePorts) Catch Errors(Var Ports) Merge Variables Table Rowto Variable Python Script Python Script Python Script Python Script Merge Variables Java EditVariable (simple) https://forum.knime.com/t/enabling-hyperlink-in-excel-through-knime/17109/4?u=mlauber71https://forum.knime.com/t/manipulate-tabs-inside-an-excel/14492Extended from:https://forum.knime.com/t/write-data-to-excel-sheet-on-row-10/12897/6?u=mlauber71 More inspirations for styleshttps://jingwen-z.github.io/how-to-munipulate-excel-workbook-by-python/ Manipulate Excel files in KNIME with Python OpenPyXLDemonstrates several Python based manipulations for Excel import knime.scripting.io as knioimport pandas as pd# https://openpyxl.readthedocs.io/en/stable/tutorial.html#loading-from-a-file# conda install -c conda-forge openpyxlfrom openpyxl import load_workbookimport openpyxlimport osvar_data_path = knio.flow_variables['v_path_data_folder']print("var_data_path : ", var_data_path )var_path_excel_file = var_data_path + "my_data.xlsx"print("var_path_excel_file : ", var_path_excel_file )wb = load_workbook(var_path_excel_file)ws = wb["data"]# activate the ws 'data'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = sws['D1'] = 'just wanted to write something here'ws['F1'] = 'An_new_header'ws['F19'] = 999.7655# enter a hyperlink into a cellws[knio.flow_variables['v_cell_for_link']].hyperlink = "https://forum.knime.com"ws[knio.flow_variables['v_cell_for_link']].value = 'KNIME Forum'ws[knio.flow_variables['v_cell_for_link']].style = "Hyperlink"# save the Excel filewb.save(var_path_excel_file)# close the workbookwb.close() import knime.scripting.io as knioimport pandas as pd# https://openpyxl.readthedocs.io/en/stable/tutorial.html#loading-from-a-file# conda install -c conda-forge openpyxlfrom openpyxl import load_workbookimport openpyxlimport osvar_data_path = knio.flow_variables['v_path_data_folder']print("var_data_path : ", var_data_path )var_path_excel_file = var_data_path + "my_data.xlsx"print("var_path_excel_file : ", var_path_excel_file )var_old_sheet_name = 'data'var_new_sheet_name = 'data_new'wb = load_workbook(var_path_excel_file)ws = wb[var_old_sheet_name]# activate the ws 'data'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = sv_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)# make sure there is no sheet with the new namefor i in v_list_sheetnames: if i == var_new_sheet_name: # wb.active = i wb.remove_sheet(var_new_sheet_name)# assign the new name to the sheetws.title = var_new_sheet_namev_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)wb.save(var_path_excel_file)# close the workbookwb.close() import knime.scripting.io as knioimport pandas as pd# https://openpyxl.readthedocs.io/en/stable/tutorial.html#loading-from-a-file# conda install -c conda-forge openpyxlfrom openpyxl import load_workbookimport openpyxlimport osvar_data_path = knio.flow_variables['v_path_data_folder']print("var_data_path : ", var_data_path )var_path_excel_file = var_data_path + "my_data.xlsx"print("var_path_excel_file : ", var_path_excel_file )# https://stackoverflow.com/questions/51128029/copying-to-a-specific-sheet-openpyxl-destination-sheet-ignored-when-using-copwb = load_workbook(var_path_excel_file)ws_souce_name = 'data_new'ws_target_name = 'data_new_copy'v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)# delete the new ws if it already existsfor i in v_list_sheetnames: if i == ws_target_name: std=wb.get_sheet_by_name(i) print("remove: ", i) wb.remove_sheet(std) ws = wb[ws_souce_name]v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)# activate the ws 'data'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = s# get Sheetwb.copy_worksheet(ws)#Get the position of the new sheetn = len(wb.sheetnames) #number of worksheetscopied_ws = wb.worksheets[n-1]# print ('Final sheet is:', wb.sheetnames[n-1])#rename worksheetscopied_ws.title = ws_target_name# save the workbookwb.save(var_path_excel_file)# close the workbookwb.close() # Importing necessary librariesimport knime.scripting.io as knio # KNIME scripting for workflow variablesimport pandas as pd # Pandas for data manipulationfrom openpyxl import load_workbook # For loading Excel workbooksfrom openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment # For cell stylingimport openpyxl # Openpyxl for Excel file operationsimport os # For operating system dependent functionality# Retrieve the data folder path from KNIME workflow variablesvar_data_path = knio.flow_variables['v_path_data_folder']print("var_data_path : ", var_data_path)# Construct the full path to the Excel filevar_path_excel_file = os.path.join(var_data_path, "my_data.xlsx")print("var_path_excel_file : ", var_path_excel_file)# Load the workbook from the specified filewb = load_workbook(var_path_excel_file)# Define the name for the new sheetws_new_sheet_name = 'new_sheet'# Print list of current sheet names in the workbookv_list_sheetnames = wb.sheetnamesprint("v_list_sheetnames: ", v_list_sheetnames)# http://www.pythonexcel.com/delete-sheet.php# Delete the new sheet if it already exists in the workbookfor sheet_name in v_list_sheetnames: if sheet_name == ws_new_sheet_name: wb.remove(wb[sheet_name]) print("Removed existing sheet:", sheet_name)# https://codoid.com/read-write-excel-using-python/# Create a new sheet with the specified namews = wb.create_sheet(ws_new_sheet_name)# Set the newly created sheet as the active sheetwb.active = wb.sheetnames.index(ws_new_sheet_name)# experiemnt with styles# https://openpyxl.readthedocs.io/en/stable/styles.html# Merge cells B2 to F4 in the new sheetws.merge_cells('B2:F4')# Set up styles for the top left cell after mergingtop_left_cell = ws['B2']top_left_cell.value = "My Cell"# Define border stylesthin = Side(border_style="thin", color="000000")double = Side(border_style="double", color="ff0000")# Apply styles to the top left celltop_left_cell.border = Border(top=double, left=thin, right=thin, bottom=double)top_left_cell.fill = GradientFill(stop=("000000", "FFFFFF"))top_left_cell.font = Font(b=True, color="FF0000")top_left_cell.alignment = Alignment(horizontal="center", vertical="center")# Save the workbook with the changeswb.save(var_path_excel_file)# Close the workbookwb.close() locate and create/data/ folderwith absolute pathsdata1v_cell_for_link"G1" the cell where the link should be storedlist filetry todeletethe existing filemy_data.xlsxmanipulate excel filein Pythonrename a sheetcopy a sheetuse some stylesNode 46v_path_data_folderCollect LocalMetadata Data Generator Table Creator List Files/Folders DeleteFiles/Folders Excel Writer Table Rowto Variable Try (VariablePorts) Catch Errors(Var Ports) Merge Variables Table Rowto Variable Python Script Python Script Python Script Python Script Merge Variables Java EditVariable (simple)

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-12-13
On NodePit since: 2019-11-30
Last update: 2024-04-20
Created with KNIME version: v4.7.8
Tags: pythonopenpyxlexcel

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

Try NodePit Runner!