# -*- coding: utf-8 -*-
"""把 人员信息表.xlsx 的 E 列（详细地址）从「解析备注」恢复成真实地址，备注移到 J 列。"""
import openpyxl
import os

SRC = r"D:\workbbbbbb\workb\人员信息表.xlsx"
HOME_ADDR = {
    "崇盛里": "拱墅区崇盛里（近温州路社区）",
    "风来旭和府": "临平区乔司街道风来旭和府",
    "如玺华院": "临平区乔司街道如玺华院",
    "桦枫居": "拱墅区石桥街道桦枫居（原下城区，近石桥支行）",
    "盛世钱塘": "上城区富春路899号盛世钱塘（钱江新城）",
    "吉鸿家园": "西湖区三墩镇吉鸿社区吉鸿家园（和景苑/临秀苑/望昕苑）",
    "海上海": "拱墅区海上海（湖墅）",
    "慧仁家园": "西湖区嘉仁路慧仁家园",
    "圣苑": "西湖区圣苑",
    "水韵康桥康泽苑": "拱墅区康桥街道康桥路372号 水韵康桥康泽苑",
}
NOTE_MARKS = ("已录小区：", "未识别：", "网点地名：", "区县中心：")

wb = openpyxl.load_workbook(SRC)
ws = wb["Sheet1"]
hdr = {}
for c in ws[1]:
    v = str(c.value or "").strip()
    if v:
        hdr.setdefault(v, []).append(c.column)
C_NAME = hdr.get("名称", [1])[0]
C_HOME = hdr.get("住址", [4])[0]
C_ADDR = hdr.get("详细地址", [5])[0]
NOTE_COL = max([x for v in hdr.values() for x in v]) + 1  # 最后一列之后新开一列
ws.cell(1, NOTE_COL, "解析备注")

changed = 0
for r in range(2, ws.max_row + 1):
    nm = ws.cell(r, C_NAME).value
    if not nm:
        continue
    home = str(ws.cell(r, C_HOME).value or "").strip()
    cur = str(ws.cell(r, C_ADDR).value or "").strip()
    is_note = any(cur.startswith(k) for k in NOTE_MARKS)
    note = cur if is_note else ""
    real = HOME_ADDR.get(home, "")
    if not real:
        real = cur if not is_note else ""
    if is_note or (real and real != cur):
        ws.cell(r, C_ADDR, real)
        ws.cell(r, NOTE_COL, note)
        changed += 1
        print("FIX %s | E=%s | J=%s" % (nm, real, note[:40]))

ws.column_dimensions["E"].width = 46
from openpyxl.utils import get_column_letter
ws.column_dimensions[get_column_letter(NOTE_COL)].width = 46
wb.save(SRC)
print("changed=%d noteCol=%s size=%d" % (changed, get_column_letter(NOTE_COL), os.path.getsize(SRC)))