Untitled

import openpyxl
import datetime

current_date = datetime.date.today()
current_date = current_date.strftime("%d_%m_%Y")
month = str(datetime.date.today().month - 1).zfill(2) #zfill добавляет нули
year = str(datetime.date.today().year)
prev_year = str(datetime.date.today().year - 1)
# print(current_date)
from openpyxl import Workbook

# print("Выберите год: (1 - 2022) / (2 - 2023)")
# selection = input()

svod_path = r"C:\Users\skoskin\Documents\check_list\arm_nc_25_07_2023_(2022)\data\Сводная таблица.xlsx"
check_book_save_path = r'C:\Users\skoskin\Documents\check_list\arm_nc_25_07_2023_(2022)\АРМ НЦ(2022)_Чек-лист_25_07_23.xlsx'

# СВОДНАЯ


check_book_path = r'C:\Users\skoskin\Documents\check_list\arm_nc_25_07_2023_(2022)\АРМ НЦ(2022)_Чек-лист_25_07_23_clean.xlsx'


names = ["ОУД", "план", "факт", "УД", "мероприятия план", 'мероприятия риск']  # ОУД - общий уровень достижения
rows = [4, 11, 20, 26]  # строки, которые нужно удалить


def cell_range_to_list(cell_range):
    ret = [i[0].value for i in cell_range if i[0].value != 'x']
    return ret


def big_cell_range_to_list(cell_range):
    ret = [i[0].value for i in cell_range if i[0].value is not None]
    return ret


def cool_cell_range_to_list(cell_range):
    ret = [i[0].value for i in cell_range]
    return ret


def get_data_svod(path):
    wb = openpyxl.load_workbook(path)
    sh1 = wb[wb.sheetnames[0]]
    data_svod = {}

    # уровень достижения общий
    column = sh1["C8:C39"]
    data_svod[names[0]] = big_cell_range_to_list(column)

    # план
    column = sh1["F8:F39"]
    data_svod[names[1]] = cell_range_to_list(column)

    # факт
    column = sh1["G8:G39"]
    data_svod[names[2]] = cell_range_to_list(column)

    # уровень достижения
    column = sh1["H8:H39"]
    data_svod[names[3]] = cell_range_to_list(column)

    # мероприятия
    column = sh1["I8:I39"]
    data_svod[names[4]] = cell_range_to_list(column)
    column = sh1["M8:M39"]
    data_svod[names[5]] = cell_range_to_list(column)

    return data_svod


data_svod = get_data_svod(svod_path)


print(data_svod)


def delete(data, name, rows):
    for i in range(len(rows)-1, -1, -1):
        del data[name][rows[i]]
        print('deleted row' + str(rows[i]))


for name in names[3:]:
    delete(data_svod, name, rows)


# write data to check_list
def write_column(column, data, dict_name):
    dt = column
    count = 0
    for i in dt:
        # if (type(data[dict_name][count]) is int):
        #     i[0].value = data[dict_name][count]
        if data[dict_name][count] is None:
            i[0].value = 0
        else:
            i[0].value = float(str(data[dict_name][count]).replace(',', '.'))
        count += 1
    # for i in dt:
    #     print(i[0].value)


def save_svod_data(path, save_path, data_svod):
    wb_check = openpyxl.load_workbook(path)
    check_sh = wb_check[wb_check.sheetnames[1]]

    # ОУД
    column = check_sh["B36:B40"]
    write_column(column, data_svod, names[0])

    # План
    column = check_sh["G3:G30"]
    write_column(column, data_svod, names[1])

    # Факт
    column = check_sh["L3:L30"]
    write_column(column, data_svod, names[2])

    # УД
    column = check_sh['B3:B30']
    write_column(column, data_svod, names[3])

    # Мероприятия план
    column = check_sh["Q3:Q30"]
    write_column(column, data_svod, names[4])

    # Мероприятия риск
    column = check_sh["U3:U30"]
    write_column(column, data_svod, names[5])

    wb_check.save(save_path)


save_svod_data(check_book_path, check_book_save_path, data_svod)