Untitled

import pandas as pd
from openpyxl import load_workbook

def extend_formulas_with_data(excel_path, dataframe, output_path):
    # Load the workbook and the first sheet
    wb = load_workbook(excel_path)
    ws = wb.active
    
    # Assuming the first row is the header and the second row is the sample data with formulas
    header = [cell.value for cell in ws[1]]
    sample_row = ws[2]
    
    # Create a mapping of column letters to column names based on headers
    column_map = {cell.column_letter: cell.value for cell in ws[1]}
    
    # Detect which columns have formulas in the sample data row
    formula_columns = {}
    for cell in sample_row:
        if cell.value and isinstance(cell.value, str) and cell.value.startswith('='):
            formula_columns[column_map[cell.column_letter]] = cell.column
    
    # Read the dataframe columns
    df_columns = dataframe.columns
    
    # Write the data from dataframe to worksheet, skip columns with formulas
    for index, row in dataframe.iterrows():
        for col in df_columns:
            if col in formula_columns:
                continue  # Skip formula columns
            # Find the correct column letter to write data
            column_letter = [k for k, v in column_map.items() if v == col][0]
            ws[f'{column_letter}{index + 3}'].value = row[col]
    
    # Extend formulas from the sample row to all the rows
    for col, column_letter in formula_columns.items():
        sample_formula = ws[f'{column_letter}{2}'].value
        for i in range(3, len(dataframe) + 3):
            ws[f'{column_letter}{i}'].value = sample_formula.replace(f'{column_letter}2', f'{column_letter}{i}')
    
    # Save the new workbook
    wb.save(output_path)

# Example usage
df = pd.DataFrame({
    'Column1': [10, 20, 30],
    'Column2': [None, None, None],  # This will have formulas
    'Column3': [100, 200, 300]
})

extend_formulas_with_data('path_to_your_input_excel.xlsx', df, 'path_to_your_output_excel.xlsx')