import pandas as pd
import os

fnamebegin_array = [
    "PB orders Jan",
    "PB orders Feb",
    "PB orders Mar",
    "PB orders Apr",
    "PB orders May",
    "PB orders Jun",
    "PB orders Jul",
    "PB orders Aug",
    "PB orders Sep",
    "PB orders Oct",
    "PB orders Nov",
    "PB orders Dec",
]

sheetname_array = [
    "JanData",
    "FebData",
    "MarData",
    "AprData",
    "MayData",
    "JunData",
    "JulData",
    "AugData",
    "SepData",
    "OctData",
    "NovData",
    "DecData",
]

# Define the paths
my_path = os.normpath(r"C:\Users\greencolor\Autoreport\Load_attachments")
my_path1 = os.normpath(r"C:\Users\greencolor\Desktop\Autoreport")

# Retrieve the list of filenames (without extension) in my_path
files_no_ext = [
    os.path.splitext(f)[0] for f in os.listdir(my_path) if os.path.isfile(f)
]


for filename in files_no_ext:  # Iterate over the list
    if filename in fnamebegin_array:  # If the filename is in the list
        # Create the 'month' DataFrame
        month = pd.read_excel(
            os.path.join(my_path, filename), sheet_name="Raw data ", engine="openpyxl"
        )

        with pd.ExcelWriter(
            os.path.join(my_path1, "Master.xlsx"),
            mode="a",
            if_sheet_exists="replace",
            engine="openpyxl",
        ) as writer:
            month.to_excel(
                writer,
                sheet_name=sheetname_array[
                    fnamebegin_array.index(filename)
                ],  # Since the lists share the same indeces, use the filename to retrieve the name of the sheet
                engine="openpyxl",
                index=False,
            )