from openpyxl import load_workbook
import pandas as pd, datetime
from os import listdir
from os.path import isfile, join

def report(path_n, file_n, sheet_n):
    df = pd.read_excel(f'{path_n}/{file_n}', sheet_name=sheet_n)
    df_add_help = list(df.keys())
    nums = 0
    for i in range(3):
        if 'Unnamed' not in df_add_help[i]:
            nums += 1
    for i in range(len(df_add_help)):
        if i > 0:
            if type(df_add_help[i]) is str:
                if 'Unnamed' in df_add_help[i]:
                    df_add_help[i] = df_add_help[i-1]
        else:
            if type(df_add_help[i]) is str:
                if 'Unnamed' in df_add_help[i]:
                    df_add_help[i] = 'forDrop'
    if nums > 1:
        df_add = pd.DataFrame([df_add_help], columns=df.keys())
        df = pd.concat([df_add, df])
    df.reset_index(drop=True, inplace=True)
    if 'Unnamed: 0' in df.keys():
        df = df.drop(['Unnamed: 0'], axis=1)
    # Определение начала таблицы
    for i, row in df.iterrows():
        k = 0
        for j in row:
            if type(j) is str:
                k+=1
        if k > 2:
            k = i
            break
    # Определение начала данных таблицы
    for i, row in df.iterrows():
        l = 0
        for j in row:
            if (type(j) is (int or float)) and (str(j) != 'nan'):
                l+=1
        if l > 0:
            l = i
            break

    # Есть ли номера столбцов после названий
    colnumbs = 0
    for i, row in df.iterrows():
        if l == i:
            if type(row[1]) is int and type(row[2]) is int:
                if row[1] == row[2] - 1:
                    colnumbs = 1

    # Составление описания
    main, hs1, hs2, hs3, hs4 = [], '', '', '', ''
    for col_name, data in df.items():
        # print("col_name:", col_name, "\ndata:", data[k])
        # Название строки
        column_name = ''
        for per in range(l-k):
            if str(data[k+per]) == 'nan':
                if per == 0:
                    column_name = hs1

                if per == 1 and str(data[k]) == 'nan':
                    column_name += ' //'+str(hs2)
                elif per == 1:
                    if str(data[k+1]) != 'nan':
                        column_name += ' //'+str(data[k+1])

                if per == 2 and str(data[k+1]) == 'nan' and str(data[k]) == 'nan':
                    column_name += ' //'+str(hs3)
                elif per == 2:
                    if str(data[k+2]) != 'nan':
                        column_name += ' //'+str(data[k+2])

                if per == 3 and str(data[k+2]) == 'nan' and str(data[k+1]) == 'nan' and str(data[k]) == 'nan':
                    column_name += ' //'+str(hs4)
                elif per == 3:
                    if str(data[k+3]) != 'nan':
                        column_name += ' //'+str(data[k+3])
            else:
                if per == 0:
                    column_name += str(data[k+per])
                else:
                    column_name += ' //'+str(data[k+per])
                if per == 0 and str(data[k]) != 'nan':
                    hs1 = data[k]
                    if l-k > 1:
                        hs2 = data[k+1]
                    if l-k > 2:
                        hs3 = data[k+2]
                    if l-k > 3:
                        hs4 = data[k+3]
                elif per == 1 and str(data[k+1]) != 'nan':
                    hs2 = data[k+1]
                    if l-k > 2:
                        hs3 = data[k+2]
                    if l-k > 3:
                        hs4 = data[k+3]
                elif per == 2 and str(data[k+2]) != 'nan':
                    hs3 = data[k+2]
                    if l-k > 3:
                        hs4 = data[k+3]
                elif per == 3 and str(data[k+3]) == 'nan':
                    hs4 = data[k+3]
        typeis = 'string'
        decimal_types = 0
        for i in data:
            if (type(i) is int or type(i) is float) and str(i) != 'nan':
                decimal_types += 1
            if colnumbs == 1:
                if decimal_types > 1:
                    typeis = 'decimal'
            else:
                if decimal_types > 0:
                    typeis = 'decimal'
            if type(i) is datetime.datetime:
                typeis = 'datetime'
                break
        priznak = 'Признак'
        if typeis == 'decimal':
            priznak = 'Показатель'
        main.append([path_n, file_n, sheet_n, column_name, typeis, priznak])
    return main
#----------------------------------------------------------------------------------------------------------------
cols = ['Организация', 'Наименование отчёта', 'Наименование листа', 'Наименование атрибута', 'Тип данных', 'Признак/Показатель']
df = pd.DataFrame(columns=cols)
folders = listdir('.')
folders = [i for i in folders if '.' not in i]
print(folders)
for folder in folders:
    files = [f for f in listdir(f'{folder}') if isfile(join(f'{folder}', f))]
    files = [i for i in files if 'ДАННЫЕ' not in i]
    files = [i for i in files if 'Таблица ИТ' not in i]
    print(files)
    for file in files:
        if '.xlsx' in file:
            print(file)
            wb = load_workbook(filename=f'{folder}/{file}')
            print('fff')
            for i in wb.sheetnames:
                if 'порядок' not in i:
                    print(i)
                    df = pd.concat([df, pd.DataFrame(report(folder, file, i), columns=cols)], axis=0)
                    print(df)
df.to_excel('report.xlsx', index=False)



объясни код