采购清单清洗助手

概述

Clean inconsistent purchase order spreadsheets into a standard, reviewable purchase template.

Core Boundary

This skill is offline and file-based.

• Do not connect to any live purchasing or warehouse system.
• Do not read or write any production database.
• Do not place orders, approve purchases, create suppliers, or call any API.
• Do not perform financial accounting, tax calculation, or price compliance judgement.
• Only transform user-provided Excel files into reviewable outputs.

What Makes Purchase Lists Different

Unlike a flat inbound list, a purchase document is usually two layers:

• Order header block above the table: order no., supplier, contract no., dates, requesting unit.
• Detail table below it: one row per material.

The script detects the header block, backfills those order-level values onto every detail row, and records the source row of each backfilled value. Always show the user where each backfilled value came from — a wrong backfill silently contaminates every row.

If a detail column carries the same field (for example a per-row 供应商 column), the column value wins over the header block value.

Workflow

1. Inspect the uploaded workbook and locate the detail-table header row.
2. Read the order header block above it and extract order-level fields.
3. Map messy detail headers to the standard template.
4. Backfill order-level values onto every detail row.
5. Skip totals rows (合计 / 总计 / 小计) instead of reporting them as invalid.
6. Mark invalid rows instead of silently dropping them.
7. Create a cleaned .xlsx plus a markdown report.
8. Tell the user the backfill sources, and that this is a preparation file requiring human review.

Standard Output Columns

Use these columns in this order:

采购订单号, 供应商, 合同号, 提单号, 申请日期, 预计到货日期, 发票日期, 计划单位, 物资编码, 物资名称, 规格型号, 单位, 采购数量, 单价, 小计, 备注

Required per detail row: 物资名称, 单位, 采购数量. 物资编码 is not required — purchase lists from suppliers often have no internal code.

Wrong-Skill Hint

This skill does not detect what kind of list the file is. The user already chose this skill; that choice is the intent.

The only exception: if every detail row fails validation, the report adds a "可能选错了 Skill" notice naming the sibling scenario the sheet resembles. That is a hint printed for the user — never a trigger to switch templates, re-process the file, or guess the document type. Relay the notice as-is and let the user decide.

Type detection belongs to the future combined entry skill, not here.

Money Handling

• 单价 and 小计 are format-cleaned only and written as numbers.
• If both are present and 小计 differs from 采购数量 × 单价 by more than 1%, raise a reminder.
• If 小计 is missing, leave it empty and remind the user — this tool does not compute amounts.
• Tax rate, tax-inclusive pricing, and multi-currency are detected and surfaced as reminders only. They are out of scope for this version.

Script

Use scripts/clean_purchase_excel.py for deterministic cleaning:

python3 scripts/clean_purchase_excel.py input.xlsx --out-dir outputs

Expected outputs:

• cleaned_purchase_template.xlsx
• purchase_report.md
• errors.csv

References

Read references/field-mapping.md when you need the full alias table, header-block detection rules, validation rules and reminder triggers.