#Подключаем модули
import os
import shutil
#import datetime as dt
from datetime import datetime, timedelta
from openpyxl import load_workbook


def get_date_from_file_name(file, _days):
    g = int(file[:4])
    m = int(file[5:7])
    d = int(file[8:10])
    dtime = datetime(g, m, d, 8, 0) + timedelta(days=_days)
    return dtime.year, dtime.month, dtime.day

#Каталог из которого будем брать файлы
dirs = ['week1', 'week2']

for directory in dirs:
    #Получаем список файлов в переменную files
    files = os.listdir(directory)
    #print(files)
    for deltas in [14, 28, 42]:
        for file in files:
            daydate = get_date_from_file_name(file, deltas)
            #newfile = f'{daydate[0]}-{daydate[1]}-{daydate[2]}-sm.xlsx' # год-месяц-день
            newfile = str(daydate[0]).zfill(4) + '-' + str(daydate[1]).zfill(2) + '-' + str(daydate[2]).zfill(2) + '-sm.xlsx' # год-месяц-день-sm.xlsx
            newfilepath = os.path.join(directory, newfile)
            shutil.copy(os.path.join(directory, file), newfilepath)

            if os.path.exists(newfilepath):
                #load excel file
                workbook = load_workbook(filename=newfilepath) # '2022-12-09-sm.xlsx'

                #open workbook
                sheet = workbook.active

                #modify the desired cell день.месяц.год
                sheet["J1"] = str(daydate[2]).zfill(2) + '.' + str(daydate[1]).zfill(2) + '.' + str(daydate[0]).zfill(4)

                #save the file
                workbook.save(filename=newfilepath)
print('Done')