From 723cad3d1f8ee1b1b9753b0cc1f9d7da0a8cc5f0 Mon Sep 17 00:00:00 2001
From: zhizhijie <zhizhijie@users.noreply.gitee.com>
Date: 星期一, 28 九月 2026 17:14:37 +0800
Subject: [PATCH] docs(交接): HANDOFF 补《无表头草稿列》9 处清单（母版已清空、手工仍保留），与已确认差异清单同步
---
docs/工具/汇总大表差异比对.py | 240 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 240 insertions(+), 0 deletions(-)
diff --git "a/docs/\345\267\245\345\205\267/\346\261\207\346\200\273\345\244\247\350\241\250\345\267\256\345\274\202\346\257\224\345\257\271.py" "b/docs/\345\267\245\345\205\267/\346\261\207\346\200\273\345\244\247\350\241\250\345\267\256\345\274\202\346\257\224\345\257\271.py"
new file mode 100644
index 0000000..56228de
--- /dev/null
+++ "b/docs/\345\267\245\345\205\267/\346\261\207\346\200\273\345\244\247\350\241\250\345\267\256\345\274\202\346\257\224\345\257\271.py"
@@ -0,0 +1,240 @@
+# -*- coding: utf-8 -*-
+"""姹囨�诲ぇ琛ㄥ樊寮傛瘮瀵癸細鐢熸垚浠� vs 鎵嬪伐瀹氱
+鐢ㄦ硶: python 姹囨�诲ぇ琛ㄥ樊寮傛瘮瀵�.py <鐢熸垚浠�.xlsx> <鎵嬪伐瀹氱.xlsx> <杈撳嚭鍓嶇紑>
+璇存槑: 閫愰〉鎸�"琛屾爣绛�/鍒楄〃澶�"鍋氬簭鍒楀榻愶紙difflib锛夛紝鍙纭鐞嗗鍒�/澶氳/閲嶅琛ㄥご锛�
+ 姣忎釜涓嶄竴鑷存牸褰掑叆 缁撴瀯浣嶇Щ|鍙栨暣灏惧樊|鏂囨湰鍨嬫暟瀛梶鑽夌/杈呭姪鍒梶浠呯▼搴忔湁|浠呮墜宸ユ湁|鏂囨湰涓嶅悓|閿欒鍊紎瀹炶川鏁板�煎樊寮傘��
+"""
+import datetime, io, os, re, 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()
+ if isinstance(b, str) and b.strip():
+ lab = "%s|%s|%s" % (block_of(ws, r), norm_region(cur), norm_indicator(b))
+ else:
+ lab = "%s|%s|" % (block_of(ws, r), norm_region(cur))
+ seen[lab] = seen.get(lab, 0) + 1
+ out.append("%s#%d" % (lab, seen[lab]))
+ return out
+
+def norm(s):
+ """鏍囩褰掍竴鍖栵細鍘荤┖鐧姐�佸幓鍏ㄨ绌烘牸銆佺粺涓�寮曞彿銆佸幓鎺夆�樺競/鐪�/宸炩�欏悗缂�宸紓"""
+ if not isinstance(s, str): return ""
+ t = s.replace("\u3000", " ").replace("锛�", "(").replace("锛�", ")").strip()
+ t = re.sub(r"[\s]+", "", t)
+ return t
+
+def norm_region(s):
+ t = norm(s)
+ for suf in ("甯�", "鐪�", "鑷不宸�", "宸�"):
+ if t.endswith(suf) and len(t) > len(suf): t = t[:-len(suf)]
+ return t
+
+def norm_indicator(s):
+ t = norm(s).replace("(涓囧惃鍏噷)", "").replace("(涓囧惃)", "").replace("(涓囦汉鍏噷)", "").replace("(涓囦汉)", "")
+ t = t.replace("鍏朵腑瑙勪笂", "瑙勪笂").replace("鍏朵腑瑙勪笅", "瑙勪笅")
+ return t
+
+def block_of(ws, r):
+ """鍚戜笂鎵炬渶杩戜竴涓�"鏍囬琛�"锛圔 鍒椾负绌轰笖 A/鏂囧瓧杈冮暱锛夊垽鏂墍灞炲潡锛氱疮璁� / 褰撴湀 / 鍏跺畠"""
+ for rr in range(r, 0, -1):
+ a = ws.cell(row=rr, column=1).value; b = ws.cell(row=rr, column=2).value
+ txt = " ".join(str(x) for x in (a, b) if isinstance(x, str))
+ if not isinstance(b, str) or not b.strip():
+ if len(norm(txt)) >= 6:
+ if "绱" in txt: return "绱鍧�"
+ if "鍚勫競宸�" in txt or "鎺掑悕" in txt or "鏄庣粏琛�" in txt: return "褰撴湀鍧�"
+ return "鍏跺畠"
+
+# 宸茬煡璁捐宸紓锛氫笉璁″叆闂锛堟寜鐢ㄦ埛 2026-09-28 鍙ｅ緞锛�
+KNOWN_ROWS = {" 璐ц繍": ("瑙勪笂+瑙勪笅璐ц繍閲�", "瑙勪笅璐ц繍閲�")}
+KNOWN_SHEETS_ANY_BLOCK = () # 鎺掑悕琛ㄧ殑褰撴湀鍧楃敱 block_of 澶勭悊
+
+def is_known_extra_row(sheet, indicator, side):
+ if sheet in KNOWN_ROWS and side == "绋嬪簭":
+ for pat in KNOWN_ROWS[sheet]:
+ if norm_indicator(indicator) == norm_indicator(pat): return True
+ return False
+
+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
+
+NOTE = """
+> **宸茬‘璁ゅ彛寰勶紙2026-09-28锛岀敤鎴凤級鈥斺�斾互涓嬩笉璁″叆闂**
+> 1. ` 璐ц繍` 姣忓競宸� 6 琛� vs 鎵嬪伐 4 琛岋細绋嬪簭鏂板銆岃涓�+瑙勪笅璐ц繍閲忥紙涓囧惃锛夈�嶃�岃涓嬭揣杩愰噺锛堜竾鍚級銆嶅悇 17 琛岋紙鍏� 34 琛岋級锛屽睘姣嶇増璁捐 鈫� 褰掑叆銆屽凡鐭ヨ璁�(姣嶇増鏂板琛�)銆嶃��
+> 鍥犺鏁颁笉鍚岋紝绗� k 涓競宸炲湪绋嬪簭閲屾瘮鎵嬪伐浣� 2脳(k-1) 琛岋紙姝︽眽閮戒粠绗� 11 琛岃捣锛涢粍鐭� 绋嬪簭 17 / 鎵嬪伐 15锛涚鍐滄灦 绋嬪簭 107 / 鎵嬪伐 75锛夆�斺��**绾綅缃亸绉伙紝鎸�"鍦板尯+鎸囨爣"瀵归綈鍚庢暟鍊间竴鑷�**銆�
+> 2. ` 璐ц繍!U5:U112`锛堟棤琛ㄥご杈呭姪鍒楋級鏄汉宸ヨ崏绋� 鈫� 褰掑叆銆屽凡鐭ヨ璁�(鏃犺〃澶磋崏绋垮垪)銆嶃��
+> 3. 鍚勬帓鍚嶈〃銆屽綋鏈堝潡銆嶆槸褰撳垵鍋氭瘝鐗堟椂灏卞姞涓婄殑璁捐 鈫� 褰掑叆銆屽凡鐭ヨ璁�(鎺掑悕琛ㄥ綋鏈�/绱鍧�)銆嶃��
+> 4. 甯傚窞鍚嶇煭鍚�/鍏ㄥ悕锛堟姹� vs 姝︽眽甯傦級涓嶅奖鍝� 鈫� 宸插仛褰掍竴鍖栥��
+> 5. 鍙栨暣灏惧樊銆佹枃鏈瀷鏁板瓧鍙湪姹囨�婚噷璁℃暟锛屼笉杩涖�屽疄璐ㄦ暟鍊煎樊寮傘�嶆竻鍗曘��
+>
+> **浠嶉渶澶勭悊/鏍稿**
+> - `鐝嚎鍖呰溅` 3 鏍硷細`EO36`/`FN36`/`FO36`锛堣崋闂� 瀹㈣繍閲� 2020 绱/绱鍚屾瘮锛夆�斺��**绋嬪簭鐢熸垚鏃堕噸绠椾负 -2731.41 / -1.0008 / -1.0002**锛屾瘝鐗堜笌鎵嬪伐瀹氱鍧囦负 0.181777 / 2312.2199 / -0.586419锛涚枒姣嶇増 2020 骞村潡鍒嗘瘝涓� 0锛岄渶鏍稿鍏紡涓庢暟鎹��
+> - ` 璐ц繍` 10 鏉� 2022 骞村悇甯傚窞銆岀疮璁″悓姣斻�嶏細涓氬姟宸茬‘璁�**杞欢鍙ｅ緞姝ｇ‘**锛堟墜宸ラ偅鍒楅敊锛夈��
+> - 鎵嬪伐渚у緟鏀癸細304 鏍艰崏绋垮垪銆�286 鏍兼枃鏈瀷鏁板瓧銆�4 澶勮剰鏁版嵁锛坄鐝嚎鍖呰溅!IL4/IL5` 鏂摼銆乣IL50` 鍙嶅紩鍙枫�乣 璐ц繍!EA93/EA111` 鐨� `#DIV/0!`锛夈��
+> - 姣嶇増/绋嬪簭鑷韩锛�11 涓� `#REF!` + 356 涓� `#DIV/0!`锛堝巻鍙查仐鐣欙級銆乣鍩庡競瀹㈣繍` 绗� 2 琛岀 3 涓湀璧疯〃澶翠负鏃ユ湡搴忓垪鍙枫�佺己 2026-09 姣嶇増銆佸鍑哄墠鍩烘暟浣撴鏈仛銆�
+
+"""
+
+def main(gen, dgf, prefix):
+ ACTION = ("瀹炶川鏁板�煎樊寮�", "閿欒鍊�(绋嬪簭)", "閿欒鍊�(鎵嬪伐)", "闇�澶勭悊(闈炴暟鍊兼枃鏈�)", "鏂囨湰涓嶅悓")
+ IGNORE = ("宸插拷鐣�(鍗曡竟鏈夊��)", "宸插拷鐣�(缁撴瀯/璁捐)", "鍙栨暣灏惧樊(鍙拷鐣�)", "鏂囨湰鍨嬫暟瀛�(鍙拷鐣�)",
+ "宸茬煡璁捐(姣嶇増鏂板琛�)", "宸茬煡璁捐(鏃犺〃澶磋崏绋垮垪)", "宸茬煡璁捐(鎺掑悕琛ㄥ綋鏈�/绱鍧�)", "鍏跺畠")
+ cats = list(ACTION) + list(IGNORE)
+ REASON = {(" 璐ц繍", "EA"): "2022骞村悇甯傚窞绱鍚屾瘮锛氫笟鍔″凡纭杞欢鍙ｅ緞姝ｇ‘锛堟墜宸ラ偅鍒楅敊锛�",
+ ("鐝嚎鍖呰溅", "EO"): "绋嬪簭鐢熸垚鏃堕噸绠楋紙鐤戞瘝鐗�2020骞村潡鍒嗘瘝涓�0锛夛細姣嶇増/鎵嬪伐涓� 0.1818锛屽緟鏍稿",
+ ("鐝嚎鍖呰溅", "FN"): "鍚屼笂锛氭瘝鐗�/鎵嬪伐涓� 2312.2199",
+ ("鐝嚎鍖呰溅", "FO"): "鍚屼笂锛氭瘝鐗�/鎵嬪伐涓� -0.5864"}
+ def reason_of(x):
+ ref = x[1]
+ col = "".join(ch for ch in ref if ch.isalpha())
+ return REASON.get((x[0], col), "")
+
+ 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: continue
+ if cl_g[c] == "(鏃犺〃澶村垪)":
+ bump("宸茬煡璁捐(鏃犺〃澶磋崏绋垮垪)"); continue
+ bump("宸插拷鐣�(缁撴瀯/璁捐)")
+ for c in unmatched_cols_d:
+ if c < 2: continue
+ if cl_d[c] == "(鏃犺〃澶村垪)":
+ bump("宸茬煡璁捐(鏃犺〃澶磋崏绋垮垪)"); continue
+ bump("宸插拷鐣�(缁撴瀯/璁捐)")
+ for r in unmatched_rows_g:
+ if r < 4: continue
+ rowb = "%s" % wg.cell(row=r+1, column=2).value
+ if is_known_extra_row(sh, rowb, "绋嬪簭"):
+ bump("宸茬煡璁捐(姣嶇増鏂板琛�)"); continue
+ bump("宸插拷鐣�(缁撴瀯/璁捐)")
+ for r in unmatched_rows_d:
+ if r < 4: continue
+ if "褰撴湀鍧�" in rl_d[r] or "绱鍧�" in rl_d[r]:
+ bump("宸茬煡璁捐(鎺掑悕琛ㄥ綋鏈�/绱鍧�)"); continue
+ bump("宸插拷鐣�(缁撴瀯/璁捐)")
+ for i, j in sorted(colmap.items()):
+ if cl_g[i] == "(鏃犺〃澶村垪)" or cl_d[j] == "(鏃犺〃澶村垪)":
+ bump("宸茬煡璁捐(鏃犺〃澶磋崏绋垮垪)"); continue
+ 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
+ elif (num(vg) and isinstance(vd, str)) or (num(vd) and isinstance(vg, str)):
+ cat = "闇�澶勭悊(闈炴暟鍊兼枃鏈�)"; note = "绋嬪簭=%s 鎵嬪伐=%s" % (str(vg)[:14], str(vd)[:14])
+ 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,
+ "%s%d" % (get_column_letter(j+1), q+1)])
+ 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:
+ if x[4] not in ACTION: continue
+ f.write(",".join('"%s"' % str(v).replace('"', "'") for v in x) + "\n")
+ # 杈撳嚭 MD
+ 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] in ACTION and x[4] != "鏂囨湰涓嶅悓"]
+ if not subs: f.write("锛堟棤锛塡n")
+ for x in subs[:300]:
+ f.write("- **%s** %s [%s] 鐢熸垚浠�=%s 鎵嬪伐=%s%s\n" % (x[0], x[1], x[2], x[5], x[6],
+ ("锛堝師鍥狅細%s锛�" % reason_of(x)) if reason_of(x) else ""))
+ f.write("\n## 鏍囬/琛ㄥご鎺緸宸紓\n\n")
+ tt = [x for x in rows_out if x[4] == "鏂囨湰涓嶅悓"]
+ if not tt: f.write("锛堟棤锛塡n")
+ for x in tt[:50]:
+ f.write("- %s %s [%s] 鐢熸垚浠�=%s 鎵嬪伐=%s\n" % (x[0], x[1], x[2], x[5], x[6]))
+ f.write("\n## 宸叉寜鍙ｅ緞蹇界暐锛堜粎璁℃暟锛屾槑缁嗚 CSV 涔嬪涓嶅垪鍑猴級\n\n| 椤电 | " + " | ".join(IGNORE) + " |\n|---|" + "---|"*len(IGNORE) + "\n")
+ for sh, cnt in per_sheet:
+ f.write("| %s | %s |\n" % (sh, " | ".join(str(cnt.get(c, 0)) for c in IGNORE)))
+ 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])
--
Gitblit v1.9.1