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: text = str(value or "").strip() return text.replace("（", "(").replace("）", ")") UPDATES = { "湖南有线江永网络有限公司": { "phone": "0746-5728023", "source_type": "企业公开信息页", "confidence": "high", "url": "https://www.qiyeku.com/huangye/2082377.html", "evidence": "公开企业信息页显示联系电话 0746-5728023。", "note": "本次补充公开登记电话，拨打前建议确认在用状态。", "review": False, }, "正奇晟业(北京)科技有限公司": { "phone": "400-008-2300", "source_type": "官网/品牌公开电话", "confidence": "high", "url": "https://www.mankebao.com/", "evidence": "满客宝官网公开客服电话 400-008-2300，企业主体为正奇晟业。", "note": "本次补充品牌官网外联电话。", "review": False, }, "多彩贵州数字科技股份有限公司": { "phone": "18985579640", "source_type": "项目公告联系人", "confidence": "medium", "url": "https://www.yfbzb.com/winbid/detail/20260703_563692054.html", "evidence": "相关项目公告披露联系人电话 18985579640。", "note": "项目联系人电话，不等同工商登记总机，使用前建议复核。", "review": True, }, "上海科稷网络技术有限公司": { "phone": "021-80198002", "source_type": "企业公开信息页", "confidence": "high", "url": "https://www.tianyancha.com/search?key=%E4%B8%8A%E6%B5%B7%E7%A7%91%E7%A8%B7%E7%BD%91%E7%BB%9C%E6%8A%80%E6%9C%AF%E6%9C%89%E9%99%90%E5%85%AC%E5%8F%B8", "evidence": "公开企业信息页显示联系电话 021-80198002。", "note": "本次补充公开登记电话。", "review": False, }, "保定市数字政务后勤服务有限公司": { "phone": "13792286543", "source_type": "项目公告联系人", "confidence": "medium", "url": "https://www.zgggzy.com/", "evidence": "项目公开公告中披露联系人电话 13792286543。", "note": "项目联系人电话，疑似可达但不是工商登记总机，建议复核。", "review": True, }, "深圳东贝食安技术有限公司": { "phone": "13500244822", "source_type": "官网公开电话", "confidence": "high", "url": "http://www.sidon.cn/contact.html", "evidence": "企业官网联系我们页公开联系电话 13500244822。", "note": "本次补充企业官网联系电话。", "review": False, }, "成都蓉卡科技有限公司": { "phone": "400-000-1213；028-86665266", "source_type": "官网公开电话", "confidence": "high", "url": "http://www.cntech.com.cn/", "evidence": "企业官网公开 400-000-1213、028-86665266。", "note": "本次补充企业官网联系电话。", "review": False, }, "呼和浩特市远广科技有限公司": { "phone": "0471-5222624；13948128282", "source_type": "第三方企业信息页", "confidence": "medium", "url": "https://www.yellowurl.cn/", "evidence": "第三方企业信息页显示电话 0471-5222624、13948128282。", "note": "第三方汇聚来源，需电话外呼前复核。", "review": True, }, "联通数字科技有限公司陕西分公司": { "phone": "029-68588168", "source_type": "第三方企业信息页", "confidence": "medium", "url": "https://www.shuididp.cn/", "evidence": "第三方企业信息页显示联系电话 029-68588168。", "note": "第三方汇聚来源，需电话外呼前复核。", "review": True, }, "广西申能达智能技术有限公司": { "phone": "0771-3215588；0771-3210450", "source_type": "官网公开电话", "confidence": "high", "url": "http://www.gx-shennengda.com/", "evidence": "企业官网公开联系电话 0771-3215588、0771-3210450。", "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 original_final = str(ws.cell(row, 20).value or "").strip() if not original_final: 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 (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 (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_ws = wb["已补电话-新增标黄"] main_changed, rows = update_main_sheet(main_ws) 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()