# -*- coding: utf-8 -*-
"""把查到的区县/街道写进「详细地址」列，把自动认领结果写进「负责辖区」F~I 列。"""
import io, sys, json, openpyxl
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', errors='replace')

SRC = r"D:\workbbbbbb\workb\人员信息表.xlsx"
OUT_TXT = r"D:\workbbbbbb\build\_addr_out.txt"

DETAIL = {
    "崇盛里":   "拱墅区崇盛里（近温州路社区）",
    "风来旭和府": "临平区乔司街道风来旭和府",
    "如玺华院":  "临平区乔司街道如玺华院",
    "桦枫居":   "拱墅区石桥街道桦枫居（原下城区，近石桥支行）",
    "盛世钱塘":  "上城区富春路899号盛世钱塘（钱江新城）",
    "吉鸿家园":  "西湖区三墩镇吉鸿社区吉鸿家园（和景苑/临秀苑/望昕苑）",
    "慧仁家园":  "西湖区嘉仁路慧仁家园",
    "圣苑":    "西湖区圣苑",
}

s = open(OUT_TXT, encoding="utf-8").read()
line = [l for l in s.splitlines() if l.startswith("AREAJSON=")][0]
alloc = {p["n"]: p for p in json.loads(line[9:])}

wb = openpyxl.load_workbook(SRC)
ws = wb.worksheets[0]
hdr = {}
for c in ws[1]:
    v = str(c.value or "").strip()
    if v:
        hdr.setdefault(v, []).append(c.column)
C_NAME = hdr.get("名称", hdr.get("姓名", [1]))[0]
C_HOME = hdr["住址"][0]
C_ADDR = hdr.get("详细地址", [5])[0]
AREA_COLS = sorted(hdr.get("负责辖区", []))
print("hdr:", {k: v for k, v in hdr.items()}, "AREA_COLS=", AREA_COLS)

for r in range(2, ws.max_row + 1):
    nm = ws.cell(r, C_NAME).value
    if not nm:
        continue
    nm = str(nm).strip()
    home = str(ws.cell(r, C_HOME).value or "").strip()
    if home in DETAIL:
        ws.cell(r, C_ADDR).value = DETAIL[home]
    a = alloc.get(nm)
    areas = (a or {}).get("areas") or []
    for k, colidx in enumerate(AREA_COLS):
        ws.cell(r, colidx).value = areas[k] if k < len(areas) else None
    print("  %-4s %-6s -> %-46s %s" % (nm, home, DETAIL.get(home, "-"), ",".join(areas)))

ws.column_dimensions["E"].width = 46
wb.save(SRC)
print("saved", SRC)