#!/usr/bin/env python3 """Enrich the 2026-07/08 Yifangbao .xls export with winner phone numbers. The source file is an Excel 97-2003 workbook. This script keeps the upload surface stable: no new columns, no header changes, no sheet name changes. It only writes missing winner-phone cells in column O and colors them. """ from __future__ import annotations import argparse import re from collections import Counter from pathlib import Path import xlrd import xlwt from xlutils.copy import copy as copy_xls PHONE_RE = re.compile(r"(?:0\d{2,4}-?\d{6,8}(?:-\d{1,5})?|1[3-9]\d{9}|400-?\d{3}-?\d{4})") PHONE_BOOK = { "河北鑫考科技股份有限公司": ("400-189-0086", "confirmed", "official_site", "https://www.xinkaogufen.com/", "官网公开客服电话"), "南京小牛智能科技有限公司": ("025-85303775", "confirmed", "official_site", "https://www.xnzn.net/", "官网公开联系电话"), "福建联迪商用设备有限公司": ("400-658-0616", "confirmed", "official_site", "https://www.landicorp.com/", "官网公开服务热线"), "广东优信无限网络股份有限公司": ("400-101-2698", "confirmed", "official_site", "https://www.iyouxin.com/", "官网公开服务热线"), "四川新华物业有限公司": ("028-84339999", "review", "public_profile", "https://baike.fang.com/item/%E5%9B%9B%E5%B7%9D%E6%96%B0%E5%8D%8E%E7%89%A9%E4%B8%9A%E6%9C%89%E9%99%90%E5%85%AC%E5%8F%B8/3761700", "公开企业资料页列示联系方式，非官网来源"), "阳光智园科技有限公司": ("400-689-6600", "confirmed", "official_site", "https://www.ygzykj.com/home/ygzyFront.do", "官网公开客服热线"), "四川九洲北斗导航与位置服务有限公司": ("0816-2468999", "review", "public_profile", "https://www.qcc.com/firm/108f68645f11e2b033aca2aa7cf39e8b.html", "公开企业信息页检索结果显示联系电话"), "上海京东到家元信信息技术有限公司": ("021-25002888", "review", "public_group_profile", "https://about.jd.com/", "京东集团公开联系方式，非该子公司直线"), "恒宝股份有限公司": ("0511-86644324", "confirmed", "listed_company_disclosure", "https://www.cninfo.com.cn/new/disclosure/stock?stockCode=002104&orgId=gssz0002104", "上市公司公开披露联系方式"), "上海商米科技集团股份有限公司": ("400-902-1168", "confirmed", "official_site", "https://www.sunmi.com/", "官网公开服务热线"), "杭州老板电器股份有限公司": ("0571-86187810", "confirmed", "listed_company_disclosure", "https://www.cninfo.com.cn/new/disclosure/stock?stockCode=002508&orgId=9900013632", "上市公司公开披露联系方式"), "数字广东网络建设有限公司": ("020-83134200", "review", "public_profile", "https://www.digitalgd.com.cn/", "官网/公开资料可见公司联系方式，建议复核"), "正元智慧集团股份有限公司": ("0571-88994605", "confirmed", "listed_company_disclosure", "https://www.cninfo.com.cn/new/disclosure/stock?stockCode=300645&orgId=9900030832", "上市公司公开披露联系方式"), "上海新致软件股份有限公司": ("021-51105633", "confirmed", "listed_company_disclosure", "https://www.cninfo.com.cn/new/disclosure/stock?stockCode=688590&orgId=gfbj0836948", "上市公司公开披露联系方式"), "同方股份有限公司": ("010-82399888", "confirmed", "listed_company_disclosure", "https://www.cninfo.com.cn/new/disclosure/stock?stockCode=600100&orgId=gssh0600100", "上市公司公开披露联系方式"), "广东天波科技股份有限公司": ("020-66679999", "review", "public_company_profile", "https://www.telpo.com.cn/", "官网/公开资料可见联系方式，建议复核"), "深圳怡化电脑股份有限公司": ("0755-26998999", "review", "public_company_profile", "https://www.grgbanking.com/", "公开资料显示企业联系电话，建议复核"), "北京善食科技有限公司": ("400-090-0917", "review", "public_profile", "https://www.shanshi.com/", "公开品牌/企业资料显示服务热线，建议复核主体对应"), "上海东福网络科技有限公司": ("400-821-6990", "confirmed", "official_site", "https://www.dongfangfuli.com/", "官网公开客服电话"), "平安云厨科技集团有限公司": ("400-101-6888", "review", "public_profile", "https://www.pingan.com/", "公开集团联系方式，非企业直线，建议复核"), "广东伟邦科技股份有限公司": ("020-32030208", "confirmed", "public_company_profile", "https://www.neeq.com.cn/disclosure/announcement.html?companyCode=836603", "新三板公开资料列示联系方式"), "重庆新中新一卡通系统集成有限公司": ("023-68666888", "review", "public_profile", "https://www.newcapec.com.cn/", "关联公开资料显示联系电话，建议复核主体对应"), "一起生活网络成都有限公司": ("028-62013588", "review", "public_profile", "https://www.17shanyuan.com/", "公开资料显示联系电话，建议复核"), "中国移动通信集团湖南有限公司邵阳分公司": ("13906129389", "review", "public_project_contact", "https://gjxy.sosoq.net/zhigongkejichuangxinchengguoku/3669.html", "同类公开项目联系人来源，非企业总机"), "中国移动通信集团海南有限公司": ("0898-66500599", "review", "public_profile", "https://www.10086.cn/", "运营商公开联系方式，非分公司直线"), "深圳市淘淘谷信息技术有限公司": ("0755-25546800", "confirmed", "previous_verified_workbook", "https://www.tianyancha.com/company/2323125768", "复用 0631-0715 已补电话结果，同名单位且来源为企业信息平台"), "安徽银通物联有限公司": ("0551-63659330", "confirmed", "previous_verified_workbook", "https://www.tianyancha.com/company/2354921348", "复用 0631-0715 已补电话结果，同名单位且来源为企业信息平台"), "宁夏鑫优仕信息科技有限公司": ("0951-8895416", "confirmed", "previous_verified_workbook", "", "复用 0631-0715 已补电话结果，同名单位"), } ALIASES = { "正奇晟业（北京）科技有限公司": "正奇晟业(北京)科技有限公司", } def clean(value) -> str: if value is None: return "" if isinstance(value, float) and value.is_integer(): return str(int(value)) return str(value).strip() def normalize_phone(value: str) -> str: return re.sub(r"\D", "", value or "") def has_phone(value: str) -> bool: return bool(PHONE_RE.search(value or "")) def make_pattern(book, base_xf_index: int, color: str) -> xlwt.XFStyle: style = xlwt.easyxf("") style.num_format_str = "General" pattern = xlwt.Pattern() pattern.pattern = xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour = xlwt.Style.colour_map[color] style.pattern = pattern # xlutils cannot clone XF styles directly into a new writable XFStyle with # a modified pattern, so keep the fill obvious and minimal for upload review. return style def derive_internal_winner_phones(sheet) -> dict[str, str]: result = {} for row in range(2, sheet.nrows): winner = clean(sheet.cell_value(row, 12)) phone = clean(sheet.cell_value(row, 14)) if winner and has_phone(phone): result.setdefault(winner, phone) return result def enrich(input_path: Path, output_path: Path, audit_path: Path) -> dict: rb = xlrd.open_workbook(str(input_path), formatting_info=True) rs = rb.sheet_by_index(0) wb = copy_xls(rb) ws = wb.get_sheet(0) yellow_style = make_pattern(rb, 65, "yellow") red_style = make_pattern(rb, 65, "rose") internal = derive_internal_winner_phones(rs) # xlutils preserves merged regions visually, but covered cells in row 2 can # read back blank after save. Rewriting the second header row keeps import # systems that read row 2 literally from losing field names. for col in range(rs.ncols): ws.write(1, col, rs.cell_value(1, col)) audit_rows = [["原表行号", "中标单位", "写入电话", "状态", "来源类型", "来源URL", "说明"]] stats = Counter({"rows": max(0, rs.nrows - 2)}) for row in range(2, rs.nrows): winner = clean(rs.cell_value(row, 12)) existing = clean(rs.cell_value(row, 14)) buyer_phone = clean(rs.cell_value(row, 11)) if winner: stats["winner_named"] += 1 if has_phone(existing): stats["original_winner_phone"] += 1 continue if not winner: stats["blank_winner"] += 1 continue lookup = ALIASES.get(winner, winner) source = None if lookup in internal and normalize_phone(internal[lookup]) != normalize_phone(buyer_phone): source = (internal[lookup], "confirmed", "same_workbook_duplicate", "", "同表同名中标单位已有电话，按同角色复用") elif lookup in PHONE_BOOK: source = PHONE_BOOK[lookup] if not source: stats["remaining_gap"] += 1 continue phone, status, source_type, url, note = source if not has_phone(phone) or (buyer_phone and normalize_phone(phone) == normalize_phone(buyer_phone)): stats["remaining_gap"] += 1 continue ws.write(row, 14, phone, red_style if status == "review" else yellow_style) audit_rows.append([row + 1, winner, phone, status, source_type, url, note]) stats["added"] += 1 if status == "review": stats["review"] += 1 else: stats["confirmed"] += 1 output_path.parent.mkdir(parents=True, exist_ok=True) wb.save(str(output_path)) audit_book = xlwt.Workbook() audit_sheet = audit_book.add_sheet("补联审核") header_style = xlwt.easyxf("font: bold on; pattern: pattern solid, fore_colour pale_blue; align: vert centre") for r, values in enumerate(audit_rows): for c, value in enumerate(values): audit_sheet.write(r, c, value, header_style if r == 0 else xlwt.easyxf("align: wrap on, vert top")) widths = [10, 34, 18, 12, 24, 70, 56] for idx, width in enumerate(widths): audit_sheet.col(idx).width = width * 256 audit_path.parent.mkdir(parents=True, exist_ok=True) audit_book.save(str(audit_path)) stats["final_winner_phone"] = stats["original_winner_phone"] + stats["added"] stats["audit_rows"] = len(audit_rows) - 1 stats["output"] = str(output_path) stats["audit"] = str(audit_path) return dict(stats) def main() -> int: parser = argparse.ArgumentParser() parser.add_argument("--input", required=True, type=Path) parser.add_argument("--output", required=True, type=Path) parser.add_argument("--audit", required=True, type=Path) args = parser.parse_args() print(enrich(args.input, args.output, args.audit)) return 0 if __name__ == "__main__": raise SystemExit(main())