Icon

kn_​example_​python_​excel_​table_​object_​filter

KNIME and Python's openpyxl to define and name a Data area in Excel (using the bundled Python version)

KNIME and Python's openpyxl to define and name a Data area in Excel (using the bundled Python version)
https://forum.knime.com/t/write-knime-table-to-excel-as-table-proposal/14044/10?u=mlauber71

KNIME and Python's openpyxl to define and name a Data area in Excel (using the bundled Python version)https://forum.knime.com/t/write-knime-table-to-excel-as-table-proposal/14044/10?u=mlauber71 ################################################################################ https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html# import the KNIME moduleimport knime.scripting.io as knioimport numpy as npimport pandas as pdfrom openpyxl import Workbookfrom openpyxl.worksheet.table import Table, TableStyleInfoimport osvar_workflow_path = knio.flow_variables['context.workflow.data-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = var_workflow_path + "table.xlsx"print("var_path_excel_file : ", var_path_excel_file )from openpyxl.worksheet.table import Table, TableStyleInfowb = Workbook()ws = wb.activedata = [ ['Apples', 10000, 5000, 8000, 6000], ['Pears', 2000, 3000, 4000, 5000], ['Bananas', 6000, 6000, 6500, 6000], ['Oranges', 500, 300, 200, 700],]# add column headings. NB. these must be stringsws.append(["Fruit", "2011", "2012", "2013", "2014"])for row in data: ws.append(row)tab = Table(displayName="Table1", ref="A1:E5")# Add a default style with striped rows and banded columnsstyle = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True)tab.tableStyleInfo = stylews.add_table(tab)wb.save(var_path_excel_file) ################################################################################ https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html# import the KNIME moduleimport knime.scripting.io as knioimport numpy as npimport pandas as pdfrom openpyxl import Workbookfrom openpyxl import load_workbookfrom openpyxl.worksheet.table import Table, TableStyleInfoimport os# change this to get the correct range of the table objectvar_range = "A1:F" + str(knio.flow_variables['v_num_of_lines'])var_workflow_path = knio.flow_variables['context.workflow.data-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = var_workflow_path + "table.xlsx"print("var_path_excel_file : ", var_path_excel_file )wb = load_workbook(var_path_excel_file)# activate sheet2ws = wb["Sheet2"]# activate the ws 'data'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = stab = Table(displayName="All", ref=var_range)# Add a default style with striped rows and banded columnsstyle = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True)tab.tableStyleInfo = stylews.add_table(tab)wb.save(var_path_excel_file) ################################################################################ https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html# import the KNIME moduleimport knime.scripting.io as knioimport numpy as npimport pandas as pdfrom openpyxl import Workbookfrom openpyxl.worksheet.table import Table, TableStyleInfoimport osvar_workflow_path = knio.flow_variables['context.workflow.data-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = var_workflow_path + "filtered.xlsx"print("var_path_excel_file : ", var_path_excel_file )wb = Workbook()ws = wb.activedata = [ ["Fruit", "Quantity"], ["Kiwi", 3], ["Grape", 15], ["Apple", 3], ["Peach", 3], ["Pomegranate", 3], ["Pear", 3], ["Tangerine", 3], ["Blueberry", 3], ["Mango", 3], ["Watermelon", 3], ["Blackberry", 3], ["Orange", 3], ["Raspberry", 3], ["Banana", 3]]for r in data: ws.append(r)ws.auto_filter.ref = "A1:B15"ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"])ws.auto_filter.add_sort_condition("B2:B15")wb.save(var_path_excel_file) locate and create/data/ folderwith absolute pathsno of linesv_num_of_linestable.xlsxfiltered.xlsxtable.xlsxtable.xlsxappend "Sheet2"v_num_of_linestable.xlsxbring back the table object Collect LocalMetadata Concatenate ConstantValue Column GroupBy Column Rename Math Formula Python Script Python Script Excel Reader Excel Writer Table Rowto Variable Python Script KNIME and Python's openpyxl to define and name a Data area in Excel (using the bundled Python version)https://forum.knime.com/t/write-knime-table-to-excel-as-table-proposal/14044/10?u=mlauber71 ################################################################################ https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html# import the KNIME moduleimport knime.scripting.io as knioimport numpy as npimport pandas as pdfrom openpyxl import Workbookfrom openpyxl.worksheet.table import Table, TableStyleInfoimport osvar_workflow_path = knio.flow_variables['context.workflow.data-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = var_workflow_path + "table.xlsx"print("var_path_excel_file : ", var_path_excel_file )from openpyxl.worksheet.table import Table, TableStyleInfowb = Workbook()ws = wb.activedata = [ ['Apples', 10000, 5000, 8000, 6000], ['Pears', 2000, 3000, 4000, 5000], ['Bananas', 6000, 6000, 6500, 6000], ['Oranges', 500, 300, 200, 700],]# add column headings. NB. these must be stringsws.append(["Fruit", "2011", "2012", "2013", "2014"])for row in data: ws.append(row)tab = Table(displayName="Table1", ref="A1:E5")# Add a default style with striped rows and banded columnsstyle = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True)tab.tableStyleInfo = stylews.add_table(tab)wb.save(var_path_excel_file) ################################################################################ https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html# import the KNIME moduleimport knime.scripting.io as knioimport numpy as npimport pandas as pdfrom openpyxl import Workbookfrom openpyxl import load_workbookfrom openpyxl.worksheet.table import Table, TableStyleInfoimport os# change this to get the correct range of the table objectvar_range = "A1:F" + str(knio.flow_variables['v_num_of_lines'])var_workflow_path = knio.flow_variables['context.workflow.data-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = var_workflow_path + "table.xlsx"print("var_path_excel_file : ", var_path_excel_file )wb = load_workbook(var_path_excel_file)# activate sheet2ws = wb["Sheet2"]# activate the ws 'data'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = stab = Table(displayName="All", ref=var_range)# Add a default style with striped rows and banded columnsstyle = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=True)tab.tableStyleInfo = stylews.add_table(tab)wb.save(var_path_excel_file) ################################################################################ https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html# import the KNIME moduleimport knime.scripting.io as knioimport numpy as npimport pandas as pdfrom openpyxl import Workbookfrom openpyxl.worksheet.table import Table, TableStyleInfoimport osvar_workflow_path = knio.flow_variables['context.workflow.data-path']print("var_workflow_path : ", var_workflow_path )var_path_excel_file = var_workflow_path + "filtered.xlsx"print("var_path_excel_file : ", var_path_excel_file )wb = Workbook()ws = wb.activedata = [ ["Fruit", "Quantity"], ["Kiwi", 3], ["Grape", 15], ["Apple", 3], ["Peach", 3], ["Pomegranate", 3], ["Pear", 3], ["Tangerine", 3], ["Blueberry", 3], ["Mango", 3], ["Watermelon", 3], ["Blackberry", 3], ["Orange", 3], ["Raspberry", 3], ["Banana", 3]]for r in data: ws.append(r)ws.auto_filter.ref = "A1:B15"ws.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"])ws.auto_filter.add_sort_condition("B2:B15")wb.save(var_path_excel_file) locate and create/data/ folderwith absolute pathsno of linesv_num_of_linestable.xlsxfiltered.xlsxtable.xlsxtable.xlsxappend "Sheet2"v_num_of_linestable.xlsxbring back the table objectCollect LocalMetadata Concatenate ConstantValue Column GroupBy Column Rename Math Formula Python Script Python Script Excel Reader Excel Writer Table Rowto 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: 2021-12-10
Last update: 2024-04-27
Created with KNIME version: v4.7.1
Tags: openpyxlexcelareafilterdatapython

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

Try NodePit Runner!