excel-normalizer

English | 简体中文

📊 Excel Semantic Normalizer

Any Excel, any headers, any column order → canonical fields → Canonical JSON → template export

Maps columns by meaning, not position. Hybrid retrieval, LLM adjudication, template export, and a human-feedback loop — with graceful offline fallback.

---

✨ Why this exists

Procurement / supply-chain spreadsheets in the wild use whatever headers each vendor likes: 品名 / 货物名称 / Product / Name all mean the same thing, and column order changes per file. This service parses any .xlsx, resolves each column to one of 19 canonical fields (product, price, supplier, material, …), and outputs clean, typed, validated Canonical JSON — plus it can render results back into your own Excel templates.

🚀 Quick start

Requirements: Python ≥ 3.10. API keys optional — the service runs fully offline.

git clone <repo-url>
cd excel-normalizer

pip install -r requirements.txt          # or: pip install -e ".[dev]"

cp .env.example .env                     # optional: add API keys for higher accuracy

uvicorn app.main:app --reload --port 8000

Then:

• Interactive API docs: http://127.0.0.1:8000/docs
• Health check: http://127.0.0.1:8000/health
• Live dashboard: http://127.0.0.1:8000/dashboard

One-call example

curl -X POST "http://127.0.0.1:8000/v1/table/normalize" \
  -F "file=@tests/sample_采购明细.xlsx"

import requests

resp = requests.post(
    "http://127.0.0.1:8000/v1/table/normalize",
    files={"file": open("tests/sample_采购明细.xlsx", "rb")},
)
data = resp.json()
# data["columns"] → per-column canonical field mapping + confidence
# data["records"] → normalized, typed records with _meta.issues traceability

🧭 How it works

Phase	What it does	Status
P1 Rule engine	Header exact/fuzzy alias matching + Hungarian assignment + row alignment + type coercion	✅
P2 Hybrid retrieval	Dense + BM25 + rule recall → RRF fusion → reranker (remote bge-m3 or local fallback)	✅
P3 LLM adjudication	Strict-JSON LLM verdict on low-confidence columns, with conservative overturn guard	✅
P4 Template export	Parse {{}}/${}/[[]] placeholders, reverse-bind fields, render styled xlsx back	✅
P5 Feedback loop	Human corrections → SQLite → auto-reflow into knowledge base with regression gate + auto-rollback	✅
MCP server	9 tools for Claude / Cursor / Windsurf integration	✅

Accuracy (130 unseen-header eval set, anti-cheat verified zero overlap): offline 96.2% → remote reranker 96.9% → with LLM adjudication 98.5%.

Graceful degradation: no API keys → local char-ngram + rapidfuzz. Keys configured → remote embeddings/rerank with retry-then-fallback. LLM never overrides a confident mapping unless strictly more confident.

🗂 Canonical fields (19)

product_name* · origin · price · quantity · spec · delivery_date · supplier · sku · unit · amount · material · weight · tax_rate · delivery_location · payment_method · remark · freight · vessel · warehouse

* required. Full dictionary with 500+ aliases: docs/SCHEMA_DICTIONARY.md

🔌 API overview

Group	Endpoints
Normalize	POST /v1/table/normalize · batch /v1/table/batch
Schema	GET /v1/schema · GET/POST /v1/domains (multi-domain)
Export	POST /v1/export/template/parse · /bind · /render · one-shot /v1/export
Feedback	POST /v1/feedback · /stats · /apply · /rollback
Observability	/v1/observations · /v1/drift/report · /v1/eval/trend · /v1/cache/stats
Rules	GET/POST/DELETE /v1/rules

Full examples: docs/USAGE.md (Chinese).

🧪 Testing & evaluation

pytest tests/ -v                        # 146 tests, fully offline

pytest tests/eval/test_p2_accuracy.py   # regression gate: ≥85% + anti-cheat
python -m tests.eval.run_p2_eval        # per-field accuracy report
python -m tests.eval.kb_expand_llm --dry-run --llm   # LLM alias expansion with 4-gate safety

🐳 Docker

docker compose up --build

🤝 Contributing

PRs welcome. Requirements: tests for new features, full pytest green, and knowledge-base additions must pass the anti-cheat (zero eval overlap) + no-accuracy-regression gates.

1. Fork → branch (feat/xxx) → commit → PR

📄 License

MIT

📚 More

• 完整使用方案 / Full usage guide (Chinese)
• Field dictionary snapshot
• P2 evaluation report