Icon

kn_​example_​python_​excel_​remove_​sheet

use Python package openpyxl to remove an existing worksheet from an Excel file

use Python package openpyxl to remove an existing worksheet from an Excel file


use Python package openpyxl to remove an existing worksheet from an Excel filehttps://forum.knime.com/t/remove-or-update-sheets-on-an-excel-file/19318/2?u=mlauber71 https://www.pythonexcel.com/delete-sheet.phphttps://medium.com/aubergine-solutions/working-with-excel-sheets-in-python-using-openpyxl-4f9fd32de87f import knime.scripting.io as kniofrom pandas import DataFrameimport pandas as pd# Create empty tableimport openpyxlfrom openpyxl import load_workbookimport os var_workflow_path = knio.flow_variables['context.workflow.absolute-path'] print("var_workflow_path : ", var_workflow_path ) # var_path_excel_file = var_workflow_path + os.path.sep + "data" + os.path.sep + "data.xlsx" var_path_excel_file = knio.flow_variables['context.workflow.data-path'] + "data.xlsx" print("var_path_excel_file : ", var_path_excel_file ) wb = load_workbook(var_path_excel_file)# define the Excel Sheet to be removedws_sheet_to_remove = 'data2'# put all Excel Sheets in a listv_list_sheetnames = wb.sheetnamesdf_sheets_before = pd.DataFrame( { "Excel_File" : [var_path_excel_file] , "Excel_Sheets": [v_list_sheetnames] } ) print(v_list_sheetnames)# delete the new ws if it already existsfor i in v_list_sheetnames: if i == ws_sheet_to_remove: std=wb.get_sheet_by_name(i) print("remove: ", i) wb.remove_sheet(std) v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)df_sheets_after = pd.DataFrame( { "Excel_File" : [var_path_excel_file] , "Excel_Sheets": [v_list_sheetnames] } ) wb.save(var_path_excel_file)wb.close()knio.output_tables[0] = knio.Table.from_pandas(df_sheets_before)knio.output_tables[1] = knio.Table.from_pandas(df_sheets_after) locate and create/data/ folderwith absolute pathsdata2data1knime://knime.workflow/data/data.xlsxsheet: "data_x1"knime://knime.workflow/data/data.xlsxadd sheet: "data2"show all sheetsshow all sheets"data2" now missingremove excel sheet"data2"if it exists1st Table: Sheets before2nd Table: Sheets afterNode 431 Collect LocalMetadata Table Creator Table Creator Excel Writer Excel Writer Read ExcelSheet Names Read ExcelSheet Names Python Script Merge Variables use Python package openpyxl to remove an existing worksheet from an Excel filehttps://forum.knime.com/t/remove-or-update-sheets-on-an-excel-file/19318/2?u=mlauber71 https://www.pythonexcel.com/delete-sheet.phphttps://medium.com/aubergine-solutions/working-with-excel-sheets-in-python-using-openpyxl-4f9fd32de87f import knime.scripting.io as kniofrom pandas import DataFrameimport pandas as pd# Create empty tableimport openpyxlfrom openpyxl import load_workbookimport os var_workflow_path = knio.flow_variables['context.workflow.absolute-path'] print("var_workflow_path : ", var_workflow_path ) # var_path_excel_file = var_workflow_path + os.path.sep + "data" + os.path.sep + "data.xlsx" var_path_excel_file = knio.flow_variables['context.workflow.data-path'] + "data.xlsx" print("var_path_excel_file : ", var_path_excel_file ) wb = load_workbook(var_path_excel_file)# define the Excel Sheet to be removedws_sheet_to_remove = 'data2'# put all Excel Sheets in a listv_list_sheetnames = wb.sheetnamesdf_sheets_before = pd.DataFrame( { "Excel_File" : [var_path_excel_file] , "Excel_Sheets": [v_list_sheetnames] } ) print(v_list_sheetnames)# delete the new ws if it already existsfor i in v_list_sheetnames: if i == ws_sheet_to_remove: std=wb.get_sheet_by_name(i) print("remove: ", i) wb.remove_sheet(std) v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)df_sheets_after = pd.DataFrame( { "Excel_File" : [var_path_excel_file] , "Excel_Sheets": [v_list_sheetnames] } ) wb.save(var_path_excel_file)wb.close()knio.output_tables[0] = knio.Table.from_pandas(df_sheets_before)knio.output_tables[1] = knio.Table.from_pandas(df_sheets_after) locate and create/data/ folderwith absolute pathsdata2data1knime://knime.workflow/data/data.xlsxsheet: "data_x1"knime://knime.workflow/data/data.xlsxadd sheet: "data2"show all sheetsshow all sheets"data2" now missingremove excel sheet"data2"if it exists1st Table: Sheets before2nd Table: Sheets afterNode 431Collect LocalMetadata Table Creator Table Creator Excel Writer Excel Writer Read ExcelSheet Names Read ExcelSheet Names Python Script Merge Variables

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: 2019-03-11
On NodePit since: 2019-11-09
Last update: 2024-04-26
Created with KNIME version: v4.7.8
Tags: excelloadwritesheetremovedeletepythonopenpyxl

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

Try NodePit Runner!