from openpyxl.workbook import Workbook
from openpyxl.utils import get_column_letter
from openpyxl import load_workbook


def main():
    wbr = load_workbook(filename='ОПК ГИСП - общая_2.xlsx')
    sheet = wbr['Лист1']

    counter = 1
    file_div = 500
    total_files = (sheet.max_row // file_div) + 1
    file_counter = 0
    offset = 0

    while file_counter < total_files:
        wb = Workbook()
        ws1 = wb.active
        ws1.title = "data"

        if file_counter != 0:
            ws1.append((cell.value for cell in sheet[1][0:20]))

        for row in sheet.iter_rows(file_counter * file_div + offset + 1, (file_counter + 1) * file_div):
            ws1.append((cell.value for cell in row[0:20]))

        offset = 0
        i = (file_counter + 1) * file_div
        while sheet[i][0].value == sheet[i + 1][0].value:
            print(offset)
            print(sheet[i][0].value, sheet[i + 1][0].value)
            row = sheet[i + 1]
            ws1.append((cell.value for cell in row[0:20]))
            offset += 1
            i += 1

        dest_filename = filename.split('.')[-2] + '_' + str(counter) + '.xlsx'
        counter += 1
        file_counter += 1
        wb.save(filename=dest_filename)


if __name__ == '__main__':
    main()