快递揽收单 & 标签模板自动生成器

一份「源数据」Excel，几十个需求单号，采购单号用斜杠拼接——仓库同事原本要花一整个下午手工拆表。这个脚本双击运行，3 秒出结果，自动处理 PO 拆分、箱数均分、城市提取，并保留原 Excel 模板格式。

一、核心功能架构

二、数据流处理流程

三、核心函数详解

3.1 地址解析 — extract_city_from_address()

从表格中库房地址字符串中提取城市名，处理省-市-区、直辖市、自治区等多种格式：

def extract_city_from_address(addr: str) -> str:
    """从表格中库房地址中提取城市名（处理省/自治区/直辖市）"""
    if not addr:
        return ""
    addr = str(addr).strip()
    
    # 第一级：匹配到县区级别
    m = re.match(r"(.+?[市县区])", addr)
    if not m:
        return addr[:4]
    city = m.group(1)
    
    # 第二级：处理带省份前缀的地址
    prov = re.match(r".+?(?:省|自治区)", addr)
    if prov:
        rest = addr[prov.end():]
        dm = re.match(r"(.+?[市县区])", rest)
        if dm:
            city = dm.group(1)
    # 第三级：处理直辖市（如北京市朝阳区）
    elif city.endswith("市") and len(addr) > len(m.group(1)):
        rest = addr[len(m.group(1)):]
        dm = re.match(r"(.+?[市区县])", rest)
        if dm:
            city = dm.group(1)
    
    # 清理后缀：广州**市** → 广州
    return re.sub(r"[市区县]$", "", city)

3.2 箱数处理 — safe_int() & distribute_evenly()

处理发货箱数的小数向上取整，以及在多个 PO 之间均分箱数（余数补前）：

def safe_int(v) -> int:
    """安全转换为整数，小数向上取整"""
    try:
        return math.ceil(float(v)) if v else 0
    except (ValueError, TypeError):
        return 0

def distribute_evenly(total: int, n: int) -> list[int]:
    """均分箱数，前 remainder 份各+1
    示例: 10箱÷3 → [4,3,3]；7箱÷2 → [4,3]"""
    if n <= 0:
        return []
    base = total // n
    remainder = total % n
    return [base + (1 if i < remainder else 0) 
            for i in range(n)]

3.3 揽收单数据构建 — build_pickup_data()

按需求单号分组汇总，去重 PO，均分箱数：

def build_pickup_data(rows: list[dict], 
                      po_city_map: dict[str, str] | None = None) -> list[dict]:
    """构建揽收单数据：按需求单号分组，PO去重保序，箱数均分"""
    by_req = defaultdict(list)
    for r in rows:
        req = str(r.get("需求单号", ""))
        by_req[req].append(r)
    
    pickup_rows = []
    seq = 0
    for req_no in sorted(by_req.keys()):
        group = by_req[req_no]
        
        # 收集所有 PO（去重保序）
        all_pos, seen = [], set()
        for r in group:
            for po in split_po(r.get("采购单号", "")):
                if po not in seen:
                    all_pos.append(po)
                    seen.add(po)
        
        total_boxes = sum(safe_int(r.get("发货箱数")) for r in group)
        addr = str(group[0].get("库房地址", "") or "")
        boxes_list = distribute_evenly(total_boxes, len(all_pos))
        
        for i, po in enumerate(all_pos):
            seq += 1
            city = po_city_map.get(po) if po_city_map else None
            if not city:
                city = extract_city_from_address(addr)
            
            pickup_rows.append({
                "seq": seq, "po": po, 
                "boxes": boxes_list[i],
                "city": city, "center": center,
                "warehouse": warehouse, "addr": addr
            })
    return pickup_rows

3.4 标签模板数据构建 — build_label_data()

按最细粒度展开：每行源数据 → 拆分 PO → 每箱一行标签：

def build_label_data(rows: list[dict], 
                     po_city_map: dict[str, str] | None = None) -> list[dict]:
    """构建标签数据：SKU × PO × 箱号 逐行展开"""
    labels = []
    for r in rows:
        pos = split_po(r.get("采购单号", ""))
        boxes = safe_int(r.get("发货箱数"))
        
        if boxes <= 0 or not pos:
            continue
        
        supplier = str(r.get("供应商简码", "") or "")
        addr = str(r.get("库房地址", "") or "")
        warehouse = str(r.get("送货仓库", "") or "")
        upc = str(r.get("UPC码", "") or "")
        product = str(r.get("产品名称", "") or "")
        spec = str(r.get("箱规", "") or "")
        
        boxes_list = distribute_evenly(boxes, len(pos))
        
        for i, po in enumerate(pos):
            po_boxes = boxes_list[i]
            if po_boxes <= 0:
                logger.warning(f"采购单号 {po} 被分配 0 箱，已跳过")
                continue
            
            if po_city_map and po in po_city_map:
                city = po_city_map[po]
            else:
                city = extract_city_from_address(addr)
            
            # 按箱展开：第 1 箱、第 2 箱...
            for box_no in range(1, po_boxes + 1):
                labels.append({
                    "供应商名称": supplier,
                    "采购单号": po,
                    "目的城市": city,
                    "目的库房": warehouse,
                    "SKU": upc,
                    "商号名称": product,
                    "箱规": spec,
                    "第几箱": box_no,
                    "共多少箱": po_boxes
                })
    return labels

四、模板写入与格式保留

4.1 揽收单写入 — write_pickup()

在已有模板上动态调整行数，保留格式，处理合并单元格：

def write_pickup(ws, data: list[dict]) -> None:
    """写入揽收单：动态插入/删除行，保留模板格式"""
    start_row = find_pickup_start_row(ws)  # 自动定位"序号"行+1
    data_count = len(data)
    
    # 定位汇总行（"提货总箱数"或"签字确认"）
    summary_row = None
    for r in range(start_row, ws.max_row + 1):
        v = ws.cell(row=r, column=2).value
        if v and "提货总箱数" in str(v):
            summary_row = r
            break
    
    current_data_rows = summary_row - start_row
    diff = data_count - current_data_rows
    
    # 动态调整行数：插入或删除
    if diff > 0:
        # 需要插入行
        _shift_merged_cells_down(ws, summary_row, diff)
        ws.insert_rows(summary_row, diff)
        # 复制格式...
    elif diff < 0:
        # 需要删除行
        del_count = -diff
        del_start = summary_row - del_count
        _shift_merged_cells_up(ws, del_start + del_count, del_count)
        ws.delete_rows(del_start, del_count)
    
    # 写入数据（处理 MergedCell）
    for idx, d in enumerate(data):
        r = start_row + idx
        _safe_write_cell(ws, r, 1, d["seq"])
        _safe_write_cell(ws, r, 2, d["po"])
        _safe_write_cell(ws, r, 3, d["boxes"])
        _safe_write_cell(ws, r, 4, d["city"])
    
    # 写入汇总
    total = sum(d["boxes"] for d in data)
    ws.cell(row=summary_row, column=2).value = "提货总箱数*："
    ws.cell(row=summary_row, column=3).value = total

4.2 标签模板写入 — write_labels()

标签模板是完全覆盖写入，保留表头样式：

def write_labels(ws, data: list[dict]) -> None:
    """写入标签模板：清空旧数据，重写表头+数据"""
    headers = ["供应商名称", "采购单号", "目的城市", "目的库房",
               "SKU", "商号名称", "箱规", "第几箱", "共多少箱"]
    
    # 清空旧数据（保留第 1 行表头）
    if ws.max_row > 1:
        ws.delete_rows(2, ws.max_row)
    
    # 写入数据行
    for ri, label in enumerate(data, 2):
        for ci, h in enumerate(headers, 1):
            apply_style(ws.cell(row=ri, column=ci),
                       font=FONT_NORMAL, border=BORDER_THIN,
                       align=ALIGN_CENTER, value=label[h])
    
    # 重写表头样式
    for ci, h in enumerate(headers, 1):
        apply_style(ws.cell(row=1, column=ci),
                   font=FONT_HEADER, fill=FILL_HEADER,
                   border=BORDER_THIN, align=ALIGN_CENTER)
    
    # 调整列宽
    widths = [14, 18, 10, 30, 16, 35, 8, 8, 10]
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(i)].width = w

五、文件占用处理 — 重试/取消对话框

针对 Excel 文件被占用的情况，实现了带 GUI 对话框的重试机制：

def popup_retry_cancel(title: str, message: str) -> bool:
    """弹出重试/取消对话框，返回 True 表示重试"""
    try:
        import tkinter as tk
        from tkinter import messagebox
        root = tk.Tk()
        root.withdraw()
        root.attributes("-topmost", True)
        result = messagebox.askretrycancel(title, message, 
                                              parent=root, icon="warning")
        root.destroy()
        return result
    except Exception:
        # 回退到命令行交互
        while True:
            choice = input("\n[重试(r)/取消(c)]: ").strip().lower()
            if choice in ("r", "重试"): return True
            if choice in ("c", "取消"): return False

def load_workbook_with_retry(filepath: str, data_only: bool = False):
    """加载工作簿，文件被占用时弹出重试对话框"""
    while True:
        try:
            return load_workbook(filepath, data_only=data_only)
        except PermissionError:
            msg = f"文件被占用：{filepath}\n\n请先关闭该文件的 Excel 窗口，然后点击「重试」。"
            if not popup_retry_cancel("文件被占用", msg):
                raise PermissionError(f"用户取消操作: {filepath}")
            time.sleep(0.5)

六、源数据子表智能定位

支持多种子表命名情况，策略是精确优先，唯一模糊自动选，多个模糊弹窗选：

def find_source_sheet(sheetnames: list[str]) -> Optional[str]:
    """查找源数据子表：精确匹配 → 唯一模糊 → 弹窗选择"""
    # 1. 精确匹配优先
    for name in ("源数据-1", "源数据"):
        if name in sheetnames:
            return name
    
    # 2. 唯一模糊候选 → 自动选
    candidates = [n for n in sheetnames if "源数据" in n]
    if len(candidates) == 1:
        return candidates[0]
    
    # 3. 多个候选 → 返回 None，触发弹窗让用户选
    return None

def pick_sheet(sheetnames: list[str]) -> Optional[str]:
    """弹窗让用户选择数据源子表（tkinter Listbox）"""
    try:
        import tkinter as tk
        root = tk.Tk()
        root.title("选择数据源子表")
        root.attributes("-topmost", True)
        
        selected = {"name": None}
        label = tk.Label(root, text="找不到源数据子表\n请选择要作为数据源的子表：",
                         font=("微软雅黑", 11))
        label.pack(pady=(15, 10), padx=20)
        
        listbox = tk.Listbox(root, font=("微软雅黑", 10), 
                      width=40, height=min(len(sheetnames), 15),
                      selectmode=tk.SINGLE, activestyle="dotbox")
        for name in sheetnames:
            listbox.insert(tk.END, name)
        listbox.selection_set(0)
        listbox.pack(padx=20, pady=5)
        
        def on_confirm():
            sel = listbox.curselection()
            if sel:
                selected["name"] = sheetnames[sel[0]]
            root.destroy()
        
        btn = tk.Button(root, text="确  定", command=on_confirm)
        btn.pack(pady=(5, 15))
        
        root.mainloop()
        return selected["name"]
    except (ImportError, Exception) as e:
        # 回退到命令行选择...

七、数据读取优化 — iter_rows

使用 iter_rows(values_only=True) 批量读取，比逐个 cell() 调用快 3-5 倍：

def read_source(ws) -> list[dict]:
    """批量读取源数据，按列名建索引"""
    rows_iter = ws.iter_rows(min_row=1, values_only=True)
    headers_raw = next(rows_iter, None)
    if not headers_raw:
        return []
    
    # 列名 → 索引映射（不依赖列顺序）
    col_map = {name: i for i, name in enumerate(headers_raw) if name}
    req_idx = col_map.get("需求单号")
    
    rows = []
    for row in rows_iter:
        # 跳过空行（以需求单号判断）
        if req_idx is not None and not row[req_idx]:
            continue
        rows.append({name: row[i] for name, i in col_map.items()})
    return rows

八、依赖与使用方式

使用方式

九、函数索引

十、设计亮点总结

• 格式保留：揽收单在现有模板上覆盖，不动边框和合并单元格；标签完全重写但保留表头样式
• 智能定位：源数据子表支持精确匹配、模糊匹配、弹窗选择三级策略
• 跨表扫描：构建 PO→城市映射时扫描所有子表，解决数据粒度不一致问题
• 健壮写入：处理 MergedCell、动态插入删除行、复制行高和格式
• 用户友好：文件被占用时弹出重试/取消对话框，而非直接报错
• 零依赖分发：Nuitka 打包成单文件 exe，同事无需安装 Python