Untitled

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)