# 从每天 3 小时到 10分钟：用 Python + openpyxl 自动生成 Excel 工单的完整实践

```
OA 导出的明细 Excel
        │
        ▼
  打样订单整理.py
  ├─ 读取明细 → 解析/校验字段
  ├─ 打开模板 → 填充数据 + 插入零件图
  ├─ 读取单号进度.json → 分配流水号 → 回写进度
  ├─ 另存为工单 Excel（本地输出目录）
  └─ 归档到共享盘 Z:\ + 追加年度总案表（可失败，静默跳过）
        │
        ▼
  打印 → 下发车间
```

```python
from openpyxl import load_workbook
from copy import copy

TEMPLATE = r"E:\AI\模板\订单铣样清单.xlsx"

def build_work_order(rows, out_path):
    wb = load_workbook(TEMPLATE)
    ws = wb.active

    # 填表头字段
    ws["B2"] = rows[0]["order_no"]      # 订单号
    ws["F2"] = rows[0]["owner"]         # 负责人
    ws["H2"] = rows[0]["date_str"]      # 日期

    # 填明细行：从模板里的"第一行明细"往下复制样式
    start = 6
    tpl_row = ws[start]
    for i, r in enumerate(rows):
        cur = start + i
        if cur > start:                 # 复制上一行样式到新行
            for col in range(1, 9):
                src, dst = ws.cell(cur-1, col), ws.cell(cur, col)
                dst._style = copy(src._style)
        ws.cell(cur, 1, i + 1)          # 序号
        ws.cell(cur, 2, r["part_name"]) # 零件名称
        ws.cell(cur, 3, r["qty"])       # 数量
        ws.cell(cur, 4, r["deadline"])  # 交期

    wb.save(out_path)
```

```python
import json, os

PROGRESS = r"E:\AI\内部单号进度.json"

def next_serial(count=1):
    """批量分配 count 个流水号，原子性回写进度"""
    with open(PROGRESS, encoding="utf-8") as f:
        prog = json.load(f)

    key = time.strftime("%y%m")            # 如 "2609"
    cur = prog.get(key, 1988) + 1
    nums = list(range(cur, cur + count))
    prog[key] = cur + count - 1

    tmp = PROGRESS + ".tmp"                # 先写临时文件再替换，防中途断电写坏
    with open(tmp, "w", encoding="utf-8") as f:
        json.dump(prog, f, ensure_ascii=False)
    os.replace(tmp, PROGRESS)
    return nums
```

```python
def safe_archive(files):
    try:
        if not os.path.exists(r"Z:\CNCtudang"):
            raise OSError("共享盘不可用")
        month_dir = rf"Z:\CNCtudang\订单图档\{time.strftime('%Y.%m')}"
        os.makedirs(month_dir, exist_ok=True)
        for f in files:
            shutil.copy2(f, month_dir)
        append_master_table(files)     # 追加年度总案表
    except OSError as e:
        # 归档失败不报错、不中断，只记日志，等盘恢复后补归档
        log.warning(f"归档跳过：{e}")
```

```python
import zipfile, shutil

def extract_images(xlsx_path, out_dir):
    os.makedirs(out_dir, exist_ok=True)
    paths = []
    with zipfile.ZipFile(xlsx_path) as z:
        for name in z.namelist():
            if name.startswith("xl/media/"):
                dst = os.path.join(out_dir, os.path.basename(name))
                with z.open(name) as src, open(dst, "wb") as f:
                    shutil.copyfileobj(src, f)
                paths.append(dst)
    return paths
```

```bat
@echo off
chcp 65001 >nul
cd /d "%~dp0"
"C:\Python313\python.exe" "打样订单整理.py" %*
pause
```