Icon

kn_​example_​python_​excel_​format_​header

Excel file header format with KNIME and OpenPyxl

Excel file header format with KNIME and OpenPyxl
Freeze the top row, autosize the columns and set a style

Excel file header format with KNIME and OpenPyxlFreeze the top row, autosize the columns and set a style import knime.scripting.io as knio# https://openpyxl.readthedocs.io/en/stable/tutorial.html#loading-from-a-file# conda install -c anaconda openpyxlfrom openpyxl import load_workbookfrom openpyxl import Workbookfrom openpyxl.styles import NamedStyle, Border, Side, PatternFill, Font, GradientFill, Alignmentimport openpyxlimport osvar_data_path = knio.flow_variables['v_path_data_folder']# print("var_data_path: ", var_data_path )var_path_excel_file = var_data_path + os.path.sep + "my_data.xlsx"# print("var_path_excel_file: ", var_path_excel_file )var_sheet_name = knio.flow_variables['var_sheet_name']var_new_excel_file = knio.flow_variables['v_path_new_excel_file']var_no_rows = knio.flow_variables['v_max_rows']var_no_columns = knio.flow_variables['Number Columns']# https://stackoverflow.com/questions/51128029/copying-to-a-specific-sheet-openpyxl-destination-sheet-ignored-when-using-copwb = load_workbook(var_path_excel_file)# print list of current sheet namesv_list_sheetnames = wb.sheetnames# print("v_list_sheetnames: ", v_list_sheetnames)# activate the worksheetws = wb[var_sheet_name]# activate the ws 'data'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = s# experiemnt with styles# https://openpyxl.readthedocs.io/en/stable/styles.html# work with named Stylesstyle_header = NamedStyle(name="style_header")style_header.font = Font(name= 'Arial', bold=True, size=12, color="00FFFF00")# register named style for Fontswb.add_named_style(style_header)# https://stackoverflow.com/questions/59877670/how-to-give-font-color-to-a-range-of-cells-columns-and-rows-in-excel-worksheetfor row in ws.iter_rows(min_row=1, max_col=var_no_columns, max_row=var_no_rows): #max row and col (range) for cell in row: # apply the named style for the Font cell.style = 'style_header' # fill with red background cell.fill = PatternFill(start_color="FF0000", fill_type = "solid") #html colors # https://stackoverflow.com/questions/25588918/how-to-freeze-entire-header-row-in-openpyxlws.freeze_panes = ws["A2"]# https://stackoverflow.com/questions/39529662/python-automatically-adjust-width-of-an-excel-files-columnsfor column_cells in ws.columns: length = max(len(str(cell.value)) for cell in column_cells) ws.column_dimensions[column_cells[0].column_letter].width = length# save the wb.save(var_new_excel_file)# close the workbookwb.close() just make sure the example has a clean start .... data1determine local pathv_path_data_folderlist filemy_data*try todeletethe existing filemy_data.xlsxvar_new_excel_file"my_data_new.xlsx"use some styles on Excel file headerv_max_rows=> Maximum Number of RowsNumber of Columnsvar_sheet_name" Sheet1" Data Generator Extract ContextProperties Java Edit Variable List Files/Folders DeleteFiles/Folders Excel Writer Table Rowto Variable Try (VariablePorts) Catch Errors(Var Ports) Merge Variables StringConfiguration Merge Variables Python Script Extract TableDimension IntegerConfiguration Table Columnto Variable StringConfiguration Merge Variables Excel file header format with KNIME and OpenPyxlFreeze the top row, autosize the columns and set a style import knime.scripting.io as knio# https://openpyxl.readthedocs.io/en/stable/tutorial.html#loading-from-a-file# conda install -c anaconda openpyxlfrom openpyxl import load_workbookfrom openpyxl import Workbookfrom openpyxl.styles import NamedStyle, Border, Side, PatternFill, Font, GradientFill, Alignmentimport openpyxlimport osvar_data_path = knio.flow_variables['v_path_data_folder']# print("var_data_path: ", var_data_path )var_path_excel_file = var_data_path + os.path.sep + "my_data.xlsx"# print("var_path_excel_file: ", var_path_excel_file )var_sheet_name = knio.flow_variables['var_sheet_name']var_new_excel_file = knio.flow_variables['v_path_new_excel_file']var_no_rows = knio.flow_variables['v_max_rows']var_no_columns = knio.flow_variables['Number Columns']# https://stackoverflow.com/questions/51128029/copying-to-a-specific-sheet-openpyxl-destination-sheet-ignored-when-using-copwb = load_workbook(var_path_excel_file)# print list of current sheet namesv_list_sheetnames = wb.sheetnames# print("v_list_sheetnames: ", v_list_sheetnames)# activate the worksheetws = wb[var_sheet_name]# activate the ws 'data'for s in range(len(wb.sheetnames)): if wb.sheetnames[s] == ws: breakwb.active = s# experiemnt with styles# https://openpyxl.readthedocs.io/en/stable/styles.html# work with named Stylesstyle_header = NamedStyle(name="style_header")style_header.font = Font(name= 'Arial', bold=True, size=12, color="00FFFF00")# register named style for Fontswb.add_named_style(style_header)# https://stackoverflow.com/questions/59877670/how-to-give-font-color-to-a-range-of-cells-columns-and-rows-in-excel-worksheetfor row in ws.iter_rows(min_row=1, max_col=var_no_columns, max_row=var_no_rows): #max row and col (range) for cell in row: # apply the named style for the Font cell.style = 'style_header' # fill with red background cell.fill = PatternFill(start_color="FF0000", fill_type = "solid") #html colors # https://stackoverflow.com/questions/25588918/how-to-freeze-entire-header-row-in-openpyxlws.freeze_panes = ws["A2"]# https://stackoverflow.com/questions/39529662/python-automatically-adjust-width-of-an-excel-files-columnsfor column_cells in ws.columns: length = max(len(str(cell.value)) for cell in column_cells) ws.column_dimensions[column_cells[0].column_letter].width = length# save the wb.save(var_new_excel_file)# close the workbookwb.close() just make sure the example has a clean start .... data1determine local pathv_path_data_folderlist filemy_data*try todeletethe existing filemy_data.xlsxvar_new_excel_file"my_data_new.xlsx"use some styles on Excel file headerv_max_rows=> Maximum Number of RowsNumber of Columnsvar_sheet_name" Sheet1" Data Generator Extract ContextProperties Java Edit Variable List Files/Folders DeleteFiles/Folders Excel Writer Table Rowto Variable Try (VariablePorts) Catch Errors(Var Ports) Merge Variables StringConfiguration Merge Variables Python Script Extract TableDimension IntegerConfiguration Table Columnto Variable StringConfiguration Merge Variables

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: 2022-10-31
On NodePit since: 2022-11-01
Last update: 2024-04-27
Created with KNIME version: v4.7.7
Tags: pythonopenpyxlknimestyleheader

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

Try NodePit Runner!