import pandas as pdfrom openpyxl import load_workbookfrom openpyxl.styles import Font, Alignment, PatternFill, Border, Side# ============ 文件路径（按需修改）=============file_path = r".\债权债务抵消_其他.xlsx"output_path = r".\债权债务抵消_其他_抵消结果.xlsx"# ============ 读取数据 ============# 从Excel中读取三个sheet：合并范围、应收明细、应付明细df_scope = pd.read_excel(file_path, sheet_name="合并范围")df_ar = pd.read_excel(file_path, sheet_name="应收")df_ap = pd.read_excel(file_path, sheet_name="应付")# 把合并范围整理成字典，方便后面查找：{公司名称: 是否并表}scope_dict = dict(zip(df_scope["公司名称"], df_scope["是否并表"]))# ============ 核心处理函数 ============def process_sheet(df_self, df_other, sheet_type="应收"):    """    处理应收或应付明细表    df_self: 当前科目明细（比如应收）    df_other: 对方科目明细（比如应付）    sheet_type: "应收" 或 "应付"    """    df = df_self.copy()    # ---- 第一步：补充并表信息 ----    # 查本公司是否在合并范围内，查不到就填"否"    df["是否并表"] = df["公司名称"].map(scope_dict).fillna("否")    # 查对方单位是否在合并范围内，查不到就填"否"    df["客商是否并表"] = df["对方单位"].map(scope_dict).fillna("否")    # ---- 第二步：第一类抵消——本科目内部抵消 ----    # 规则：本公司和对方单位都在合并范围内 → 全额抵消    # 比如A、B都是合并内公司，A借B 100万，这100万在合并层面要抵消掉    df["本科目内部抵消"] = 0.0    for idx, row in df.iterrows():        if row["是否并表"] == "是" and row["客商是否并表"] == "是":            df.at[idx, "本科目内部抵消"] = row["金额"] * -1  # 负数表示抵消    # 内部抵消后还剩多少    df["内部抵消后金额"] = df["金额"] + df["本科目内部抵消"]    # ---- 第三步：对方科目也做一遍内部抵消 ----    # 这样才能知道对方科目"内部抵消后"的余额，用于第二类抵消计算    df_other_proc = df_other.copy()    df_other_proc["是否并表"] = df_other_proc["公司名称"].map(scope_dict).fillna("否")    df_other_proc["客商是否并表"] = df_other_proc["对方单位"].map(scope_dict).fillna("否")    df_other_proc["本科目内部抵消"] = 0.0    for idx, row in df_other_proc.iterrows():        if row["是否并表"] == "是" and row["客商是否并表"] == "是":            df_other_proc.at[idx, "本科目内部抵消"] = row["金额"] * -1    df_other_proc["内部抵消后金额"] = df_other_proc["金额"] + df_other_proc["本科目内部抵消"]    # ---- 第四步：按对方单位汇总"内部抵消后金额" ----    # 把当前科目的余额，按对方单位汇总    self_bal = df.groupby("对方单位")["内部抵消后金额"].sum().reset_index()    # 把对方科目的余额，按对方单位汇总    other_bal = df_other_proc.groupby("对方单位")["内部抵消后金额"].sum().reset_index()    # 给两列起个好懂的名字    if sheet_type == "应收":        self_bal.columns = ["对方单位", "应收余额"]        other_bal.columns = ["对方单位", "应付余额"]    else:        self_bal.columns = ["对方单位", "应付余额"]        other_bal.columns = ["对方单位", "应收余额"]    # ---- 第五步：第二类抵消——相同客商，取小抵消 ----    # 把应收和应付的余额拼到一张表上，同一个客商的应收应付，取较小的那个抵消    merged = self_bal.merge(other_bal, on="对方单位", how="outer").fillna(0)    merged["可抵消金额"] = merged[["应收余额", "应付余额"]].min(axis=1)    # 算一下抵消比例：可抵消金额 ÷ 当前科目余额    # 这个比例用来把可抵消金额"分摊"回每一笔明细上    merged["抵消率"] = 0.0    if sheet_type == "应收":        for idx, row in merged.iterrows():            if row["应收余额"] != 0:                merged.at[idx, "抵消率"] = row["可抵消金额"] / row["应收余额"]    else:        for idx, row in merged.iterrows():            if row["应付余额"] != 0:                merged.at[idx, "抵消率"] = row["可抵消金额"] / row["应付余额"]    # 整理成字典，方便后面按对方单位查找抵消率    rate_dict = dict(zip(merged["对方单位"], merged["抵消率"]))    # ---- 第六步：把相同客商抵消金额回填到明细 ----    # 只对"客商不在合并范围内"且有余额的明细做第二类抵消    df["相同客商抵消"] = 0.0    for idx, row in df.iterrows():        if row["客商是否并表"] != "是" and row["内部抵消后金额"] != 0:            ratio = rate_dict.get(row["对方单位"], 0)            df.at[idx, "相同客商抵消"] = -row["内部抵消后金额"] * ratio    # ---- 第七步：最终结果 ----    df["抵消总数"] = df["本科目内部抵消"] + df["相同客商抵消"]    df["抵消后余额"] = df["内部抵消后金额"] + df["相同客商抵消"]    return df, merged# ============ 开始处理 ============# 处理应收明细df_ar_result, ar_rate = process_sheet(df_ar, df_ap, "应收")# 处理应付明细df_ap_result, ap_rate = process_sheet(df_ap, df_ar, "应付")# ============ 相同客商抵消汇总表 ============# 把应收和应付的客商余额放一起，算出每个客商能抵消多少customer = ar_rate[["对方单位", "应收余额"]].merge(    ap_rate[["对方单位", "应付余额"]], on="对方单位", how="outer").fillna(0)customer["可抵消金额"] = customer[["应收余额", "应付余额"]].min(axis=1)# ============ 准备输出列 ============output_cols = [    "序号", "科目名称", "公司名称", "是否并表", "对方单位", "客商是否并表",    "金额", "本科目内部抵消", "相同客商抵消", "抵消总数", "抵消后余额"]# 如果原始数据没有序号列，自动补一个（序号为数字）if "序号" not in df_ar_result.columns:    df_ar_result.insert(0, "序号", range(1, len(df_ar_result) + 1))if "序号" not in df_ap_result.columns:    df_ap_result.insert(0, "序号", range(1, len(df_ap_result) + 1))# 确保序号列为数字类型df_ar_result["序号"] = pd.to_numeric(df_ar_result["序号"], errors='coerce')df_ap_result["序号"] = pd.to_numeric(df_ap_result["序号"], errors='coerce')df_ar_output = df_ar_result[output_cols]df_ap_output = df_ap_result[output_cols]# ============ 汇总表（删除了合计行）============# 应收的汇总数ar_orig = df_ar_result["金额"].sum()ar_internal = abs(df_ar_result["本科目内部抵消"].sum())ar_cust = abs(df_ar_result["相同客商抵消"].sum())ar_total = abs(df_ar_result["抵消总数"].sum())ar_balance = df_ar_result["抵消后余额"].sum()# 应付的汇总数ap_orig = df_ap_result["金额"].sum()ap_internal = abs(df_ap_result["本科目内部抵消"].sum())ap_cust = abs(df_ap_result["相同客商抵消"].sum())ap_total = abs(df_ap_result["抵消总数"].sum())ap_balance = df_ap_result["抵消后余额"].sum()# 拼成汇总表（只有应收和应付两行）df_summary = pd.DataFrame({    "项目": ["应收", "应付"],    "明细总额（原）": [ar_orig, ap_orig],    "抵消金额": [ar_total, ap_total],    "其中：同科目内部抵消": [ar_internal, ap_internal],    "其中：相同客商抵消": [ar_cust, ap_cust],    "抵消后余额": [ar_balance, ap_balance]})# ============ 保存到Excel ============with pd.ExcelWriter(output_path, engine='openpyxl') as writer:    df_scope.to_excel(writer, sheet_name="合并范围", index=False)    df_ar_output.to_excel(writer, sheet_name="应收明细", index=False)    df_ap_output.to_excel(writer, sheet_name="应付明细", index=False)    customer.to_excel(writer, sheet_name="相同客商抵消", index=False)    df_summary.to_excel(writer, sheet_name="汇总表", index=False)# ============ 美化格式 ============wb = load_workbook(output_path)# 通用样式设置hdr_font = Font(bold=True, color="FFFFFF", size=11)hdr_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")align_c = Alignment(horizontal="center", vertical="center")align_r = Alignment(horizontal="right", vertical="center")border = Border(    left=Side('thin'), right=Side('thin'),    top=Side('thin'), bottom=Side('thin'))# ---- 汇总表单独美化 ----ws = wb["汇总表"]ws.column_dimensions['A'].width = 8for col in ['B', 'C', 'D', 'E', 'F']:    ws.column_dimensions[col].width = 22# 表头样式for cell in ws[1]:    cell.font, cell.fill, cell.alignment, cell.border = hdr_font, hdr_fill, align_c, border# 数据行样式（第2行是应收，第3行是应付）for row in ws.iter_rows(min_row=2, max_row=3):    for cell in row:        cell.border = border        cell.alignment = align_r if isinstance(cell.value, (int, float)) else align_c        if isinstance(cell.value, (int, float)):            cell.number_format = '#,##0.00'# ---- 其他Sheet美化 ----for name in ["合并范围", "应收明细", "应付明细", "相同客商抵消"]:    ws = wb[name]    # 表头样式    for cell in ws[1]:        cell.font, cell.fill, cell.alignment, cell.border = hdr_font, hdr_fill, align_c, border    # 自动调整列宽    for col in ws.columns:        max_len = max(len(str(cell.value or "")) for cell in col)        ws.column_dimensions[col[0].column_letter].width = min(max_len + 3, 30)    # 数据行样式    for row in ws.iter_rows(min_row=2):        for cell in row:            cell.border = border            if isinstance(cell.value, (int, float)):                cell.number_format = '#,##0.00'                cell.alignment = align_r# 保存wb.save(output_path)