# -*- 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):
|
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("仅程序有(列)"); rows_out.append([sh, "%s*" % get_column_letter(c+1), "（整列）", cl_g[c], "仅程序有(列)", "", "", "生成件多出此列"])
|
for c in unmatched_cols_d:
|
if c < 2: continue
|
if cl_d[c] == "(无表头列)":
|
bump("已知设计(无表头草稿列)"); continue
|
bump("仅手工有(列)"); rows_out.append([sh, "（无）", "（整列）", cl_d[c], "仅手工有(列)", "", "", "手工定稿多出此列"])
|
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("仅程序有(行)"); rows_out.append([sh, "第%d行" % (r+1), rl_g[r], "（整行）", "仅程序有(行)", "", "", "生成件多出此区段"])
|
for r in unmatched_rows_d:
|
if r < 4: continue
|
if "当月块" in rl_d[r] or "累计块" in rl_d[r]:
|
bump("已知设计(排名表当月/累计块)"); continue
|
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])
|
