Icon

kn_​example_​python_​excel_​sheet_​move_​to_​position

use KNIME and Python OpenPyxl to read the index numbers of every sheet in an Excel file - then move a sheet from one index position to a new one

use KNIME and Python OpenPyxl to read the index numbers of every sheet in an Excel file - then move a sheet from one index position to a new one



################################################################################ https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html# there is a Jupyter notebook in the /data/kn_example_python_excel_sheet_index.ipynb folder to experiment with# import the KNIME moduleimport knime.scripting.io as knioimport numpy as npimport pandas as pdfrom openpyxl import Workbookfrom openpyxl import load_workbookfrom openpyxl.worksheet.table import Table, TableStyleInfoimport os# recreate the classic input_table from the new Python import since KNIME 4.5input_table = knio.input_tables[0].to_pandas()# get the path to the data foldervar_workflow_data_path = knio.flow_variables['context.workflow.data-path']print("var_workflow_data_path : ", var_workflow_data_path )# create the path of the Excel filevar_path_excel_file = var_workflow_data_path + "my_data.xlsx"print("var_path_excel_file : ", var_path_excel_file )wb = load_workbook(var_path_excel_file) # Set the filepath + filename# create a list of the sheet nameslist_sheetnames = wb.sheetnames# create an empty listlist_sheetnames_index= list()# iterate over the sheet names and extract their indexfor s in range(len(wb.sheetnames)): wb.active = s sheet_name = wb.sheetnames[s] sheet_index = wb._active_sheet_index new_item = sheet_name, sheet_index type(new_item) list_sheetnames_index.append(new_item) # print(sheet_name, sheet_index)# convert the list to a data frame to bring back to KNIMEdf = pd.DataFrame(list_sheetnames_index, columns = ["sheet_name", "sheet_index"])df['sheet_index'] = df['sheet_index'].astype(int)wb.close()# also export the path of the excel file from Pythonknio.flow_variables['var_path_excel_file'] = var_path_excel_fileknio.output_tables[0] = knio.Table.from_pandas(df) Prepare an Excel file where "Sheet3" is at the secondposition resulting in an index 1 (starting at 0) use KNIME and Python OpenPyxl to read the index numbers of every sheet in an Excel file - then move a sheet from one index position to a new one("did someone say overengineered ...?") ################################################################################ https://stackoverflow.com/questions/51082458/move-a-worksheet-in-a-workbook-using-openpyxl-or-xl-or-xlsxwriter/63622324#63622324# import the KNIME moduleimport knime.scripting.io as kniofrom openpyxl import load_workbookdef neworder(file, sheetname, tpos):"""Takes a list of ints, and inserts the fpos (from position) int, to tpos (to position)""" wb = load_workbook(filename=file) fpos = wb.worksheets.index(wb[sheetname]) if fpos == tpos: wb.close() return shlist = wb.sheetnames # get current order sheets in workbook lst = [] lpos = (len(shlist) - 1) # last position if lpos >= fpos > tpos >= 0: # move from a high to low position for x in range(lpos+1): if x == tpos: lst.append(fpos) elif tpos < x <= fpos: lst.append(x-1) else: lst.append(x) if lpos >= tpos > fpos >= 0: # move from a low to high position for x in range(lpos+1): if x == tpos: lst.append(fpos) elif fpos <= x < tpos: lst.append(x+1) else: lst.append(x) wb._sheets = [wb._sheets[i] for i in lst] # get each object instance from wb._sheets, and replace wb.active = wb[sheetname] wb.save(filename=file) wb.close() returnfile_var = knio.flow_variables['v_excel_file_path']sheetname_var = knio.flow_variables['v_sheet_name']tpos_var = knio.flow_variables['v_sheet_new_position']neworder(file_var, sheetname_var , tpos_var) locate and create/data/ folderwith absolute paths1.000 linesmy_data.xlsxlist index of every sheetBEFOREvar_*move Excel sheet toa new positionwith the help ofOpenPyxlmy_data.xlsxlist index of every sheetAFTERmy_data.xlsx Collect LocalMetadata Data Generator Merge Variables Python Script Variable toTable Row Component Python Script Metanode ################################################################################ https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html# there is a Jupyter notebook in the /data/kn_example_python_excel_sheet_index.ipynb folder to experiment with# import the KNIME moduleimport knime.scripting.io as knioimport numpy as npimport pandas as pdfrom openpyxl import Workbookfrom openpyxl import load_workbookfrom openpyxl.worksheet.table import Table, TableStyleInfoimport os# recreate the classic input_table from the new Python import since KNIME 4.5input_table = knio.input_tables[0].to_pandas()# get the path to the data foldervar_workflow_data_path = knio.flow_variables['context.workflow.data-path']print("var_workflow_data_path : ", var_workflow_data_path )# create the path of the Excel filevar_path_excel_file = var_workflow_data_path + "my_data.xlsx"print("var_path_excel_file : ", var_path_excel_file )wb = load_workbook(var_path_excel_file) # Set the filepath + filename# create a list of the sheet nameslist_sheetnames = wb.sheetnames# create an empty listlist_sheetnames_index= list()# iterate over the sheet names and extract their indexfor s in range(len(wb.sheetnames)): wb.active = s sheet_name = wb.sheetnames[s] sheet_index = wb._active_sheet_index new_item = sheet_name, sheet_index type(new_item) list_sheetnames_index.append(new_item) # print(sheet_name, sheet_index)# convert the list to a data frame to bring back to KNIMEdf = pd.DataFrame(list_sheetnames_index, columns = ["sheet_name", "sheet_index"])df['sheet_index'] = df['sheet_index'].astype(int)wb.close()# also export the path of the excel file from Pythonknio.flow_variables['var_path_excel_file'] = var_path_excel_fileknio.output_tables[0] = knio.Table.from_pandas(df) Prepare an Excel file where "Sheet3" is at the secondposition resulting in an index 1 (starting at 0) use KNIME and Python OpenPyxl to read the index numbers of every sheet in an Excel file - then move a sheet from one index position to a new one("did someone say overengineered ...?") ################################################################################ https://stackoverflow.com/questions/51082458/move-a-worksheet-in-a-workbook-using-openpyxl-or-xl-or-xlsxwriter/63622324#63622324# import the KNIME moduleimport knime.scripting.io as kniofrom openpyxl import load_workbookdef neworder(file, sheetname, tpos):"""Takes a list of ints, and inserts the fpos (from position) int, to tpos (to position)""" wb = load_workbook(filename=file) fpos = wb.worksheets.index(wb[sheetname]) if fpos == tpos: wb.close() return shlist = wb.sheetnames # get current order sheets in workbook lst = [] lpos = (len(shlist) - 1) # last position if lpos >= fpos > tpos >= 0: # move from a high to low position for x in range(lpos+1): if x == tpos: lst.append(fpos) elif tpos < x <= fpos: lst.append(x-1) else: lst.append(x) if lpos >= tpos > fpos >= 0: # move from a low to high position for x in range(lpos+1): if x == tpos: lst.append(fpos) elif fpos <= x < tpos: lst.append(x+1) else: lst.append(x) wb._sheets = [wb._sheets[i] for i in lst] # get each object instance from wb._sheets, and replace wb.active = wb[sheetname] wb.save(filename=file) wb.close() returnfile_var = knio.flow_variables['v_excel_file_path']sheetname_var = knio.flow_variables['v_sheet_name']tpos_var = knio.flow_variables['v_sheet_new_position']neworder(file_var, sheetname_var , tpos_var) locate and create/data/ folderwith absolute paths1.000 linesmy_data.xlsxlist index of every sheetBEFOREvar_*move Excel sheet toa new positionwith the help ofOpenPyxlmy_data.xlsxlist index of every sheetAFTERmy_data.xlsxCollect LocalMetadata Data Generator Merge Variables Python Script Variable toTable Row Component Python Script Metanode

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: 2021-12-17
On NodePit since: 2022-06-15
Last update: 2024-04-26
Created with KNIME version: v4.7.0
Tags: KNIMEpythonopenpyxlindexsheetsheetnamemoveposition

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

Try NodePit Runner!