excel自动记录重复数

import openpyxl
import pandas as pd


def one_lie():
    # 打开 Excel 文件

    # workbook = openpyxl.load_workbook('Main.xlsx')
    workbook = openpyxl.load_workbook('a1.xlsx')

    # 获取第一个工作表
    worksheet = workbook.worksheets[0]

    # 获取第一列的所有单元格对象
    column_cells = worksheet['B:B']

    return [cell.value for cell in column_cells][1:]


def two_lie():
    two_list = []
    # 打开 Excel 文件

    # workbook = openpyxl.load_workbook('Main.xlsx')
    workbook = openpyxl.load_workbook('a1.xlsx')

    # 获取第一个工作表
    worksheet = workbook.worksheets[0]

    # 获取第二列的所有单元格对象
    column_cells = worksheet['C:C']

    # 提取单元格的值
    column_values = [cell.value for cell in column_cells][1:]


    for i in column_values:
        try:
            new_i = i.strip('[').strip(']').replace('\'', '')
            new_ii = new_i.split(',')
            two_list.append(new_ii)
        except:
            two_list.append([])
            # print(new_ii)
            # exit()
            # pass


    return two_list


def start_get_D():
    oneLs = one_lie()
    twoLs = two_lie()
    # print(len(oneLs), oneLs)
    # print(len(twoLs), twoLs)

    couls = ["次数"]
    for indexi, val0 in enumerate(oneLs):

        if val0 is None:
            couls.append("0")
            continue
        cou = 0

        inon = twoLs[indexi]  # 第一列对于的第二列的行的数组
        # print(type(inon), inon)
        for val1 in twoLs:
            inter = [i for i in inon if i in val1]  # 判断数组中的任意数是否在另外一个数组内
            if val0 in val1 and not inter:
                cou += 1

        print(f'{val0} 出现次数： {cou}次\n')
        couls.append(cou)
        # exit()
    print("打印完长度：", len(couls))
    return couls  # 重复出现次数列表


def write_excel_D(repeat):

    # 打开现有的工作簿
    workbook = openpyxl.load_workbook('a1.xlsx')

    # 选择特定的工作表
    worksheet = workbook['Sheet1']



    # 将列表写入Excel表中的D列
    for index, item in enumerate(repeat):
        cell = f'D{index + 1}'
        worksheet[cell] = item

    # 保存工作簿
    workbook.save('a1.xlsx')

def main():
    # 出现次数
    repeat = start_get_D()

    # 写入excel表 列D:D
    write_excel_D(repeat)
    print("恭喜~数据处理完毕~")


if __name__ == '__main__':
    main()