Icon

kn_​forum_​66488_​excel_​first_​sheet

Excel Sheet names and index of Sheets - KNIME forum (66488)

Excel Sheet names and index of Sheets - KNIME forum (66488)

################################################################################ 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 + knio.flow_variables['file_name'] + "." + knio.flow_variables['file_extension']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) Excel Sheet names and index of Sheets - KNIME forum (66488)https://forum.knime.com/t/excel-reader-with-read-excel-sheet-names-inconsistency/66488/11?u=mlauber71 BE_test.xlsxlocate and create/data/ folderwith absolute pathsmy_data.xlsxlist index of every sheet*path*my_excel_filemy_excel_filemy_excel_filemy_excel_fileExcel_Sheetresults.table Excel Reader Collect LocalMetadata Python Script Variable toTable Row Path to String(Variable) URL to FilePath (Variable) Read ExcelSheet Names Table Row ToVariable Loop Start Excel Reader ConstantValue Column Loop End GroupBy Table Writer ################################################################################ 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 + knio.flow_variables['file_name'] + "." + knio.flow_variables['file_extension']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) Excel Sheet names and index of Sheets - KNIME forum (66488)https://forum.knime.com/t/excel-reader-with-read-excel-sheet-names-inconsistency/66488/11?u=mlauber71 BE_test.xlsxlocate and create/data/ folderwith absolute pathsmy_data.xlsxlist index of every sheet*path*my_excel_filemy_excel_filemy_excel_filemy_excel_fileExcel_Sheetresults.tableExcel Reader Collect LocalMetadata Python Script Variable toTable Row Path to String(Variable) URL to FilePath (Variable) Read ExcelSheet Names Table Row ToVariable Loop Start Excel Reader ConstantValue Column Loop End GroupBy Table Writer

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: A9699459
Created at: 2023-05-17
On NodePit since: 2023-05-18
Last update: 2024-04-28
Created with KNIME version: v4.7.3
Tags: excelsheetindexpythonopenpyxl

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

Try NodePit Runner!