xlsx-to-offline-html-report

Build a self-contained, offline-capable HTML report from an Excel sheet. The output is a single
.html file the user double-clicks to open — no server, no internet. Data is embedded inline so
the first load works offline; newly uploaded files are parsed in the browser (SheetJS) and stored
locally (IndexedDB) so each period stacks up and can be switched.

When to use

• User wants a website/report generated from an .xlsx (bank flows, ledgers, weekly queries,
  inventory, any tabular dump).
• Recurring cadence: "我每周都会有新的表" / "每个月上传一次" → needs upload + per-period storage.
• User wants classification/grouping beyond raw rows (e.g. 工程款/管理费/税金 bucketing, expand
  vague "内部往来" into concrete counterparties).
• Deliverable must open offline by double-click (single file, everything inlined).

Workflow

1. Read & normalize the source xlsx (Python/pandas)

• Python gotcha (this machine): the managed python 3.13 has no pandas. Use the venv:
  C:\Users\刘甜\.workbuddy\binaries\python\envs\default\Scripts\python.exe (has pandas 3.0.5).
• Chinese bank-export tables often put the header on row 2, so read with pd.read_excel(SRC, header=1).
• Normalize each row to a flat dict with stable keys (idx, d date, dir 收/支, amt float,
  pa/pn payer-account/name, ra/rn receiver-account/name, sum, rem, rf RPA流水分类,
  rl RPA往来分类, bp/op 本方/对方). Coerce amounts with float(re.sub(r'[,\s]','',s)).
• Build accounts (sorted unique account numbers) and accName (account → name, first seen wins).
• Emit a DATA dict: {id, name, file, ts, builtin:true, records, accounts, accName, cat:{}}.

2. Embed data safely

• json.dumps(DATA, ensure_ascii=False) then .replace('</script>','<\\/script>') (twice for
  case variants) before injecting into the template's <script>const DATA = __DATA__;</script>.
  This prevents a </script> inside data from terminating the block.
• Prefer const DATA = {...} over <script type="application/json"> — easier to reach from JS and
  the escaping above makes it safe.

3. Inline SheetJS for browser-side upload

• Download once: curl -L https://cdn.sheetjs.com/xlsx-0.20.3/package/dist/xlsx.full.min.js -o xlsx.full.min.js.
• Inline it into a <script>__XLSX_LIB__</script> block, first escaping </script → <\/script.
• In the app: XLSX.read(new Uint8Array(buf),{type:'array'}) then
  XLSX.utils.sheet_to_json(sheet,{header:1,defval:''}).

4. Robust upload parser (parseSheet)

• Find the header row by matching ≥3 known columns (交易日期/付款账号/收款账号/交易方向/摘要/...).
• Map columns by a candidate list (e.g. ['付款账号','付款账户'] → first hit), with includes()
  fallback so column-name variants in future files still map.
• Skip rows with neither payer nor receiver account. Coerce Excel dates (number 20000–60000) and
  serial amounts. Produce the same DATA-shape object with builtin:false and cat:{}.

5. Per-period storage (IndexedDB + localStorage fallback)

• openDB() opens indexedDB (store weeks, keyPath:'id'). If indexedDB absent, fall back
  to a localStorage JSON array.
• putWeek(w) / getWeeks() / delWeek(id) wrap the store with .transaction.
• On init: load stored weeks; for the builtin week, ALWAYS use the freshly embedded BUILTIN
  (so regenerated data is current) but merge in the stored cat map (per-record idx) so the
  user's manual edits persist. Uploaded weeks are not embedded — only in IndexedDB.
• Top bar: a week <select> to switch, an upload button, and a delete button (disable delete for
  the builtin week).

6. Layered classification (autoCat → dictReClassify)

Order matters — evaluate top-down, return on first hit:

1. Expand internal transfers when RPA往来分类 ∈ {省内调入, 总部调入, 省内调出, 归集总部}:
   produce 省内调入｜来自 {对方户名}（{对方账号}） etc. Never emit a bare "内部往来" — the
   user rejects vague labels; always name the concrete counterparty.
2. Use explicit RPA classification if RPA流水分类 is set and not 未匹配/empty.
3. Dictionary re-classify (dictReClassify) for the rest, via keyword rules over
   摘要 + 备注 + 付款户名 + 收款户名. Common buckets: 税金, 银行手续费, 保证金, 工程款, 管理费.
   - Engineering-payment strengthening for bank flows: accept if any of:
   • gc pattern (工程款/施工合同/总包/进度款/材料款/结算款/工程合同/水土保持补偿费/监理/设计…);
   • pay pattern (支付…项目 / 支付…第…期 / 支付…工程);
   • counterparty name (co: 建设/工程/建筑/施工/钢构/劳务/建材…) and 摘要 has 支付/第X期/合同.
     Exclude when text contains 税金/手续费/社保.
4. Anything still unmatched → {cat:'未识别', unrec:true} for manual review.

toOption(cat) maps the display category back to a fixed dropdown value (e.g.
省内调入｜来自 X（123） → 内部往来) so the <select> options stay stable.

7. Manual re-classification (common bug to avoid)

• Store manual overrides in ACTIVE.cat[idx] = classification; getCat checks manual first.
• Rendering bug to avoid: a summary row is an aggregate, not a raw record. Do NOT call
  getCat(summaryObj, role) on it (the record fields are undefined → JS crash → whole render
  aborts, making "不能再次分类" appear). Instead render from the summary's own cat/unrec/count/sum
  and compute a "已改" flag via summary.rows.some(r => ACTIVE.cat[r.idx]).
• Redraw with innerHTML after each change; persist via putWeek(JSON.parse(JSON.stringify(ACTIVE))).

8. Layout conventions (Chinese finance UI)

• 4 summary cards: 收入笔数/金额, 支出笔数/金额, 净流量, 未识别笔数.
• Money in two columns: 元 and 万 (÷10000). Amounts render as clean numbers the user can
  click-copy: 4,000,000 (元列) / 400 (万元列) — no ¥, no forced .00, no 万 suffix.
  Click copies the raw digits (4000000 / 400) via navigator.clipboard.writeText + a toast.
• 收入汇总 and 支出汇总 sections; each category row has 详情 → drawer expanding per-row detail.
• 收 = record where 收款账号 === acc && 方向 === 收; 支 = 付款账号 === acc && 方向 === 支
  (mirrors of 资金下拨 would double-count otherwise).

9. User-defined custom categories

• Keep CAT_OPTIONS (fixed) plus a persistent CUSTOM_CATS array in localStorage
  (wb_custom_cats). allOptions() = CAT_OPTIONS.concat(CUSTOM_CATS).
• Every category <select> is built by buildOpts(selected): allOptions + a trailing
  <option value="__add__">＋ 新增分类…</option> entry.
• setCat/setGroup intercept val === '__add__' → addCustomCat(role, idxArr): prompt for a
  name, strip "'<>& (prevents HTML-attribute breakage), push to CUSTOM_CATS, save, and apply it
  immediately to the selected rows/group.
• toOption(cat) must check allOptions().includes(cat) FIRST, otherwise a custom category
  falls through to the 未识别 mapping and the select loses its selection.
• On init, scan all stored weeks' cat maps and re-register unknown values into CUSTOM_CATS —
  recovers definitions even if localStorage was cleared.

10. Account picker: custom dropdown, not <datalist>

Native <datalist> breaks with hundreds of options (the arrow won't scroll). Build a custom
component: input + ▾ button + absolutely-positioned scrollable list. Support live filtering,
click to choose, ↑↓ + Enter keyboard navigation, Esc / outside-click to close.

11. Publish as HTTPS link (sites skill)

• Deploy the output directory with workbuddy_sites_deploy (language:"static",
  a short appName, and userAskedToPublish:true ONLY when the user asked in their latest
  message — re-deploying over an existing link needs fresh consent every turn).
• The same sandbox serves two domain aliases: <sandboxId>.app.workbuddy.host and
  <sandboxId>.app.workbuddy.link — both work, content identical, no re-deploy needed to switch.
• curl gotcha: curl against the published link returns HTTP 200 with a 0-byte body (the
  proxy dislikes CLI clients). Verify with WebFetch instead — it renders the full page.
• Browser-side data (manual cats, custom categories, uploaded weeks) lives in that browser's
  IndexedDB/localStorage — it does NOT sync across devices. Offer import/export if needed.

Bundled script

scripts/build_report.py — a runnable template: reads a source xlsx, builds DATA, inlines
SheetJS, and writes a single index.html. Copy it next to the source file and adjust the column
list + classification rules per report. Run with the venv python above.