# -*- coding: utf-8 -*- """汇总大表差异比对：生成件 vs 手工定稿 用法: python 汇总大表差异比对.py <生成件.xlsx> <手工定稿.xlsx> <输出前缀> 说明: 逐页按"行标签/列表头"做序列对齐（difflib），可正确处理多列/多行/重复表头； 每个不一致格归入 结构位移|取整尾差|文本型数字|草稿/辅助列|仅程序有|仅手工有|文本不同|错误值|实质数值差异。 """ import datetime, io, os, sys from difflib import SequenceMatcher from openpyxl import load_workbook from openpyxl.utils import get_column_letter def col_label(ws, c, hdr_rows=6): for r in range(1, hdr_rows+1): v = ws.cell(row=r, column=c).value if isinstance(v, (int, float)) and not isinstance(v, bool) and 20000 < v < 60000: d = datetime.date(1899,12,30) + datetime.timedelta(days=int(v)) return "%d-%02d" % (d.year, d.month) if isinstance(v, str) and v.strip() and not v.startswith("="): return v.strip()[:20] return "" def row_label(ws): out = []; cur = ""; seen = {} for r in range(1, ws.max_row+1): a = ws.cell(row=r, column=1).value; b = ws.cell(row=r, column=2).value if isinstance(a, str) and a.strip(): cur = a.strip() lab = ("%s|%s" % (cur, b.strip())) if (isinstance(b, str) and b.strip()) else (cur or "") seen[lab] = seen.get(lab, 0) + 1 out.append("%s#%d" % (lab, seen[lab])) return out def num(v): return isinstance(v, (int, float)) and not isinstance(v, bool) def text_num(v): if isinstance(v, str): s = v.strip().replace(",", "") try: return float(s) except Exception: return None return None def match_labels(a, b): sm = SequenceMatcher(None, a, b, autojunk=False) pairs = {} for op, i1, i2, j1, j2 in sm.get_opcodes(): if op == "equal": for k in range(i2-i1): pairs[i1+k] = j1+k return pairs def main(gen, dgf, prefix): g = load_workbook(gen, data_only=True); d = load_workbook(dgf, data_only=True) rows_out = [] per_sheet = [] for sh in g.sheetnames: if sh not in d.sheetnames: continue wg, wd = g[sh], d[sh] rl_g, rl_d = row_label(wg), row_label(wd) cl_g = [col_label(wg, c) for c in range(1, wg.max_column+1)] cl_d = [col_label(wd, c) for c in range(1, wd.max_column+1)] colmap = match_labels(cl_g, cl_d); rowmap = match_labels(rl_g, rl_d) cnt = {} def bump(k): cnt[k] = cnt.get(k, 0) + 1 # 列/行的结构性缺口 unmatched_cols_g = [c-1 for c in range(1, len(cl_g)+1) if c-1 not in colmap] unmatched_cols_d = [c-1 for c in range(1, len(cl_d)+1) if c-1 not in set(colmap.values())] unmatched_rows_g = [r-1 for r in range(1, len(rl_g)+1) if r-1 not in rowmap] unmatched_rows_d = [r-1 for r in range(1, len(rl_d)+1) if r-1 not in set(rowmap.values())] for c in unmatched_cols_g: if c >= 2: bump("仅程序有(列)"); rows_out.append([sh, "%s*" % get_column_letter(c+1), "（整列）", cl_g[c], "仅程序有(列)", "", "", "生成件多出此列"]) for c in unmatched_cols_d: if c >= 2: bump("仅手工有(列)"); rows_out.append([sh, "（无）", "（整列）", cl_d[c], "仅手工有(列)", "", "", "手工定稿多出此列"]) for r in unmatched_rows_g: if r >= 4: bump("仅程序有(行)"); rows_out.append([sh, "第%d行" % (r+1), rl_g[r], "（整行）", "仅程序有(行)", "", "", "生成件多出此区段"]) for r in unmatched_rows_d: if r >= 4: bump("仅手工有(行)"); rows_out.append([sh, "（无）", rl_d[r], "（整行）", "仅手工有(行)", "", "", "手工定稿多出此区段"]) for i, j in sorted(colmap.items()): for p, q in sorted(rowmap.items()): vg = wg.cell(row=p+1, column=i+1).value; vd = wd.cell(row=q+1, column=j+1).value if vg == vd: continue if vg is None and isinstance(vd, str) and vd.startswith("="): vd = None if vd is None and isinstance(vg, str) and vg.startswith("="): vg = None if (vg is None) and (vd is None): continue cat = None; note = "" tg, td = text_num(vg), text_num(vd) if num(vg) and num(vd): diff = abs(vg - vd) if diff <= 1e-6: continue rel = diff / max(abs(vd), 1e-9) if diff <= 0.01 or rel <= 1e-4: cat = "取整尾差" else: cat = "实质数值差异" note = "差=%.6g" % diff elif num(vg) and (vd is None): cat = "仅程序有"; elif num(vd) and (vg is None): cat = "仅手工有" elif tg is not None and num(vd) and abs(tg - vd) <= 1e-9: cat = "文本型数字"; note = "手工为文本'%s'" % vg elif td is not None and num(vg) and abs(td - vg) <= 1e-9: cat = "文本型数字"; note = "程序为文本'%s'" % vd elif isinstance(vg, str) and isinstance(vd, str): cat = "文本不同"; note = "程序'%s' / 手工'%s'" % (vg[:18], vd[:18]) elif isinstance(vg, str) and vg.startswith("#") : cat = "错误值(程序)"; note = vg elif isinstance(vd, str) and vd.startswith("#"): cat = "错误值(手工)"; note = vd else: cat = "其它"; note = "程序=%s 手工=%s" % (str(vg)[:14], str(vd)[:14]) if cat: bump(cat) rows_out.append([sh, "%s%d" % (get_column_letter(i+1), p+1), rl_g[p][:38], cl_g[i][:18], cat, ("" if vg is None else str(vg)[:20]), ("" if vd is None else str(vd)[:20]), note]) per_sheet.append((sh, cnt)) # 输出 CSV csv = prefix + ".csv" with io.open(csv, "w", encoding="utf-8-sig", newline="") as f: f.write("页签,生成件单元格,行标签,列表头,类别,生成件值,手工值,备注\n") for x in rows_out: f.write(",".join('"%s"' % str(v).replace('"', "'") for v in x) + "\n") # 输出 MD cats = ["实质数值差异", "仅手工有", "仅程序有", "取整尾差", "文本型数字", "文本不同", "错误值(程序)", "错误值(手工)", "仅程序有(列)", "仅手工有(列)", "仅程序有(行)", "仅手工有(行)", "其它"] md = prefix + ".md" with io.open(md, "w", encoding="utf-8", newline="\n") as f: f.write("# 汇总大表差异明细（生成件 vs 手工定稿）\n\n") f.write("- 生成件：`%s`\n- 手工定稿：`%s`\n- 比对时间：%s\n- 明细数据：`%s`\n\n" % (gen, dgf, datetime.datetime.now().strftime("%Y-%m-%d %H:%M"), os.path.basename(csv))) f.write("## 逐页汇总\n\n| 页签 | " + " | ".join(cats[:7]) + " |\n|---|" + "---|"*7 + "\n") for sh, cnt in per_sheet: f.write("| %s | %s |\n" % (sh, " | ".join(str(cnt.get(c, 0)) for c in cats[:7]))) f.write("\n## 实质数值差异（全部）\n\n") subs = [x for x in rows_out if x[4] == "实质数值差异"] if not subs: f.write("（无）\n") for x in subs[:200]: f.write("- %s %s [%s] 生成件=%s 手工=%s（%s）\n" % (x[0], x[1], x[2], x[5], x[6], x[7])) f.write("\n## 仅手工有（前 200 条）\n\n") for x in [y for y in rows_out if y[4] in ("仅手工有", "仅手工有(列)")][:200]: f.write("- %s %s [%s] %s 手工=%s\n" % (x[0], x[1], x[2], x[3], x[6])) f.write("\n## 仅程序有（前 200 条）\n\n") for x in [y for y in rows_out if y[4] in ("仅程序有", "仅程序有(列)", "仅程序有(行)")][:200]: f.write("- %s %s [%s] %s 生成件=%s\n" % (x[0], x[1], x[2], x[3], x[5])) print("已输出:", md, "|", csv, "| 明细条数:", len(rows_out)) for sh, cnt in per_sheet: if cnt: print(" %-12s %s" % (sh, dict(cnt))) if __name__ == "__main__": main(sys.argv[1], sys.argv[2], sys.argv[3])