Icon

kn_​example_​python_​excel_​format_​copy

KNIME and OpenPyxl - copy formats from one sheet to another

KNIME and OpenPyxl - copy formats from one sheet to another

KNIME and OpenPyxl - copy formats from one sheet to another import knime.scripting.io as knioimport openpyxlfrom openpyxl.styles import Font, PatternFill, Border, Alignment, Sidev_data_path = knio.flow_variables['context.workflow.data-path']def copy_font(src_font): return Font(name=src_font.name, size=src_font.size, bold=src_font.bold, italic=src_font.italic, vertAlign=src_font.vertAlign, underline=src_font.underline, strike=src_font.strike, color=src_font.color)def copy_fill(src_fill): return PatternFill(patternType=src_fill.patternType, fgColor=src_fill.fgColor, bgColor=src_fill.bgColor)def copy_side(src_side): return Side(style=src_side.style, color=src_side.color)def copy_border(src_border): return Border(left=copy_side(src_border.left), right=copy_side(src_border.right), top=copy_side(src_border.top), bottom=copy_side(src_border.bottom), diagonal=copy_side(src_border.diagonal), diagonal_direction=src_border.diagonal_direction)def copy_alignment(src_alignment): return Alignment(horizontal=src_alignment.horizontal, vertical=src_alignment.vertical, text_rotation=src_alignment.text_rotation, wrap_text=src_alignment.wrap_text, shrink_to_fit=src_alignment.shrink_to_fit, indent=src_alignment.indent)# Load the workbookwb = openpyxl.load_workbook(v_data_path + 'file1.xlsx')# Get the source and destination sheetssource_sheet = wb['default_1']destination_sheet = wb['default_2']# Loop through the rows and columns of the source sheetfor row in source_sheet.iter_rows(): for cell in row: # Get the corresponding cell in the destination sheet dest_cell = destination_sheet.cell(row=cell.row, column=cell.column) # Copy the styles, font, alignment, and other formatting properties dest_cell.font = copy_font(cell.font) dest_cell.fill = copy_fill(cell.fill) dest_cell.border = copy_border(cell.border) dest_cell.alignment = copy_alignment(cell.alignment)# Save the workbookwb.save(v_data_path + 'your_workbook_with_copied_formats_1.xlsx') import knime.scripting.io as knioimport openpyxlfrom openpyxl.styles import Font, PatternFill, Border, Alignment, Sidev_data_path = knio.flow_variables['context.workflow.data-path']def copy_font(src_font): return Font(name=src_font.name, size=src_font.size, bold=src_font.bold, italic=src_font.italic, vertAlign=src_font.vertAlign, underline=src_font.underline, strike=src_font.strike, color=src_font.color)def copy_fill(src_fill): return PatternFill(patternType=src_fill.patternType, fgColor=src_fill.fgColor, bgColor=src_fill.bgColor)def copy_side(src_side): return Side(style=src_side.style, color=src_side.color)def copy_border(src_border): return Border(left=copy_side(src_border.left), right=copy_side(src_border.right), top=copy_side(src_border.top), bottom=copy_side(src_border.bottom), diagonal=copy_side(src_border.diagonal), diagonal_direction=src_border.diagonal_direction)def copy_alignment(src_alignment): return Alignment(horizontal=src_alignment.horizontal, vertical=src_alignment.vertical, text_rotation=src_alignment.text_rotation, wrap_text=src_alignment.wrap_text, shrink_to_fit=src_alignment.shrink_to_fit, indent=src_alignment.indent)# Load the workbookwb = openpyxl.load_workbook(v_data_path + 'file2.xlsx')# Get the source and destination sheetssource_sheet = wb['default_1']destination_sheet = wb['default_2']# Get the header row (first row) formatting from 'Sheet1'header_format = {cell.column_letter: {'font': copy_font(cell.font),'fill': copy_fill(cell.fill),'border': copy_border(cell.border),'alignment': copy_alignment(cell.alignment)} for cell in source_sheet[1]}# Get the first data row (second row) formatting from 'Sheet1'data_format = {cell.column_letter: {'font': copy_font(cell.font),'fill': copy_fill(cell.fill),'border': copy_border(cell.border),'alignment': copy_alignment(cell.alignment)} for cell in source_sheet[2]}# Apply header and data formatting to 'Sheet2'for row in destination_sheet.iter_rows(): for cell in row: if cell.row == 1: cell.font = header_format[cell.column_letter]['font'] cell.fill = header_format[cell.column_letter]['fill'] cell.border = header_format[cell.column_letter]['border'] cell.alignment = header_format[cell.column_letter]['alignment'] else: cell.font = data_format[cell.column_letter]['font'] cell.fill = data_format[cell.column_letter]['fill'] cell.border = data_format[cell.column_letter]['border'] cell.alignment = data_format[cell.column_letter]['alignment']# Autosize columns in the destination sheet# autosize_columns(destination_sheet)# Save the workbookwb.save(v_data_path + 'your_workbook_with_copied_formats_2.xlsx') This program is designed to apply the formatting from one Excel sheet ('default_1') to another sheet ('default_2') without copying the content. It takes theformatting from the header (first row) of 'default_1' and applies it to the header of 'default_2'. Then, it applies the formatting from the first data row (secondrow) of 'Sheet1' to all other rows in 'default_2'. The content of 'default_2' remains unchanged, only the formatting is updated.The program is implemented using the openpyxl library in Python, which is widely used for handling Excel files. It provides various functions and classes forworking with Excel files, such as loading workbooks, accessing sheets, iterating through rows and columns, and modifying cell styles and formatting. locate and create/data/ folderwith absolute pathsdate_nowdate_nowdate_nowdate_nowfile1.xlsxinitial "default_1" file1.xlsxoverwrite" default_2" so no format is thereApply formatfrom default_1to default_2export toyour_workbook_with_copied_formats_1.xlsxApply format from first tow Rowsfrom default_1to default_2export toyour_workbook_with_copied_formats_2.xlsxfile2.xlsxinitial "default_1" file2.xlsxoverwrite" default_2" so no format is thereonly 2 Rows Collect LocalMetadata Data Generator ConstantValue Column Create Date&TimeRange Table Rowto Variable String to Date&Time Excel Writer Excel Writer Python Script Python Script Excel Writer Excel Writer Row Filter KNIME and OpenPyxl - copy formats from one sheet to another import knime.scripting.io as knioimport openpyxlfrom openpyxl.styles import Font, PatternFill, Border, Alignment, Sidev_data_path = knio.flow_variables['context.workflow.data-path']def copy_font(src_font): return Font(name=src_font.name, size=src_font.size, bold=src_font.bold, italic=src_font.italic, vertAlign=src_font.vertAlign, underline=src_font.underline, strike=src_font.strike, color=src_font.color)def copy_fill(src_fill): return PatternFill(patternType=src_fill.patternType, fgColor=src_fill.fgColor, bgColor=src_fill.bgColor)def copy_side(src_side): return Side(style=src_side.style, color=src_side.color)def copy_border(src_border): return Border(left=copy_side(src_border.left), right=copy_side(src_border.right), top=copy_side(src_border.top), bottom=copy_side(src_border.bottom), diagonal=copy_side(src_border.diagonal), diagonal_direction=src_border.diagonal_direction)def copy_alignment(src_alignment): return Alignment(horizontal=src_alignment.horizontal, vertical=src_alignment.vertical, text_rotation=src_alignment.text_rotation, wrap_text=src_alignment.wrap_text, shrink_to_fit=src_alignment.shrink_to_fit, indent=src_alignment.indent)# Load the workbookwb = openpyxl.load_workbook(v_data_path + 'file1.xlsx')# Get the source and destination sheetssource_sheet = wb['default_1']destination_sheet = wb['default_2']# Loop through the rows and columns of the source sheetfor row in source_sheet.iter_rows(): for cell in row: # Get the corresponding cell in the destination sheet dest_cell = destination_sheet.cell(row=cell.row, column=cell.column) # Copy the styles, font, alignment, and other formatting properties dest_cell.font = copy_font(cell.font) dest_cell.fill = copy_fill(cell.fill) dest_cell.border = copy_border(cell.border) dest_cell.alignment = copy_alignment(cell.alignment)# Save the workbookwb.save(v_data_path + 'your_workbook_with_copied_formats_1.xlsx') import knime.scripting.io as knioimport openpyxlfrom openpyxl.styles import Font, PatternFill, Border, Alignment, Sidev_data_path = knio.flow_variables['context.workflow.data-path']def copy_font(src_font): return Font(name=src_font.name, size=src_font.size, bold=src_font.bold, italic=src_font.italic, vertAlign=src_font.vertAlign, underline=src_font.underline, strike=src_font.strike, color=src_font.color)def copy_fill(src_fill): return PatternFill(patternType=src_fill.patternType, fgColor=src_fill.fgColor, bgColor=src_fill.bgColor)def copy_side(src_side): return Side(style=src_side.style, color=src_side.color)def copy_border(src_border): return Border(left=copy_side(src_border.left), right=copy_side(src_border.right), top=copy_side(src_border.top), bottom=copy_side(src_border.bottom), diagonal=copy_side(src_border.diagonal), diagonal_direction=src_border.diagonal_direction)def copy_alignment(src_alignment): return Alignment(horizontal=src_alignment.horizontal, vertical=src_alignment.vertical, text_rotation=src_alignment.text_rotation, wrap_text=src_alignment.wrap_text, shrink_to_fit=src_alignment.shrink_to_fit, indent=src_alignment.indent)# Load the workbookwb = openpyxl.load_workbook(v_data_path + 'file2.xlsx')# Get the source and destination sheetssource_sheet = wb['default_1']destination_sheet = wb['default_2']# Get the header row (first row) formatting from 'Sheet1'header_format = {cell.column_letter: {'font': copy_font(cell.font),'fill': copy_fill(cell.fill),'border': copy_border(cell.border),'alignment': copy_alignment(cell.alignment)} for cell in source_sheet[1]}# Get the first data row (second row) formatting from 'Sheet1'data_format = {cell.column_letter: {'font': copy_font(cell.font),'fill': copy_fill(cell.fill),'border': copy_border(cell.border),'alignment': copy_alignment(cell.alignment)} for cell in source_sheet[2]}# Apply header and data formatting to 'Sheet2'for row in destination_sheet.iter_rows(): for cell in row: if cell.row == 1: cell.font = header_format[cell.column_letter]['font'] cell.fill = header_format[cell.column_letter]['fill'] cell.border = header_format[cell.column_letter]['border'] cell.alignment = header_format[cell.column_letter]['alignment'] else: cell.font = data_format[cell.column_letter]['font'] cell.fill = data_format[cell.column_letter]['fill'] cell.border = data_format[cell.column_letter]['border'] cell.alignment = data_format[cell.column_letter]['alignment']# Autosize columns in the destination sheet# autosize_columns(destination_sheet)# Save the workbookwb.save(v_data_path + 'your_workbook_with_copied_formats_2.xlsx') This program is designed to apply the formatting from one Excel sheet ('default_1') to another sheet ('default_2') without copying the content. It takes theformatting from the header (first row) of 'default_1' and applies it to the header of 'default_2'. Then, it applies the formatting from the first data row (secondrow) of 'Sheet1' to all other rows in 'default_2'. The content of 'default_2' remains unchanged, only the formatting is updated.The program is implemented using the openpyxl library in Python, which is widely used for handling Excel files. It provides various functions and classes forworking with Excel files, such as loading workbooks, accessing sheets, iterating through rows and columns, and modifying cell styles and formatting. locate and create/data/ folderwith absolute pathsdate_nowdate_nowdate_nowdate_nowfile1.xlsxinitial "default_1" file1.xlsxoverwrite" default_2" so no format is thereApply formatfrom default_1to default_2export toyour_workbook_with_copied_formats_1.xlsxApply format from first tow Rowsfrom default_1to default_2export toyour_workbook_with_copied_formats_2.xlsxfile2.xlsxinitial "default_1" file2.xlsxoverwrite" default_2" so no format is thereonly 2 RowsCollect LocalMetadata Data Generator ConstantValue Column Create Date&TimeRange Table Rowto Variable String to Date&Time Excel Writer Excel Writer Python Script Python Script Excel Writer Excel Writer Row Filter

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: 2023-03-28
On NodePit since: 2023-03-29
Last update: 2024-04-26
Created with KNIME version: v4.7.2
Tags: ChatGPTExcelPythonOpenPyxlheaderformat

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

Try NodePit Runner!