Untitled
unknown
plain_text
a year ago
3.2 kB
5
Indexable
import openpyxl from openpyxl.utils import get_column_letter from openpyxl.formula.translate import Translator from copy import copy def copy_headers_and_adjusted_formulas(source_file, source_sheet_name, destination_file, destination_sheet_name, src_start_col, dest_start_col): # Load the source workbook and sheet src_wb = openpyxl.load_workbook(source_file) src_sheet = src_wb[source_sheet_name] # Load or create the destination workbook and sheet try: dest_wb = openpyxl.load_workbook(destination_file) except FileNotFoundError: dest_wb = openpyxl.Workbook() # Create new workbook if it doesn't exist # Get or create the destination sheet if destination_sheet_name in dest_wb.sheetnames: dest_sheet = dest_wb[destination_sheet_name] else: dest_sheet = dest_wb.create_sheet(destination_sheet_name) # Variables for rows header_row_src = 9 header_row_dest = 1 formula_row_src = 10 formula_row_dest = 2 # Copy headers from specific column in source to the destination starting from specific column for col_offset, src_col in enumerate(range(src_start_col, src_sheet.max_column + 1), start=0): src_cell = src_sheet.cell(row=header_row_src, column=src_col) dest_col = dest_start_col + col_offset dest_cell = dest_sheet.cell(row=header_row_dest, column=dest_col) dest_cell.value = src_cell.value # Copy style if present if src_cell.has_style: dest_cell.font = copy(src_cell.font) dest_cell.border = copy(src_cell.border) dest_cell.fill = copy(src_cell.fill) dest_cell.number_format = copy(src_cell.number_format) dest_cell.protection = copy(src_cell.protection) dest_cell.alignment = copy(src_cell.alignment) # Copy and adjust formulas from specific column in source to the destination starting from specific column for col_offset, src_col in enumerate(range(src_start_col, src_sheet.max_column + 1), start=0): src_cell = src_sheet.cell(row=formula_row_src, column=src_col) dest_col = dest_start_col + col_offset dest_cell = dest_sheet.cell(row=formula_row_dest, column=dest_col) if src_cell.value and isinstance(src_cell.value, str) and src_cell.value.startswith('='): # Adjust formula to new position original_formula = src_cell.value new_formula = Translator(original_formula, origin=src_cell.coordinate).translate_formula(dest_cell.coordinate) dest_cell.value = new_formula else: dest_cell.value = src_cell.value # Copy value directly if not a formula # Save the destination workbook dest_wb.save(destination_file) # Usage source_file = 'source.xlsx' source_sheet_name = 'Sheet1' destination_file = 'destination.xlsx' destination_sheet_name = 'Sheet1' src_start_col = 2 # Start from the second column in the source dest_start_col = 1 # Start from the first column in the destination copy_headers_and_adjusted_formulas(source_file, source_sheet_name, destination_file, destination_sheet_name, src_start_col, dest_start_col)
Editor is loading...
Leave a Comment