Icon

kn_​forum_​38380_​excel_​re_​evaluate_​formulas

try to find a workaround to re-calculate Excel formulas on sheets other than the one being imported

try to find a workaround to re-calculate Excel formulas on sheets other than the one being imported
(which is possible in the Excel-Reader) - WORK IN PROGRESS

# Python code to force a calculation of *all* formulas in the Excel file and *replace* them with the values# so handle with care. Also this module will make all sheet names into upper-case!!!# import the KNIME moduleimport knime_io as knioimport numpy as npimport pandas as pdimport os# https://newbedev.com/calculating-excel-sheets-without-opening-them-openpyxl-or-xlwtimport formulasvar_data_path = knio.flow_variables['var_path_data']print("var_data_path : ", var_data_path )var_path_excel_file = var_data_path + "test.xlsx"print("var_path_excel_file : ", var_path_excel_file )#The variable spreadsheet provides the full path with filename to the excel spreadsheet with unevaluated formulaexl_model = formulas.ExcelModel().loads(var_path_excel_file).finish()xl_model.calculate()xl_model.write(dirpath=var_data_path) try to find a workaround to re-calculate Excel formulas on sheets other than the one being imported(which is possible in the Excel-Reader) - WORK IN PROGRESS ################################################################################ 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.html# import the KNIME moduleimport knime_io as knioimport numpy as npimport pandas as pdimport openpyxlimport osfrom openpyxl.formula.translate import Translatorfrom openpyxl.utils import get_column_letterfrom openpyxl.styles import Font, Fillvar_data_path = knio.flow_variables['var_path_data']print("var_data_path : ", var_data_path )var_path_excel_file = var_data_path + "test.xlsx"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 = 'sheet_with_formulas'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)v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames) for i in v_list_sheetnames: i = i.upper() if i == ws_sheet_to_remove.upper(): std=wb.get_sheet_by_name(i) print("remove: ", i) wb.remove_sheet(std)v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)for i in v_list_sheetnames: i = i.lower() if i == ws_sheet_to_remove.lower(): std=wb.get_sheet_by_name(i) print("remove: ", i) wb.remove_sheet(std) v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)ws = wb.create_sheet("sheet_with_formulas")# 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'] = "Value from sheet2 cell A1"ws['A2'] = "=sheet2!A1"# Text with formatting.ws['B1'] = "Sum of first 100 lines column2 from sheet3:"ws['B2'] = "=SUM(sheet3!B2:B100)"# Text with formatting.ws['C1'] = "Sum of first 100 lines column2 from sheet1:"ws['C2'] = "=SUM(sheet1!B2:B100)"wb.save(var_path_excel_file)wb.close() create small tabletest.xlsxoverwrite andcreate "Sheet1"append "sheet2"change some datafill "sheet3"and re-calculateTRUE => FALSEemptyempty "sheet3"determinepath and name of workflowconda.environmentcreate new"sheet_with_formulas"and add some formulasread the new sheet"sheet_with_formulas"WITHOUT recalculating itread the new sheet"sheet_with_formulas"WITHOUT recalculating itempty tableappend to"sheet_with_formulas"read the new sheet"sheet_with_formulas"WITHOUT recalculating itremove blank to create an empty string Table Creator Excel Writer Excel Writer Math Formula Write To ExcelTemplate Rule-basedRow Filter Excel Writer Extract ContextProperties Conda EnvironmentPropagation Merge Variables Python Script(Labs) determine paths Excel Reader Excel Reader Table Creator Write To ExcelTemplate Excel Reader String Manipulation # Python code to force a calculation of *all* formulas in the Excel file and *replace* them with the values# so handle with care. Also this module will make all sheet names into upper-case!!!# import the KNIME moduleimport knime_io as knioimport numpy as npimport pandas as pdimport os# https://newbedev.com/calculating-excel-sheets-without-opening-them-openpyxl-or-xlwtimport formulasvar_data_path = knio.flow_variables['var_path_data']print("var_data_path : ", var_data_path )var_path_excel_file = var_data_path + "test.xlsx"print("var_path_excel_file : ", var_path_excel_file )#The variable spreadsheet provides the full path with filename to the excel spreadsheet with unevaluated formulaexl_model = formulas.ExcelModel().loads(var_path_excel_file).finish()xl_model.calculate()xl_model.write(dirpath=var_data_path) try to find a workaround to re-calculate Excel formulas on sheets other than the one being imported(which is possible in the Excel-Reader) - WORK IN PROGRESS ################################################################################ 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.html# import the KNIME moduleimport knime_io as knioimport numpy as npimport pandas as pdimport openpyxlimport osfrom openpyxl.formula.translate import Translatorfrom openpyxl.utils import get_column_letterfrom openpyxl.styles import Font, Fillvar_data_path = knio.flow_variables['var_path_data']print("var_data_path : ", var_data_path )var_path_excel_file = var_data_path + "test.xlsx"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 = 'sheet_with_formulas'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)v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames) for i in v_list_sheetnames: i = i.upper() if i == ws_sheet_to_remove.upper(): std=wb.get_sheet_by_name(i) print("remove: ", i) wb.remove_sheet(std)v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)for i in v_list_sheetnames: i = i.lower() if i == ws_sheet_to_remove.lower(): std=wb.get_sheet_by_name(i) print("remove: ", i) wb.remove_sheet(std) v_list_sheetnames = wb.sheetnamesprint(v_list_sheetnames)ws = wb.create_sheet("sheet_with_formulas")# 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'] = "Value from sheet2 cell A1"ws['A2'] = "=sheet2!A1"# Text with formatting.ws['B1'] = "Sum of first 100 lines column2 from sheet3:"ws['B2'] = "=SUM(sheet3!B2:B100)"# Text with formatting.ws['C1'] = "Sum of first 100 lines column2 from sheet1:"ws['C2'] = "=SUM(sheet1!B2:B100)"wb.save(var_path_excel_file)wb.close() create small tabletest.xlsxoverwrite andcreate "Sheet1"append "sheet2"change some datafill "sheet3"and re-calculateTRUE => FALSEemptyempty "sheet3"determinepath and name of workflowconda.environmentcreate new"sheet_with_formulas"and add some formulasread the new sheet"sheet_with_formulas"WITHOUT recalculating itread the new sheet"sheet_with_formulas"WITHOUT recalculating itempty tableappend to"sheet_with_formulas"read the new sheet"sheet_with_formulas"WITHOUT recalculating itremove blank to create an empty stringTable Creator Excel Writer Excel Writer Math Formula Write To ExcelTemplate Rule-basedRow Filter Excel Writer Extract ContextProperties Conda EnvironmentPropagation Merge Variables Python Script(Labs) determine paths Excel Reader Excel Reader Table Creator Write To ExcelTemplate Excel Reader String Manipulation

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: 2021-12-22
On NodePit since: 2021-12-24
Last update: 2024-04-27
Created with KNIME version: v4.5.0
Tags: excelopenpyxlpythonformularecalculate

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

Try NodePit Runner!