Icon

kn_​example_​python_​excel_​export_​simple_​file_​openpyxl

Export Excel File with OpenPyxl (simple) from KNIME - also add some formats

Export Excel File with OpenPyxl (simple) from KNIME - also add some formats

import knime.scripting.io as knioimport numpy as npimport pandas as pdimport openpyxlfrom openpyxl.utils.dataframe import dataframe_to_rowsdf= knio.input_tables[0].to_pandas()# Reset the index to a range of sequential integersdf = df.reset_index(drop=True)df.index += 1# Set the new index as a long integerdf.index = df.index.astype('int64')var_excel_file = knio.flow_variables['var_excel_file']var_excel_sheet = knio.flow_variables['var_excel_sheet']# https://stackoverflow.com/a/75162871# with the help of some ChatGPTdef df_to_excel(df, ws, header=True, index=True, startrow=0, startcol=0):"""Write DataFrame df to openpyxl worksheet ws""" values = df.values nrow, ncol = values.shape # Write the headers to the worksheet if header: for c_idx, value in enumerate(df.columns, startcol + 1): ws.cell(row=startrow + 1, column=c_idx).value = value # Write the data rows to the worksheet for r_idx, row in enumerate(values, startrow + 2): for c_idx, value in enumerate(row, startcol + 1): ws.cell(row=r_idx, column=c_idx).value = value# Load the workbook and sheetwb = openpyxl.load_workbook(var_excel_file)# Check if the sheet already exists, and remove it if it doesif var_excel_sheet in wb.sheetnames: std = wb[var_excel_sheet] wb.remove(std)ws = wb.create_sheet(var_excel_sheet)# Write the dataframe to the worksheetdf_to_excel(df, ws)# Save the workbookwb.save(var_excel_file)wb.close() Export Excel File with OpenPyxl (simple) from KNIME - also add some formatshttps://forum.knime.com/t/excel-reader-how-to-retain-empty-cells/61674/24?u=mlauber71 var_excel_file"file_01.xlsx"locate and create/data/ folderwith absolute pathssample data"file_01.xlsx""Sheet1"var_excel_fileAppend data to an existingExcel File "file_01.xlsx"into "Sheet2" tab(if exists overwrite)var_excel_sheet"Sheet2"data to exceloverwrite existing Excel file "file_02.xlsx"df.to_excel(var_excel_file,sheet_name=var_excel_sheet, index=False, engine='openpyxl')var_excel_file"file_02.xlsx"var_excel_filevar_excel_sheet"Sheet"var_excel_file"file_03.xlsx"var_excel_filevar_excel_sheet"Sheet2"Append data to an existingExcel File "file_03.xlsx"into "Sheet2" tab(if exists overwrite)=> auto-format the columns andhedaer"file_03.xlsx""Sheet1" Java EditVariable (simple) Collect LocalMetadata Data Generator Excel Writer String to Path(Variable) Python Script Java EditVariable (simple) Python Script Java EditVariable (simple) String to Path(Variable) Java EditVariable (simple) Java EditVariable (simple) String to Path(Variable) Java EditVariable (simple) Python Script Excel Writer import knime.scripting.io as knioimport numpy as npimport pandas as pdimport openpyxlfrom openpyxl.utils.dataframe import dataframe_to_rowsdf= knio.input_tables[0].to_pandas()# Reset the index to a range of sequential integersdf = df.reset_index(drop=True)df.index += 1# Set the new index as a long integerdf.index = df.index.astype('int64')var_excel_file = knio.flow_variables['var_excel_file']var_excel_sheet = knio.flow_variables['var_excel_sheet']# https://stackoverflow.com/a/75162871# with the help of some ChatGPTdef df_to_excel(df, ws, header=True, index=True, startrow=0, startcol=0):"""Write DataFrame df to openpyxl worksheet ws""" values = df.values nrow, ncol = values.shape # Write the headers to the worksheet if header: for c_idx, value in enumerate(df.columns, startcol + 1): ws.cell(row=startrow + 1, column=c_idx).value = value # Write the data rows to the worksheet for r_idx, row in enumerate(values, startrow + 2): for c_idx, value in enumerate(row, startcol + 1): ws.cell(row=r_idx, column=c_idx).value = value# Load the workbook and sheetwb = openpyxl.load_workbook(var_excel_file)# Check if the sheet already exists, and remove it if it doesif var_excel_sheet in wb.sheetnames: std = wb[var_excel_sheet] wb.remove(std)ws = wb.create_sheet(var_excel_sheet)# Write the dataframe to the worksheetdf_to_excel(df, ws)# Save the workbookwb.save(var_excel_file)wb.close() Export Excel File with OpenPyxl (simple) from KNIME - also add some formatshttps://forum.knime.com/t/excel-reader-how-to-retain-empty-cells/61674/24?u=mlauber71 var_excel_file"file_01.xlsx"locate and create/data/ folderwith absolute pathssample data"file_01.xlsx""Sheet1"var_excel_fileAppend data to an existingExcel File "file_01.xlsx"into "Sheet2" tab(if exists overwrite)var_excel_sheet"Sheet2"data to exceloverwrite existing Excel file "file_02.xlsx"df.to_excel(var_excel_file,sheet_name=var_excel_sheet, index=False, engine='openpyxl')var_excel_file"file_02.xlsx"var_excel_filevar_excel_sheet"Sheet"var_excel_file"file_03.xlsx"var_excel_filevar_excel_sheet"Sheet2"Append data to an existingExcel File "file_03.xlsx"into "Sheet2" tab(if exists overwrite)=> auto-format the columns andhedaer"file_03.xlsx""Sheet1" Java EditVariable (simple) Collect LocalMetadata Data Generator Excel Writer String to Path(Variable) Python Script Java EditVariable (simple) Python Script Java EditVariable (simple) String to Path(Variable) Java EditVariable (simple) Java EditVariable (simple) String to Path(Variable) Java EditVariable (simple) Python Script Excel 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: m_lauber
Created at: 2023-02-13
On NodePit since: 2023-02-22
Last update: 2024-04-27
Created with KNIME version: v4.7.1
Tags: ExcelPythonOpenpyxlexport

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

Try NodePit Runner!