Python 批量处理 Excel：保留原文件并生成差异报告

py -m venv .venv
.\.venv\Scripts\python.exe -m pip install "openpyxl>=3.1,<3.2"
.\.venv\Scripts\python.exe make_fixture.py

from openpyxl import Workbook
from pathlib import Path
if Path("orders.xlsx").exists(): raise FileExistsError("不要覆盖已有文件")
wb = Workbook(); ws = wb.active; ws.title = "orders"
ws.append(["order_id","date","channel","amount","double"])
ws.append(["00123","2026/9/1","wechat",100,"=D2*2"])
ws.append(["00124","01/09/2026","微信",200,"=D3*2"])
ws.append(["00125","2026/9/3","unknown",50,"=D4*2"])
wb.create_sheet("notes")["A1"] = "教学数据；不包含宏、图形或外部链接"
wb.save("orders.xlsx")

from pathlib import Path
from datetime import datetime
import csv, hashlib
from openpyxl import load_workbook
source = Path("orders.xlsx")
before = hashlib.sha256(source.read_bytes()).hexdigest()
wb = load_workbook(source, data_only=False)
ws = wb["orders"]
if [ws.cell(1, c).value for c in range(1, 6)] != ["order_id","date","channel","amount","double"]:
    raise ValueError("表头不符合本练习，不猜列")
formula_before = sum(c.data_type == "f" for sheet in wb for row in sheet for c in row)
ws.cell(1,6,"date_normalized"); ws.cell(1,7,"channel_normalized")
diff = []
channels = {"wechat":"微信", "微信":"微信"}
for row in range(2, ws.max_row + 1):
    raw_date, raw_channel = ws.cell(row,2).value, ws.cell(row,3).value
    normalized, reason = raw_date, ""
    try:
        if not isinstance(raw_date, str) or raw_date.count("/") != 2 or len(raw_date.split("/")[0]) != 4:
            raise ValueError()
        normalized = datetime.strptime(raw_date, "%Y/%m/%d").strftime("%Y-%m-%d")
    except ValueError:
        reason = "日期不是明确的年/月/日，保留原值"
    channel = channels.get(raw_channel, raw_channel)
    if raw_channel not in channels:
        reason += ("；" if reason else "") + "渠道未在映射表中，保留原值"
    ws.cell(row,6,normalized); ws.cell(row,7,channel)
    diff.append([row, ws.cell(row,1).value, raw_date, normalized, raw_channel, channel, reason])
target = Path("orders_cleaned.xlsx")
if target.exists() or Path("orders_diff.csv").exists():
    raise FileExistsError("输出已存在；先检查或换一个练习目录")
wb.save(target)
with open("orders_diff.csv","w",encoding="utf-8-sig",newline="") as f:
    writer = csv.writer(f)
    writer.writerow(["row","order_id","date_old","date_new","channel_old","channel_new","reason"])
    writer.writerows(diff)
check = load_workbook(target, data_only=False)
formula_after = sum(c.data_type == "f" for sheet in check for row in sheet for c in row)
assert hashlib.sha256(source.read_bytes()).hexdigest() == before
assert formula_after == formula_before == 3
assert check["orders"]["A2"].value == "00123"
print("原文件哈希未变；公式数量3；差异记录", len(diff))

.\.venv\Scripts\python.exe clean.py