| New file |
| | |
|---|
| | | # -*- 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): |
|---|
| | | """åä¸æ¾æè¿ä¸ä¸ª"æ é¢è¡"ï¼B åä¸ºç©ºä¸ 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]) |
|---|
