Icon

kn_​example_​python_​excel_​enter_​formula

KNIME - openpyxl and xlsxwriter to add formulas to excel files

use python package openpyxl to add formulas to excel files

https://forum.knime.com/t/excel-writing-functions-dynamic-files-export/15530/2?u=mlauber71 # Copy input to output################################################################################ A simple example of some of the features of the XlsxWriter Python module.## Copyright 2013-2018, John McNamara, jmcnamara@cpan.org# https://xlsxwriter.readthedocs.io/example_demo.htmlimport xlsxwriterimport osvar_workflow_path = flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = var_workflow_path + os.path.sep + "demo2.xlsx"print("var_path_excel_file : ", var_path_excel_file )# Create an new Excel file and add a worksheet.workbook = xlsxwriter.Workbook(var_path_excel_file)worksheet = workbook.add_worksheet()txt = "ACTION"worksheet.write("B1", txt)worksheet.data_validation("B2", {"validate": "list", "source": ["Action1", "Action2"]})workbook.close()output_table = input_table.copy() # Copy input to output################################################################################ A simple example of some of the features of the XlsxWriter Pythonmodule.## Copyright 2013-2018, John McNamara, jmcnamara@cpan.org# https://xlsxwriter.readthedocs.io/example_demo.htmlimport xlsxwriterimport osfrom openpyxl.formula.translate import Translatorvar_workflow_path = flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = var_workflow_path + os.path.sep + "demo1.xlsx"print("var_path_excel_file : ", var_path_excel_file )# Create an new Excel file and add a worksheet.workbook = xlsxwriter.Workbook(var_path_excel_file)worksheet = workbook.add_worksheet()# Widen the first column to make the text clearer.worksheet.set_column('A:A', 20)# Add a bold format to use to highlight cells.bold = workbook.add_format({'bold': True})# Write some simple text.worksheet.write('A1', 'Values')# Text with formatting.worksheet.write('B1', 'Sum of first 100 lines', bold)# Write some numbers, with row/column notation.worksheet.write(1, 0, 123.1)worksheet.write(2, 0, 456.2)# insert Formulaworksheet.write('B2', "=SUM(A1:A100)")# Insert an image.# worksheet.insert_image('B5', 'logo.png')workbook.close()output_table = input_table.copy() use python package openpyxl to add formulas to excel files demo 2using xlsxwriterdetermine local pathdemo 1 Python Script (1⇒1) Extract ContextProperties Variable toTable Row Python Script (1⇒1) https://forum.knime.com/t/excel-writing-functions-dynamic-files-export/15530/2?u=mlauber71 # Copy input to output################################################################################ A simple example of some of the features of the XlsxWriter Python module.## Copyright 2013-2018, John McNamara, jmcnamara@cpan.org# https://xlsxwriter.readthedocs.io/example_demo.htmlimport xlsxwriterimport osvar_workflow_path = flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = var_workflow_path + os.path.sep + "demo2.xlsx"print("var_path_excel_file : ", var_path_excel_file )# Create an new Excel file and add a worksheet.workbook = xlsxwriter.Workbook(var_path_excel_file)worksheet = workbook.add_worksheet()txt = "ACTION"worksheet.write("B1", txt)worksheet.data_validation("B2", {"validate": "list", "source": ["Action1", "Action2"]})workbook.close()output_table = input_table.copy() # Copy input to output################################################################################ A simple example of some of the features of the XlsxWriter Pythonmodule.## Copyright 2013-2018, John McNamara, jmcnamara@cpan.org# https://xlsxwriter.readthedocs.io/example_demo.htmlimport xlsxwriterimport osfrom openpyxl.formula.translate import Translatorvar_workflow_path = flow_variables['context.workflow.absolute-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = var_workflow_path + os.path.sep + "demo1.xlsx"print("var_path_excel_file : ", var_path_excel_file )# Create an new Excel file and add a worksheet.workbook = xlsxwriter.Workbook(var_path_excel_file)worksheet = workbook.add_worksheet()# Widen the first column to make the text clearer.worksheet.set_column('A:A', 20)# Add a bold format to use to highlight cells.bold = workbook.add_format({'bold': True})# Write some simple text.worksheet.write('A1', 'Values')# Text with formatting.worksheet.write('B1', 'Sum of first 100 lines', bold)# Write some numbers, with row/column notation.worksheet.write(1, 0, 123.1)worksheet.write(2, 0, 456.2)# insert Formulaworksheet.write('B2', "=SUM(A1:A100)")# Insert an image.# worksheet.insert_image('B5', 'logo.png')workbook.close()output_table = input_table.copy() use python package openpyxl to add formulas to excel files demo 2using xlsxwriterdetermine local pathdemo 1Python Script (1⇒1) Extract ContextProperties Variable toTable Row Python Script (1⇒1)

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-12
Last update: 2024-04-18
Created with KNIME version: v4.0.2
Tags: xlsxwriterexcelpythonopenpyxl

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

Try NodePit Runner!