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
| 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}") 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): 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))
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): 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("【明细表中存在不同的卡号数量为】:", len(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
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: print(f"An error occurred: {e}") input("Press Enter to exit")
if __name__ == "__main__": main()
|