<div align="center">

English | **[简体中文](README.zh-CN.md)**

# 📊 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.

![demo](docs/assets/demo.gif)

</div>

---

## ✨ 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.

```bash
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

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

```python
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](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](docs/USAGE.md) (Chinese).

## 🧪 Testing & evaluation

```bash
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

```bash
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](LICENSE)

## 📚 More

- [完整使用方案 / Full usage guide (Chinese)](docs/USAGE.md)
- [Field dictionary snapshot](docs/SCHEMA_DICTIONARY.md)
- [P2 evaluation report](tests/eval/P2_EVAL_REPORT.md)