Icon

kn_​example_​python_​excel_​enter_​formula_​openpyxl

KNIME - openpyxl and xlsxwriter to add formulas to excel files

use python package openpyxl to add formulas to excel files



Openpyxlhttps://www.geeksforgeeks.org/python-adjusting-rows-and-columns-of-an-excel-file-using-openpyxl-module/https://openpyxl.readthedocs.io/en/stable/usage.htmlhttp://zetcode.com/python/openpyxl/https://realpython.com/openpyxl-excel-spreadsheets-python/ use python package openpyxl to add formulas to excel files import knime.scripting.io as knioimport openpyxlimport osfrom openpyxl.formula.translate import Translatorfrom openpyxl.utils import get_column_letterfrom openpyxl.styles import Font, Fillvar_workflow_path = knio.flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = knio.flow_variables['v_path_excel_file']print("var_path_excel_file : ", var_path_excel_file )# Create an new Excel file and add a worksheet.wb = openpyxl.load_workbook(var_path_excel_file)ws_sheet_to_remove = 'Sheet1'v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)# delete the new ws if it already existsfor i in v_list_sheetnames: if i == ws_sheet_to_remove: std=wb.get_sheet_by_name(i) print("remove: ", i) wb.remove_sheet(std)ws = wb.create_sheet("Sheet1")# https://www.geeksforgeeks.org/python-adjusting-rows-and-columns-of-an-excel-file-using-openpyxl-module/# Widen the first column to make the text clearer.# ws.column_dimensions['A:B'].width = 20# Write some simple text.ws['A1'] = 'Values'# Text with formatting.ws['B1'] = 'Sum of first 100 lines:'ws['A2'] = 123.1ws['A3'] = 456.2ws['B2'] = "=SUM(A1:A100)"wb.save(var_path_excel_file)knio.output_tables[0] = knio.input_tables[0] locate and create/data/ folderwith absolute pathsv_path_excel_filecreate filedemo1.xlsxv_path*demo 1 Collect LocalMetadata Java EditVariable (simple) Variable toTable Row Excel Writer String to Path(Variable) Python Script Openpyxlhttps://www.geeksforgeeks.org/python-adjusting-rows-and-columns-of-an-excel-file-using-openpyxl-module/https://openpyxl.readthedocs.io/en/stable/usage.htmlhttp://zetcode.com/python/openpyxl/https://realpython.com/openpyxl-excel-spreadsheets-python/ use python package openpyxl to add formulas to excel files import knime.scripting.io as knioimport openpyxlimport osfrom openpyxl.formula.translate import Translatorfrom openpyxl.utils import get_column_letterfrom openpyxl.styles import Font, Fillvar_workflow_path = knio.flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = knio.flow_variables['v_path_excel_file']print("var_path_excel_file : ", var_path_excel_file )# Create an new Excel file and add a worksheet.wb = openpyxl.load_workbook(var_path_excel_file)ws_sheet_to_remove = 'Sheet1'v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)# delete the new ws if it already existsfor i in v_list_sheetnames: if i == ws_sheet_to_remove: std=wb.get_sheet_by_name(i) print("remove: ", i) wb.remove_sheet(std)ws = wb.create_sheet("Sheet1")# https://www.geeksforgeeks.org/python-adjusting-rows-and-columns-of-an-excel-file-using-openpyxl-module/# Widen the first column to make the text clearer.# ws.column_dimensions['A:B'].width = 20# Write some simple text.ws['A1'] = 'Values'# Text with formatting.ws['B1'] = 'Sum of first 100 lines:'ws['A2'] = 123.1ws['A3'] = 456.2ws['B2'] = "=SUM(A1:A100)"wb.save(var_path_excel_file)knio.output_tables[0] = knio.input_tables[0] locate and create/data/ folderwith absolute pathsv_path_excel_filecreate filedemo1.xlsxv_path*demo 1 Collect LocalMetadata Java EditVariable (simple) Variable toTable Row Excel Writer String to Path(Variable) 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: 2018-11-23
On NodePit since: 2019-11-17
Last update: 2024-04-24
Created with KNIME version: v4.7.0
Tags: excelpythonopenpyxl

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

Try NodePit Runner!