# 导入必要库import tkinter as tkfrom tkinter import ttk, filedialog, messageboximport pandas as pdimport osfrom datetime import datetimefrom difflib import SequenceMatcher# 创建主窗口root = tk.Tk()root.title("往来对账工具")root.geometry("500x150")# 全局变量存储文件夹路径folder_path = tk.StringVar()def select_folder(): """选择往来明细账文件夹路径""" path = filedialog.askdirectory() if path: folder_path.set(path)def standardize_columns(df): """标准化列名""" columns = df.columns for col in columns: if '借方' in col: df = df.rename(columns={col: '借方金额'}) elif '贷方' in col: df = df.rename(columns={col: '贷方金额'}) elif '摘要' in col: df = df.rename(columns={col: '摘要'}) return dfdef start_reconciliation(): """开始对账主函数""" try: # 获取文件夹路径并检查 path = folder_path.get() if not path: raise ValueError("请先选择文件夹路径") # 获取文件夹内的Excel文件 files = [f for f in os.listdir(path) if f.endswith(('.xls', '.xlsx'))] if len(files) != 2: raise ValueError("文件夹内必须包含两个Excel文件") # 读取两个Excel文件并标准化列名 df1 = pd.read_excel(os.path.join(path, files[0])) df2 = pd.read_excel(os.path.join(path, files[1])) df1 = standardize_columns(df1) df2 = standardize_columns(df2) # 第一步核对：单条匹配 def single_match(df_a, df_b): """单条借贷方匹配逻辑""" matched_a = [] matched_b = [] for idx_a, row_a in df_a.iterrows(): for idx_b, row_b in df_b.iterrows(): # 检查金额相等且借贷方向相反 if (row_a['借方金额'] == row_b['贷方金额'] and row_a['借方金额'] != 0) or \ (row_a['贷方金额'] == row_b['借方金额'] and row_a['贷方金额'] != 0): matched_a.append(idx_a) matched_b.append(idx_b) return df_a.drop(matched_a), df_b.drop(matched_b) # 执行双向匹配 remaining1, remaining2 = single_match(df1, df2) remaining2, remaining1 = single_match(remaining2, remaining1) # 第二步核对：摘要相似性分组匹配 def group_match(df_a, df_b): """摘要分组匹配逻辑""" # 生成摘要分组关键词（示例使用简单相似度匹配） groups = {} for idx, row in df_a.iterrows(): found = False for key in groups: # 判断摘要相似度（阈值可调整） if SequenceMatcher(None, key, row['摘要']).ratio() > 0.6: groups[key].append(idx) found = True break if not found: groups[row['摘要']] = [idx] # 检查分组金额是否匹配 matched_a = [] matched_b = [] for key, indices in groups.items(): sum_debit = df_a.loc[indices, '借方金额'].sum() sum_credit = df_a.loc[indices, '贷方金额'].sum() # 在df_b中寻找匹配项 for idx_b, row_b in df_b.iterrows(): if (sum_debit == row_b['贷方金额'] and sum_debit != 0) or \ (sum_credit == row_b['借方金额'] and sum_credit != 0): matched_a.extend(indices) matched_b.append(idx_b) break return df_a.drop(matched_a), df_b.drop(matched_b) # 执行双向分组匹配 final1, final2 = group_match(remaining1, remaining2) final2, final1 = group_match(final2, final1) # 生成结果文件 timestamp = datetime.now().strftime("%Y%m%d%H%M%S") output_path = os.path.join(path, f'未匹配条目_{timestamp}.xlsx') with pd.ExcelWriter(output_path) as writer: final1.to_excel(writer, sheet_name=files[0][:30], index=False) final2.to_excel(writer, sheet_name=files[1][:30], index=False) messagebox.showinfo("完成", f"对账完成！结果已保存至：\n{output_path}") except Exception as e: messagebox.showerror("错误", str(e))# 创建界面组件ttk.Label(root, text="往来明细账文件夹:").grid(row=0, column=0, padx=5, pady=5)ttk.Entry(root, textvariable=folder_path, width=40).grid(row=0, column=1, padx=5)ttk.Button(root, text="选择文件夹", command=select_folder).grid(row=0, column=2, padx=5)ttk.Button(root, text="开始对账", command=start_reconciliation).grid(row=1, column=1, pady=10)# 运行主循环root.mainloop()
