Icon

kn_​example_​python_​excel_​sheet_​index

use KNIME and Python OpenPyxl to read the index numbers of every sheet in an Excel file. Then use the index numbers to import the files back

use KNIME and Python OpenPyxl to read the index numbers of every sheet in an Excel file. Then use the index numbers to import the files back
("did someone say overengineered ...?")
there is a Jupyter notebook in the /data/kn_example_python_excel_sheet_index.ipynb folder to experiemnt with

-----------------
conda install -c conda-forge jupyterlab notebook
conda install -c conda-forge notebook

conda activate py3_knime
jupyter notebook


################################################################################ 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_path = knio.flow_variables['context.workflow.data-path']print("var_workflow_path : ", var_workflow_path )# create the path of the Excel filevar_path_excel_file = var_workflow_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 second position resulting in and index 1 (starting at 0) use KNIME and Python OpenPyxl to read the index numbers of every sheet in an Excel file. Then use the index numbers to import the files back("did someone say overengineered ...?")there is a Jupyter notebook in the /data/kn_example_python_excel_sheet_index.ipynb folder to experiment with locate and create/data/ folderwith absolute pathsLOOPEnd1.000 linesLOOPStartSheet_Indexvar_sheet_selectionINDEXmy_data.xlsxlist index of every sheet*path*50:5050:5050:50my_data.xlsxSheet1overwrite filemy_data.xlsxSheet2my_data.xlsxSheet3yes we place Sheet3deliberately on the 2nd positionmy_data.xlsxSheet4var_path_excel_fileImport Excel file andSheets per Indexsheet_indexSheet_Name"Sheet1"Sheet_Name"Sheet3"Sheet_Name"Sheet2"Sheet_Name"Sheet4" Collect LocalMetadata Loop End Data Generator Table Row ToVariable Loop Start ConstantValue Column Java EditVariable (simple) Merge Variables Python Script Variable toTable Row Partitioning Partitioning Partitioning Excel Writer Excel Writer Excel Writer Excel Writer String to Path(Variable) Excel Reader Double To Int ConstantValue Column ConstantValue Column ConstantValue Column ConstantValue Column ################################################################################ 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_path = knio.flow_variables['context.workflow.data-path']print("var_workflow_path : ", var_workflow_path )# create the path of the Excel filevar_path_excel_file = var_workflow_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 second position resulting in and index 1 (starting at 0) use KNIME and Python OpenPyxl to read the index numbers of every sheet in an Excel file. Then use the index numbers to import the files back("did someone say overengineered ...?")there is a Jupyter notebook in the /data/kn_example_python_excel_sheet_index.ipynb folder to experiment with locate and create/data/ folderwith absolute pathsLOOPEnd1.000 linesLOOPStartSheet_Indexvar_sheet_selectionINDEXmy_data.xlsxlist index of every sheet*path*50:5050:5050:50my_data.xlsxSheet1overwrite filemy_data.xlsxSheet2my_data.xlsxSheet3yes we place Sheet3deliberately on the 2nd positionmy_data.xlsxSheet4var_path_excel_fileImport Excel file andSheets per Indexsheet_indexSheet_Name"Sheet1"Sheet_Name"Sheet3"Sheet_Name"Sheet2"Sheet_Name"Sheet4"Collect LocalMetadata Loop End Data Generator Table Row ToVariable Loop Start ConstantValue Column Java EditVariable (simple) Merge Variables Python Script Variable toTable Row Partitioning Partitioning Partitioning Excel Writer Excel Writer Excel Writer Excel Writer String to Path(Variable) Excel Reader Double To Int ConstantValue Column ConstantValue Column ConstantValue Column ConstantValue Column

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: 2021-12-19
Last update: 2024-04-26
Created with KNIME version: v4.7.0
Tags: KNIMEpythonopenpyxlindexsheetsheetname

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

Try NodePit Runner!