from __future__ import annotations from copy import copy from pathlib import Path from openpyxl import load_workbook from openpyxl.styles import PatternFill SOURCE = Path( "docs/final-results/20260813-yifangbao-phone-legal-enrichment/" "7月线索导入模板-0715-0812-电话法人已补全-天眼查补查.xlsx" ) OUTPUT = Path( "docs/final-results/20260813-yifangbao-phone-legal-enrichment/" "7月线索导入模板-0715-0812-电话法人已补全-可靠来源二次补查.xlsx" ) YELLOW = PatternFill("solid", fgColor="FFFFF200") RED = PatternFill("solid", fgColor="FFF4CCCC") def normalize_company(value: object) -> str: return str(value or "").strip().replace("（", "(").replace("）", ")") UPDATES = { "青岛天高智慧科技有限公司": { "phone": "86672024", "source_type": "公共资源交易主体信息", "confidence": "high", "url": "https://ggzy.qingdao.gov.cn/PortalQDManage/ShareResources/CorpInfo?corpGuid=3760f573-63b8-4ec3-8408-44b047ae9e0b", "evidence": "青岛公共资源交易电子服务系统主体信息页列示联系电话 86672024。", "note": "本次二次补查补充公共资源交易主体联系电话；号码为未加区号的本地固定电话，外呼可按青岛区号 0532 复核。", "review": False, }, "中国农业银行股份有限公司潮州分行": { "phone": "0768-3155259", "source_type": "官方采购公告联系人", "confidence": "high", "url": "https://www.abchina.com/cn/AboutABC/nonghzx/NewsCenter/cggg/202512/t20251217_2468360.htm", "evidence": "中国农业银行官网采购结果公告列示潮州分行采购监督联系人电话 0768-3155259。", "note": "官方域名公告电话，可作为潮州分行外联入口；非工商登记总机。", "review": False, }, "成都鑫金大数据有限责任公司": { "phone": "028-68252167", "source_type": "第三方企业信息页", "confidence": "medium", "url": "https://www.qiyeku.com/huangye/6838702.html", "evidence": "企业信息汇聚页显示成都鑫金大数据有限责任公司联系电话 028-68252167。", "note": "第三方汇聚来源，二次补查仍未找到官网或官方登记页，外呼前建议复核。", "review": True, }, "中国银行股份有限公司福州闽都支行": { "phone": "95566；0591-87895685", "source_type": "银行官方机构网点", "confidence": "high", "url": "https://www.bankofchina.com/sourcedb/operations/406/599/600/", "evidence": "中国银行官方机构网点页可检索福州闽都支行，并列示统一客服电话 95566 及网点电话 0591-87895685。", "note": "官方网点信息，优先使用 0591-87895685；95566 为中国银行统一客服。", "review": False, }, "张家港市大数据科技有限公司": { "phone": "0512-58589525", "source_type": "公共招标公告联系人", "confidence": "high", "url": "http://www.ezhangjiagang.com/zjg/gov/content/406464304698429440.html", "evidence": "张家港市公共招采公告中，张家港市大数据科技有限公司作为采购人列示联系电话 0512-58589525。", "note": "同主体采购公告联系电话，可作为企业外联入口；非工商登记总机。", "review": False, }, "广州市公用公交站场管理服务有限公司": { "phone": "020-87746497", "source_type": "招标公告采购人电话", "confidence": "medium", "url": "https://www.gzdc-ebidding.com/platform-info!detail?platformNews.id=777226137558986752&siteId=805553463213490176", "evidence": "公开招标公告中，广州市公用公交站场管理服务有限公司作为招标人列示联系电话 020-87746497。", "note": "公告联系人电话，二次补查未找到工商登记总机；外呼前建议复核。", "review": True, }, "中国电信股份有限公司牡丹江分公司": { "phone": "18903631552", "source_type": "主管部门公开名单", "confidence": "high", "url": "https://hlca.miit.gov.cn/xwdt/gddt/art/2024/art_a44064f1f18d4ed8b59bebce91ef15cf.html", "evidence": "黑龙江省通信管理局公开名单中列示牡丹江市分公司相关联系电话 18903631552。", "note": "主管部门公开名单电话，可作为分公司外联入口；外呼时确认是否对应当前业务线。", "review": False, }, } def style_cell(cell, review: bool = False) -> None: cell.fill = RED if review else YELLOW def copy_row_style(ws, source_row: int, target_row: int, max_col: int) -> None: ws.row_dimensions[target_row].height = ws.row_dimensions[source_row].height for col in range(1, max_col + 1): src = ws.cell(source_row, col) dst = ws.cell(target_row, col) dst.font = copy(src.font) dst.fill = copy(src.fill) dst.border = copy(src.border) dst.alignment = copy(src.alignment) dst.number_format = src.number_format dst.protection = copy(src.protection) def update_main_sheet(ws) -> tuple[int, list[int]]: changed_rows: list[int] = [] for row in range(3, ws.max_row + 1): company = normalize_company(ws.cell(row, 13).value) update = UPDATES.get(company) if not update: continue if not str(ws.cell(row, 20).value or "").strip(): ws.cell(row, 20).value = update["phone"] style_cell(ws.cell(row, 20), update["review"]) assignments = { 21: update["phone"], 22: update["source_type"], 23: update["confidence"], 24: update["url"], 31: update["evidence"], 32: update["note"], } for col, value in assignments.items(): ws.cell(row, col).value = value style_cell(ws.cell(row, col), update["review"] and col in (20, 21, 23, 31, 32)) changed_rows.append(row) return len(changed_rows), changed_rows def update_summary_sheet(ws) -> int: changed = 0 for row in range(2, ws.max_row + 1): company = normalize_company(ws.cell(row, 2).value) update = UPDATES.get(company) if not update: continue values = { 4: update["phone"], 5: update["phone"], 8: update["source_type"], 9: update["confidence"], 10: update["url"], 11: update["evidence"], 12: update["note"], } for col, value in values.items(): ws.cell(row, col).value = value style_cell(ws.cell(row, col), update["review"] and col in (4, 5, 9, 11, 12)) changed += 1 return changed def rebuild_gap_sheet(wb) -> int: main = wb["已补电话-新增标黄"] gap = wb["缺口清单"] rows = [] for row in range(3, main.max_row + 1): company = str(main.cell(row, 13).value or "").strip() final_phone = str(main.cell(row, 20).value or "").strip() added_phone = str(main.cell(row, 21).value or "").strip() tyc_phone = str(main.cell(row, 25).value or "").strip() if final_phone or added_phone or tyc_phone: continue note = main.cell(row, 32).value or "仍缺少可直接回填的联系电话。" rows.append([row, main.cell(row, 2).value, main.cell(row, 5).value, company, note]) if gap.max_row > 1: gap.delete_rows(2, gap.max_row - 1) for offset, row_values in enumerate(rows, start=2): copy_row_style(gap, 1, offset, 5) for col, value in enumerate(row_values, start=1): gap.cell(offset, col).value = value gap.auto_filter.ref = f"A1:E{max(1, len(rows) + 1)}" return len(rows) def main() -> None: wb = load_workbook(SOURCE) main_changed, rows = update_main_sheet(wb["已补电话-新增标黄"]) summary_changed = update_summary_sheet(wb["企业补全汇总"]) gap_count = rebuild_gap_sheet(wb) wb.save(OUTPUT) print( { "output": str(OUTPUT), "main_changed_rows": main_changed, "row_numbers": rows, "summary_changed_companies": summary_changed, "remaining_gap_rows": gap_count, } ) if __name__ == "__main__": main()