Icon

kn_​example_​python_​excel_​import_​export_​openpyxl

use Python Pandas and Openpyxl to import and export Excel (sheets) and Export them back - also append to an existing Excel file

use Python Pandas and Openpyxl to import and export Excel (sheets) and Export them back - also append to an existing Excel file

import knime.scripting.io as knioimport numpy as npimport pandas as pdimport openpyxl# https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandasdf= knio.input_tables[0].to_pandas()var_excel_file = knio.flow_variables['v_excel_path']var_excel_sheet = knio.flow_variables['Sheet']var_new_excel_file = knio.flow_variables['var_excel2_file']# Open the workbook and sheetwb = openpyxl.load_workbook(var_excel_file)sheet = wb[var_excel_sheet]# Iterate over the rows of the dataframe and append them to the sheetfor row in df.values: sheet.append(row.tolist())# Save the workbookwb.save(var_new_excel_file) use Python Pandas and Openpyxl to import and export Excel (sheets) and Export them back - also append to an existing Excel file locate and create/data/ folderwith absolute paths/data/ subfolderfile_01.xlsx^(var_excel_file|var_excel2_file)$var_excel_filev_excel_pathvar_excel2_fileENDfile_02.xlsxdelete file_02.xlsxTry todeletefile_02.xlsxTry todeletefile_02.xlsxdf = pd.read_excel(knio.flow_variables['v_excel_path'], sheet_name=knio.flow_variables['Sheet'], engine='openpyxl')=> import Excel sheet with openpyxlSTARTAppend data to an existingExcel Sheet sheet.append()Collect LocalMetadata Generate Excel File Read ExcelSheet Names String to Path(Variable) Java EditVariable (simple) ConstantValue Column Java EditVariable (simple) Variable Loop End Read ExcelSheet Names DeleteFiles/Folders Try (VariablePorts) Catch Errors(Var Ports) Merge Variables Python Script Table Row ToVariable Loop Start Python Script import knime.scripting.io as knioimport numpy as npimport pandas as pdimport openpyxl# https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandasdf= knio.input_tables[0].to_pandas()var_excel_file = knio.flow_variables['v_excel_path']var_excel_sheet = knio.flow_variables['Sheet']var_new_excel_file = knio.flow_variables['var_excel2_file']# Open the workbook and sheetwb = openpyxl.load_workbook(var_excel_file)sheet = wb[var_excel_sheet]# Iterate over the rows of the dataframe and append them to the sheetfor row in df.values: sheet.append(row.tolist())# Save the workbookwb.save(var_new_excel_file) use Python Pandas and Openpyxl to import and export Excel (sheets) and Export them back - also append to an existing Excel file locate and create/data/ folderwith absolute paths/data/ subfolderfile_01.xlsx^(var_excel_file|var_excel2_file)$var_excel_filev_excel_pathvar_excel2_fileENDfile_02.xlsxdelete file_02.xlsxTry todeletefile_02.xlsxTry todeletefile_02.xlsxdf = pd.read_excel(knio.flow_variables['v_excel_path'], sheet_name=knio.flow_variables['Sheet'], engine='openpyxl')=> import Excel sheet with openpyxlSTARTAppend data to an existingExcel Sheetsheet.append()Collect LocalMetadata Generate Excel File Read ExcelSheet Names String to Path(Variable) Java EditVariable (simple) ConstantValue Column Java EditVariable (simple) Variable Loop End Read ExcelSheet Names DeleteFiles/Folders Try (VariablePorts) Catch Errors(Var Ports) Merge Variables Python Script Table Row ToVariable Loop Start Python Script

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: 2020-12-22
On NodePit since: 2020-12-22
Last update: 2024-04-24
Created with KNIME version: v4.7.1
Tags: pythonexcelimportexportknimeopenpyxlpandasappend

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

Try NodePit Runner!