[封面图]

封面图

python程序打包成exe可执行文件

1
2
pip install pyinstaller
pyinstaller --onefile your_script.py

账号和流水明细数据拆分

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
# -*- coding: utf-8 -*-
import openpyxl
from openpyxl import Workbook
import time

def main():
    try:
        print("=================银行流水自动拆分程序V1.0【吴第广】=================")
        print("=================开始=================")
        # 记录程序开始时间
        start_time = time.time()
        # 格式化时间
        formatted_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(start_time))
        print(f"程序开始执行时间：{formatted_time}")
        # 打开 Excel 文件
        try:
            workbook_user = openpyxl.load_workbook('账号.xlsx')
            workbook_detail = openpyxl.load_workbook('明细.xlsx')
        except FileNotFoundError:
            raise FileNotFoundError("账号.xlsx或明细.xlsx不存在")
            
        # 选择工作表
        worksheet_user = workbook_user.active
        worksheet_detail = workbook_detail.active

        user_data_dict = {}  # 创建一个字典用于存储数据
        exist_user_count = set()

        # 从第二行开始逐行读取数据
        for row in worksheet_user.iter_rows(min_row=2, values_only=True):  # min_row=2 表示从第二行开始读取
            name_column_user = row[0]  # 第一列数据
            card_column_user = row[2]  # 第三列数据
            bank_column_user = row[15]  # 第十六列数据
            user_card = card_column_user.replace("\t", "").replace(" ", "")
            exist_user_count.add(user_card)
            if bank_column_user is None:
                print("账号：" + name_column_user + "的开户行信息为空，请保证数据完整性")
            user_data_dict[user_card] = name_column_user + "-" + bank_column_user

        print("账户表中存在不同的卡号数量为：", len(exist_user_count))
        # print("账户表中存在不同的卡号为：", exist_user_count)

        my_detail_dict = {}

        exist_detail_card = set()

        header = [cell.value for cell in worksheet_detail[1]]
        now_row = 0
        for row in worksheet_detail.iter_rows(min_row=2, values_only=True):  # min_row=2 表示从第二行开始读取
            first_column_detail = row[0]
            first_column_detail = first_column_detail.replace("\t", "").replace(" ", "")
            now_row = now_row + 1
            if row not in exist_detail_card:
                exist_detail_card.add(first_column_detail)
            if now_row%10000 == 0:
                print("当前读取行数：", now_row)
                # 判断键是否存在
            if first_column_detail in my_detail_dict:
                # 键存在，向值（对象数组）中添加一条记录
                my_detail_dict[first_column_detail].append(row)
            else:
                # 键不存在，创建一个新的字典条目
                my_detail_dict[first_column_detail] = []
        # print(header)
        # print("用户卡号字典", user_data_dict)
        # print(my_detail_dict)
        print("【明细表中存在不同的卡号数量为】：", len(exist_detail_card))
        # print("明细表中存在不同的卡号为：", exist_detail_card)

        intersection_set = exist_user_count & exist_detail_card
        print("【有效卡号数为【：", len(intersection_set))
        print("【有效卡号为】：", intersection_set)

        first_exist_second_not_exist = exist_user_count - exist_detail_card
        print("【账号表中存在，明细表中不存在的卡号为】：", first_exist_second_not_exist)

        second_exist_first_not_exist = exist_detail_card - exist_user_count
        print("【明细表中存在，账号表中不存在的卡号为】：", second_exist_first_not_exist)

        for key, value in my_detail_dict.items():
            if key not in user_data_dict:
                print("账号表中不存在卡号：", key)
                continue
            name = user_data_dict[key]
            # 创建一个新的工作簿
            workbook = Workbook()

            # 创建一个工作表
            worksheet = workbook.active
            worksheet.append(header)
            for row in value:
                worksheet.append(row)
            new_name = name.replace("\t", "").replace(" ", "")
            card_no = key.replace("\t", "").replace(" ", "")
            workbook.save(new_name + "-" +  card_no[-4:] + ".xlsx")
            print("文档生成成功：", new_name + "-" +  card_no[-4:] + ".xlsx")

        my_detail_dict

        # 关闭 Excel 文件
        workbook_user.close()
        workbook_detail.close()
        # 记录程序结束时间
        end_time = time.time()
        # 计算执行时间
        execution_time = end_time - start_time
        print(f"程序执行时间：{execution_time}秒")
        print("=================完成=================")
        input("Press Enter to exit") 
    except Exception as e:
        # 异常处理逻辑
        # traceback.print_exc()
        print(f"An error occurred: {e}")
        input("Press Enter to exit") 

if __name__ == "__main__":
    main()

CSV表格合并

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
import os
import csv
import time

def main():
    try:
        print("=================多账号CSV文件合并程序V1.0【吴第广】=================")
        print("=================开始=================")
        start_time = time.time()
        # 获取当前目录下的所有CSV文件
        csv_files = [file for file in os.listdir() if file.endswith('.csv')]

        # 合并后的文件名称和表头
        output_file = '合并后的表格文件.csv'
        header_written = False
        common_header = None

        # 打开合并后的文件以写入数据
        with open(output_file, 'w', newline='') as merged_csv:
            writer = csv.writer(merged_csv)

            # 循环处理每个CSV文件
            for i, file in enumerate(csv_files):
                print(f"正在处理文件：{file}")
                with open(file, 'r') as csv_file:
                    reader = csv.reader(csv_file)
                    current_header = next(reader)

                    if i == 0:
                        common_header = current_header
                        writer.writerow(common_header)  # 写入第一个文件的表头
                        header_written = True
                    elif current_header != common_header:
                        print(f"错误：文件 {file} 的表头与其他文件不一致！")
                        raise SystemExit(1)

                    # 读取CSV文件的每一行数据
                    for row in reader:
                        writer.writerow(row)  # 写入非表头数据

        end_time = time.time()
        execution_time = end_time - start_time
        print(f"合并完成，结果保存在 {output_file} 文件中。")
        print(f"程序执行时间为：{execution_time:.2f} 秒")
        print("=================完成=================")
        input("Press Enter to exit") 
    except Exception as e:
        # 异常处理逻辑
        print(f"An error occurred: {e}")
        input("Press Enter to exit") 

if __name__ == "__main__":
    main()