Icon

kn_​forum_​29528_​python_​excel_​append_​to_​existing_​sheet

Append data to an existing Excel file with the help of Python Pandas and OpenPyxl

Append data to an existing Excel file with the help of Python Pandas and OpenPyxl

# Copy input to outputimport pandas as pdfrom openpyxl import load_workbookvar_workflow_path = flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )var_data_path = var_workflow_path + os.path.sep + "data" + os.path.sepprint("var_data_path : ", var_data_path )var_path_target_excel_file = var_data_path + "excelExport_new.xlsx"print("var_path_target_excel_file : ", var_path_target_excel_file )var_sheet_name = "default_1"df_append = input_table_1.copy()# https://stackoverflow.com/questions/47737220/append-dataframe-to-excel-with-pandasbook = load_workbook(var_path_target_excel_file)writer = pd.ExcelWriter(var_path_target_excel_file, engine='openpyxl')writer.book = bookwriter.sheets = {ws.title: ws for ws in book.worksheets}df_append.to_excel(writer,sheet_name=var_sheet_name, startrow=writer.sheets[var_sheet_name].max_row, index = False,header= False)writer.save()output_table_1 = input_table_1.copy() Append data to an existing Excel file with the help of Python Pandas and OpenPyxlhttps://forum.knime.com/t/data-extraction-from-xls-file-s/29528/12?u=mlauber71 Node 1the magic happenshereexcelExport.xls=> Excel File toappend data toSheet0excelExport_new.xlsx=> just to make sure we have a fresh Excel FileFirst_file.xls=> 4 Sheets with datato be appendedto sheet default_1excelExport.xlsSTARTENDExtract ContextProperties Python Script Excel Reader Excel Writer Read ExcelSheet Names Excel Reader Table Row ToVariable Loop Start Variable Loop End # Copy input to outputimport pandas as pdfrom openpyxl import load_workbookvar_workflow_path = flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )var_data_path = var_workflow_path + os.path.sep + "data" + os.path.sepprint("var_data_path : ", var_data_path )var_path_target_excel_file = var_data_path + "excelExport_new.xlsx"print("var_path_target_excel_file : ", var_path_target_excel_file )var_sheet_name = "default_1"df_append = input_table_1.copy()# https://stackoverflow.com/questions/47737220/append-dataframe-to-excel-with-pandasbook = load_workbook(var_path_target_excel_file)writer = pd.ExcelWriter(var_path_target_excel_file, engine='openpyxl')writer.book = bookwriter.sheets = {ws.title: ws for ws in book.worksheets}df_append.to_excel(writer,sheet_name=var_sheet_name, startrow=writer.sheets[var_sheet_name].max_row, index = False,header= False)writer.save()output_table_1 = input_table_1.copy() Append data to an existing Excel file with the help of Python Pandas and OpenPyxlhttps://forum.knime.com/t/data-extraction-from-xls-file-s/29528/12?u=mlauber71 Node 1the magic happenshereexcelExport.xls=> Excel File toappend data toSheet0excelExport_new.xlsx=> just to make sure we have a fresh Excel FileFirst_file.xls=> 4 Sheets with datato be appendedto sheet default_1excelExport.xlsSTARTEND Extract ContextProperties Python Script Excel Reader Excel Writer Read ExcelSheet Names Excel Reader Table Row ToVariable Loop Start Variable Loop End

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-18
On NodePit since: 2020-12-20
Last update: 2024-04-27
Created with KNIME version: v4.3.0
Tags: excelfilesheetappendpandaspythonknimeopenpyxl

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

Try NodePit Runner!