cece.py

import os

import pandas as pd


def process_files(directory):
    # Loop through all Excel files in the given directory
    for filename in os.listdir(directory):
        if filename.endswith(".xlsx"):
            file_path = os.path.join(directory, filename)
            process_excel(file_path)


def process_excel(file_path, new_file_path):
    # Load the Excel file
    df = pd.read_excel(file_path, sheet_name="Sheet1")  # Update 'Sheet1' if using a different sheet name

    breakpoint()
    test3_value = df.loc[0, "val"] + df.loc[1, "val"]  # Rows are 0-indexed in pandas
    df.at[2, "val"] = test3_value

    # Calculate test6 as OA4 + OA5 where OA4 and OA5 are in B5 and B6
    test6_value = df.loc[3, "val"] + df.loc[4, "val"]
    # Write back to test6 cell which is B7
    df.at[5, "val"] = test6_value

    # with pd.ExcelWriter(file_path, engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
    #     df.to_excel(writer, sheet_name="Sheet1", index=False)
    df.to_excel(new_file_path, index=False)


# # Replace 'your_directory_path_here' with the path to the directory containing your Excel files
# process_files("your_directory_path_here")
process_excel("cece/test.xlsx", "cece/test_modified.xlsx")