How spreadsheet formulas become TypeScript
3 min
The `xl.*` runtime gives you Excel's function surface in TypeScript. Formulas land in `lib/xl/<sheet>.ts`.
The spreadsheet-formula-generator translates cell formulas to TypeScript expressions backed by `@nxr/spreadsheet-formula-translator`. Two outputs land in your app:
• `lib/xl/runtime.ts` — the xl.* function library. Contains the Excel functions your workbook actually uses (translator emits only what's referenced — average workbook touches 15-30 functions out of Excel's ~500). Call sites read like `xl.SUM(rows.map(r => r.amount))` or `xl.VLOOKUP(targetId, products, 'price')`.
• `lib/xl/<sheet>.ts` — per-sheet module exporting each derived column as a function. A column with `=B2*1.1` becomes `export function gross(row: Row): number { return xl.MULTIPLY(row.B, 1.1); }`. The CRUD page calls these on render so derived columns recompute live as the user edits.
What works: • Numeric (SUM, AVERAGE, MIN, MAX, ROUND, ABS, …) • Logical (IF, AND, OR, NOT, IFERROR) • Lookup (VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP) • Text (LEFT, RIGHT, MID, CONCATENATE, TRIM, UPPER, LOWER) • Date (TODAY, NOW, DATE, YEAR, MONTH, DAY, DATEDIF) • Statistical (COUNT, COUNTA, COUNTIF, SUMIF, AVERAGEIF)
What needs manual review: • Array formulas (CTRL+SHIFT+ENTER) — translated but worth verifying the result matches Excel's array semantics. • Volatile functions (RAND, NOW) — preserved literally; expect different return values per render. • External references (=[other.xlsx]Sheet!A1) — flagged with a TODO comment; the translator can't resolve them. • Custom UDFs from VBA — emitted as throw-stubs paired with the VBA-port guide.
Live recipes need the desktop
This article is a static preview. The in-app Help sidecar inside Avery NXR can fire each step against your live project — install the desktop to use it interactively.