#!/usr/bin/env python3 """Build a lead import workbook from the latest Yifangbao phone-enriched sheet.""" from __future__ import annotations import argparse import re from copy import copy from pathlib import Path from openpyxl import load_workbook DIRECT_PROVINCES = {"北京", "上海", "天津", "重庆"} AUTONOMOUS_PROVINCES = { "内蒙古": "内蒙古自治区", "广西": "广西壮族自治区", "宁夏": "宁夏回族自治区", "新疆": "新疆维吾尔自治区", "西藏": "西藏自治区", } SPECIAL_CITIES = { "湘西": "湘西土家族苗族自治州", "阿坝": "阿坝藏族羌族自治州", "黔东南": "黔东南苗族侗族自治州", "大兴安岭": "大兴安岭地区", } 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_province(value: str) -> str: value = clean(value).replace("--", "") if not value: return "" if value in DIRECT_PROVINCES: return value + "市" if value in AUTONOMOUS_PROVINCES: return AUTONOMOUS_PROVINCES[value] if value in {"香港", "澳门", "台湾"}: return value if value.endswith(("省", "市", "自治区")): return value return value + "省" def normalize_city(value: str) -> str: value = clean(value).replace("--", "") if not value: return "" if value in SPECIAL_CITIES: return SPECIAL_CITIES[value] if value.endswith(("市", "州", "盟", "地区", "县", "区")): return value return value + "市" def copy_row_style(ws, src_row: int, dst_row: int, max_col: int) -> None: ws.row_dimensions[dst_row].height = ws.row_dimensions[src_row].height for col in range(1, max_col + 1): src = ws.cell(src_row, col) dst = ws.cell(dst_row, col) if src.has_style: 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 capture_row_style(ws, src_row: int, max_col: int) -> tuple[float | None, list[dict]]: row_styles = [] for col in range(1, max_col + 1): src = ws.cell(src_row, col) row_styles.append( { "font": copy(src.font), "fill": copy(src.fill), "border": copy(src.border), "alignment": copy(src.alignment), "number_format": src.number_format, "protection": copy(src.protection), } ) return ws.row_dimensions[src_row].height, row_styles def apply_captured_row_style(ws, dst_row: int, saved_style: tuple[float | None, list[dict]]) -> None: height, row_styles = saved_style ws.row_dimensions[dst_row].height = height for col, style in enumerate(row_styles, start=1): dst = ws.cell(dst_row, col) dst.font = copy(style["font"]) dst.fill = copy(style["fill"]) dst.border = copy(style["border"]) dst.alignment = copy(style["alignment"]) dst.number_format = style["number_format"] dst.protection = copy(style["protection"]) def first_sheet(path: Path): wb = load_workbook(path, data_only=False) return wb, wb[wb.sheetnames[0]] def build(template_path: Path, source_path: Path, output_path: Path) -> dict: template_wb, template_ws = first_sheet(template_path) source_wb = load_workbook(source_path, data_only=True) source_ws = source_wb["已补电话-新增标黄"] max_col = 21 row2_style = capture_row_style(template_ws, 2, max_col) row3_style = capture_row_style(template_ws, 3 if template_ws.max_row >= 3 else 2, max_col) # Delete all May data while preserving the header row, sheet settings and row-2 style template. if template_ws.max_row > 2: template_ws.delete_rows(3, template_ws.max_row - 2) style_values = [template_ws.cell(2, col).value for col in range(1, template_ws.max_column + 1)] default_business_type = clean(template_ws["B2"].value) or "数字体育科技事业部" default_source = clean(template_ws["C2"].value) or "乙方宝-数科" default_department = clean(template_ws["D2"].value) or "数科业务部" default_pool = clean(template_ws["E2"].value) or "数字体育科技事业部公海池" default_owner = clean(template_ws["G2"].value) or "申海宇" rows_written = 0 for src_row in range(3, source_ws.max_row + 1): dst_row = 2 + rows_written apply_captured_row_style(template_ws, dst_row, row2_style if dst_row == 2 else row3_style) project_name = source_ws.cell(src_row, 2).value publish_date = source_ws.cell(src_row, 3).value province = normalize_province(source_ws.cell(src_row, 5).value) city = normalize_city(source_ws.cell(src_row, 6).value) amount = source_ws.cell(src_row, 9).value buyer = source_ws.cell(src_row, 10).value buyer_contact = source_ws.cell(src_row, 11).value buyer_phone = source_ws.cell(src_row, 12).value winner = source_ws.cell(src_row, 13).value winner_contact = source_ws.cell(src_row, 14).value winner_phone = source_ws.cell(src_row, 15).value url = source_ws.cell(src_row, 19).value final_phone = source_ws.cell(src_row, 20).value tyc_phone = source_ws.cell(src_row, 25).value legal_person = source_ws.cell(src_row, 27).value company_name = clean(winner) or clean(buyer) if not company_name: company_name = clean(style_values[0]) row_values = [ company_name, default_business_type, default_source, default_department, default_pool, f"=G{dst_row}" if dst_row == 2 else default_owner, default_owner, project_name, publish_date, province, city, amount, buyer, buyer_contact, buyer_phone, winner, winner_contact, winner_phone or final_phone, url, legal_person, tyc_phone or final_phone, ] for col, value in enumerate(row_values, start=1): template_ws.cell(dst_row, col).value = "" if value is None else value rows_written += 1 # Remove leftover row 2 only if the source had no rows; otherwise it has been overwritten. template_ws.auto_filter.ref = f"A1:U{1 + rows_written}" template_ws.freeze_panes = "A2" output_path.parent.mkdir(parents=True, exist_ok=True) template_wb.save(output_path) return {"rows_written": rows_written, "output": str(output_path)} def main() -> int: parser = argparse.ArgumentParser() parser.add_argument("--template", required=True, type=Path) parser.add_argument("--source", required=True, type=Path) parser.add_argument("--output", required=True, type=Path) args = parser.parse_args() print(build(args.template, args.source, args.output)) return 0 if __name__ == "__main__": raise SystemExit(main())