# Introduction typed-xlsx is a TypeScript library for Excel reports. You describe the report once as a schema: columns, formulas, styles, summary rows. Then you pass it rows and get an `.xlsx` file. The same schema works for a 20-row export and for a million rows streamed in batches. The schema is typed against your row type, so a misspelled field, a formula that points at a missing column, or a selection with an unknown column ID fails in your editor instead of in someone's spreadsheet. ::flow-figure --- steps: - cap: Schema code: createExcelSchema() note: columns, formulas, styles, summaries - cap: Table code: .table(id, { rows }) note: rows, context, column selection - cap: Workbook code: createWorkbook() note: sheets, charts, pivot tables - cap: Output code: toBuffer() · writeToFile() note: buffer, file, Node or Web stream accent: true --- :: ## A report in one schema ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Invoice = { id: string; customer: string; qty: number; unitPrice: number; status: "paid" | "pending" | "overdue"; }; const invoices = createExcelSchema() .column("id", { header: "Invoice #", accessor: "id" }) .column("customer", { header: "Customer", accessor: "customer" }) .column("qty", { header: "Qty", accessor: "qty" }) .column("unitPrice", { header: "Unit price", accessor: "unitPrice", style: { numFmt: "$#,##0.00" }, }) .column("total", { header: "Total", formula: ({ refs, fx }) => fx.round(refs.column("qty").mul(refs.column("unitPrice")), 2), style: { numFmt: "$#,##0.00" }, summary: (s) => [s.formula("sum")], }) .column("status", { header: "Status", type: "badge", accessor: "status", variants: { paid: { fill: { color: { rgb: "DCFCE7" } }, font: { color: { rgb: "166534" } } }, pending: { fill: { color: { rgb: "FEF3C7" } }, font: { color: { rgb: "92400E" } }, }, overdue: { fill: { color: { rgb: "FEE2E2" } }, font: { color: { rgb: "991B1B" } }, }, }, }) .build(); const workbook = createWorkbook(); workbook.sheet("Invoices", { freezePane: { rows: 1 } }).table("invoices", { rows: [{ id: "INV-001", customer: "Acme", qty: 10, unitPrice: 299, status: "paid" }], schema: invoices, }); await workbook.writeToFile("./invoices.xlsx"); ``` `total` is written as a real Excel formula, `=ROUND((C2*D2),2)`, so it recalculates when someone edits the sheet. The summary row adds a `SUM` below the data, and `status` renders as a colored badge. Leave out one of the three statuses in `variants` and TypeScript flags it. The formula is a choice, not a requirement: the same column can be computed in plain TypeScript instead. ## Computed in TypeScript or live in Excel Every computed value, a total, a highlight or a summary, can take one of two paths. Both are fully typed, and you can mix them in the same schema, column by column. **In TypeScript, at export time.** Accessor functions, `transform`, style callbacks and summary reducers receive your typed row and can use anything JavaScript can: dates, lookups, your own helpers, other libraries. The file gets the results as plain values and styles. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Line = { qty: number; unitPrice: number }; const lines = createExcelSchema() .column("qty", { header: "Qty", accessor: "qty" }) .column("unitPrice", { header: "Unit price", accessor: "unitPrice" }) .column("total", { header: "Total", accessor: (row) => row.qty * row.unitPrice, style: ({ row }) => ({ font: { bold: row.qty * row.unitPrice > 5000 } }), summary: (s) => [ s.cell({ init: () => 0, step: (sum, row) => sum + row.qty * row.unitPrice, finalize: (sum) => sum, }), ], }) .build(); ``` **Live in Excel, with the typed formula DSL.** `formula`, `summary.formula()` and `conditionalStyle` write real Excel formulas and conditional formats. When someone edits a quantity, the total, the summary and the highlight update in Excel. TypeScript still checks every reference: `refs.column()` only accepts columns declared earlier. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Line = { qty: number; unitPrice: number }; const lines = createExcelSchema() .column("qty", { header: "Qty", accessor: "qty" }) .column("unitPrice", { header: "Unit price", accessor: "unitPrice" }) .column("total", { header: "Total", formula: ({ refs }) => refs.column("qty").mul(refs.column("unitPrice")), conditionalStyle: (c) => c.when(({ refs }) => refs.column("total").gt(5000), { font: { bold: true } }), summary: (s) => [s.formula("sum")], }) .build(); ``` ::sheet-figure --- align: - r - r - r - l columns: - Qty - Unit price - Total (TypeScript) - Total (Excel) marks: - range: C2:C3 label: computed while the file is written and stored as a value - range: D2:D3 label: "a formula: Excel recalculates it when Qty or Unit price changes" rows: - - 10 - 299 - 2990 - =(A2*B2) - - 8 - 850 - 6800 - =(A3*B3) widths: - 48 - 84 - 150 - 130 --- :: | | In TypeScript | Live in Excel | | --------------------------- | ------------------------------------------------------------------------ | -------------------------------------------------------------------- | | You write | accessor functions, `transform`, `style: ({ row }) =>`, `summary.cell()` | `formula`, `summary.formula()`, `conditionalStyle`, validation rules | | The file contains | the computed values and styles | formulas and conditional formats | | When someone edits the file | nothing else changes | totals, formulas and highlights recalculate | | You can use | anything in JavaScript | what Excel can compute | | Best for | exports people read, and logic Excel can't express | reports people edit, filter or reuse as a model | If nobody will edit the numbers, plain TypeScript is the simpler path, and you never need the formula DSL. Reach for it when the workbook has to stay live. See [Formula Columns](https://typed-xlsx.vercel.app/formulas/formula-columns), [Dynamic Styles](https://typed-xlsx.vercel.app/styling/dynamic-styles), [Conditional Styles](https://typed-xlsx.vercel.app/styling/conditional-styles) and [Summary Rows](https://typed-xlsx.vercel.app/columns/summary-rows). ## What TypeScript checks - **Accessors.** `"customer"` must be a key of `Invoice`, and dot paths like `"address.city"` are checked all the way down. - **Formula references.** `refs.column("qty")` only accepts columns declared earlier in the chain, so a formula can't point at a column that doesn't exist yet. - **Schema context.** A schema declared with `createExcelSchema()` requires every table to pass that `context`. - **Selection.** `select: { include: [...] }` only accepts the schema's column, group and dynamic IDs. - **Charts and pivots.** Marks and pivot fields only accept columns of the table they read, and only number columns can be plotted or summed. ## What you can build with it | Area | Features | | --------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Layout | [grouped headers](https://typed-xlsx.vercel.app/columns/column-groups), [runtime columns](https://typed-xlsx.vercel.app/columns/dynamic-columns), [sub-row expansion](https://typed-xlsx.vercel.app/core-concepts/row-model), [several tables per sheet](https://typed-xlsx.vercel.app/workbook/building-workbooks), [freeze panes](https://typed-xlsx.vercel.app/workbook/sheet-options) | | Computed values | in TypeScript: [accessor functions](https://typed-xlsx.vercel.app/columns/defining-columns), [row-based styles](https://typed-xlsx.vercel.app/styling/dynamic-styles), [summary reducers](https://typed-xlsx.vercel.app/columns/summary-rows); live in Excel: [typed formula DSL](https://typed-xlsx.vercel.app/formulas/formula-columns), [summary formulas](https://typed-xlsx.vercel.app/columns/summary-rows), [native conditional formats](https://typed-xlsx.vercel.app/styling/conditional-styles) | | Cell types | [badges and checkboxes](https://typed-xlsx.vercel.app/columns/badges-and-checkboxes), [hyperlinks](https://typed-xlsx.vercel.app/columns/hyperlinks), [sparklines](https://typed-xlsx.vercel.app/columns/sparklines), [images](https://typed-xlsx.vercel.app/columns/images), [dropdowns and validation](https://typed-xlsx.vercel.app/columns/data-validation) | | Excel | [native tables](https://typed-xlsx.vercel.app/excel-tables/table-mode) with filters and a totals row, [themes](https://typed-xlsx.vercel.app/styling/themes), [sheet and cell protection](https://typed-xlsx.vercel.app/workbook/protection) | | Analysis | [native charts](https://typed-xlsx.vercel.app/charts/overview) from your tables, [pivot tables and pivot charts](https://typed-xlsx.vercel.app/charts/pivot-tables) Excel keeps in sync | | Output | [buffered or streamed](https://typed-xlsx.vercel.app/core-concepts/execution-model) with the same schema, [file, buffer, Node and Web streams](https://typed-xlsx.vercel.app/streaming/output-targets) | No SheetJS, no ExcelJS: typed-xlsx writes the OOXML parts and the ZIP container itself, with zero runtime dependencies. ## Buffered or streamed `createWorkbook()` takes all rows at once and is the simplest API. `createWorkbookStream()` takes rows in batches with `table.commit()`, for exports that come from a database cursor or are too big to hold in memory. Both accept the same schemas. See [Execution Model](https://typed-xlsx.vercel.app/core-concepts/execution-model) to choose, and [Performance](https://typed-xlsx.vercel.app/performance/benchmarks) for measured numbers. ## When to use something else typed-xlsx writes reports. It does not read `.xlsx` files, convert between formats, or edit existing workbooks. For those, use a general spreadsheet library such as SheetJS or ExcelJS. The [Comparison](https://typed-xlsx.vercel.app/getting-started/comparison) page goes through the differences. ## Next steps - [Installation](https://typed-xlsx.vercel.app/getting-started/installation) - [Quick Start](https://typed-xlsx.vercel.app/getting-started/quick-start): a complete report, buffered and streamed - [Schema Modes](https://typed-xlsx.vercel.app/core-concepts/schema-modes): report layout or native Excel table # Installation ::code-group ```bash [pnpm] pnpm add typed-xlsx ``` ```bash [npm] npm install typed-xlsx ``` ```bash [yarn] yarn add typed-xlsx ``` ```bash [bun] bun add typed-xlsx ``` :: typed-xlsx has no runtime dependencies and ships as ESM only. ## The three functions you start from | Function | Use it to | | ------------------------ | ----------------------------------------------------------------- | | `createExcelSchema()` | describe the report: columns, formulas, styles, summaries | | `createWorkbook()` | build a workbook from rows you already have in memory | | `createWorkbookStream()` | build a workbook by committing rows in batches, for large exports | ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type User = { firstName: string; email: string }; const schema = createExcelSchema() .column("firstName", { header: "First name", accessor: "firstName" }) .column("email", { header: "Email", accessor: "email" }) .build(); const workbook = createWorkbook(); workbook.sheet("Users").table("users", { rows: [{ firstName: "Ada", email: "ada@example.com" }], schema, }); const bytes = workbook.toUint8Array(); ``` ## Runtimes Schemas, both builders, `toUint8Array()` and the streaming builder's Web outputs (`toReadableStream()`, `pipeTo()`) run in Node.js, Bun, Deno and edge runtimes such as Cloudflare Workers. A few outputs depend on Node APIs and load them only when called: | Needs | Feature | | --------------------- | ----------------------------------------------------------------------------------------------------------- | | Node `Buffer` | `toBuffer()` | | Node streams | `pipeToNode()`, `toNodeReadable()` | | a writable filesystem | `writeToFile()`, and the streaming builder's default temp storage (use `tempStorage: "memory"` without one) | The buffered builder (`createWorkbook()`) compresses the file with Node's `zlib`, which Node.js, Bun and Deno provide. In runtimes that don't expose it, such as Cloudflare Workers or a browser, the workbook still opens in Excel but its parts are stored uncompressed, so the file is much larger (a 20,000-row, three-column test went from 294 KB to 3.1 MB). The streaming builder compresses with the Web `CompressionStream` instead, where the runtime has one. ## Next steps - [Quick Start](https://typed-xlsx.vercel.app/getting-started/quick-start): a complete report, buffered and streamed - [Schema Modes](https://typed-xlsx.vercel.app/core-concepts/schema-modes): report layout or native Excel table # Quick Start This guide builds one invoice report and exports it twice: once from an array, once streamed in batches. The schema doesn't change between the two. ## 1. Describe the rows ```ts twoslash type Invoice = { invoiceId: string; customer: string; qty: number; unitPrice: number; taxRate: number; status: "paid" | "pending" | "overdue"; }; ``` ## 2. Build the schema Each `.column()` adds a column. `accessor` reads a field, `formula` writes an Excel formula, `summary` adds a row below the data, and `type: "badge"` renders a status as a colored pill. ```ts twoslash type Invoice = { invoiceId: string; customer: string; qty: number; unitPrice: number; taxRate: number; status: "paid" | "pending" | "overdue"; }; // ---cut--- import { createExcelSchema } from "typed-xlsx"; const money = { numFmt: "$#,##0.00" }; export const invoiceSchema = createExcelSchema() .column("invoiceId", { header: "Invoice #", accessor: "invoiceId" }) .column("customer", { header: "Customer", accessor: "customer" }) .column("qty", { header: "Qty", accessor: "qty" }) .column("unitPrice", { header: "Unit price", accessor: "unitPrice", style: money }) .column("subtotal", { header: "Subtotal", formula: ({ refs, fx }) => fx.round(refs.column("qty").mul(refs.column("unitPrice")), 2), style: money, summary: (s) => [s.formula("sum")], }) .column("taxRate", { header: "Tax", accessor: "taxRate", style: { numFmt: "0%" } }) .column("total", { header: "Total", formula: ({ refs, fx }) => fx.round( refs.column("subtotal").mul(fx.literal(1).add(refs.column("taxRate"))), 2, ), style: money, summary: (s) => [s.formula("sum")], }) .column("status", { header: "Status", type: "badge", accessor: "status", variants: { paid: { style: { fill: { color: { rgb: "DCFCE7" } }, font: { color: { rgb: "166534" } }, }, }, pending: { style: { fill: { color: { rgb: "FEF3C7" } }, font: { color: { rgb: "92400E" } }, }, }, overdue: { style: { fill: { color: { rgb: "FEE2E2" } }, font: { color: { rgb: "991B1B" } }, }, }, }, }) .build(); ``` Try a typo: `refs.column("subtota")` or `accessor: "unitprice"` is a type error, because both are checked against the schema and the `Invoice` type. ## 3. Export it from an array ```ts twoslash type Invoice = { invoiceId: string; customer: string; qty: number; unitPrice: number; taxRate: number; status: "paid" | "pending" | "overdue"; }; import { createExcelSchema } from "typed-xlsx"; const invoiceSchema = createExcelSchema() .column("invoiceId", { accessor: "invoiceId" }) .column("status", { accessor: "status" }) .build(); // ---cut--- import { createWorkbook } from "typed-xlsx"; const rows: Invoice[] = [ { invoiceId: "INV-001", customer: "Acme Corp", qty: 10, unitPrice: 299, taxRate: 0.2, status: "paid", }, { invoiceId: "INV-002", customer: "Globex Inc", qty: 5, unitPrice: 850, taxRate: 0.2, status: "pending", }, { invoiceId: "INV-003", customer: "Initech", qty: 2, unitPrice: 1200, taxRate: 0.2, status: "overdue", }, ]; const workbook = createWorkbook(); workbook.sheet("Invoices", { freezePane: { rows: 2 } }).table("invoices", { title: "Invoices, Q1 2025", rows, schema: invoiceSchema, }); await workbook.writeToFile("./invoices.xlsx"); // or: workbook.toBuffer() for an HTTP response or an S3 upload ``` This is the sheet it writes: ::sheet-figure --- align: - l - l - r - r - l - r - l - c columns: - "Invoice #" - Customer - Qty - Unit price - Subtotal - Tax - Total - Status marks: - range: E3:E5 label: subtotal is a live formula, recalculated when someone edits Qty or Unit price - range: E6:G6 label: summary rows add SUM formulas under the data rows: - - INV-001 - Acme Corp - 10 - 299 - =ROUND((C3*D3),2) - 20% - =ROUND((E3*(1+F3)),2) - paid - - INV-002 - Globex Inc - 5 - 850 - =ROUND((C4*D4),2) - 20% - =ROUND((E4*(1+F4)),2) - pending - - INV-003 - Initech - 2 - 1200 - =ROUND((C5*D5),2) - 20% - =ROUND((E5*(1+F5)),2) - overdue summary: - null - null - null - null - =SUM(E3:E5) - null - =SUM(G3:G5) - null widths: - 74 - 84 - 40 - 80 - 134 - 46 - 162 - 66 sheet: Invoices title: Invoices, Q1 2025 --- :: Rows 1 and 2 (title and headers) are frozen, so they stay visible while scrolling. ## 4. Stream it in batches When the rows come from a database cursor or a paginated API, use `createWorkbookStream()` with the same schema and commit pages as they arrive: ```ts twoslash type Invoice = { invoiceId: string; customer: string; qty: number; unitPrice: number; taxRate: number; status: "paid" | "pending" | "overdue"; }; import { createExcelSchema } from "typed-xlsx"; const invoiceSchema = createExcelSchema() .column("invoiceId", { accessor: "invoiceId" }) .column("status", { accessor: "status" }) .build(); declare function fetchInvoicePages(): AsyncIterable; // ---cut--- import { createWorkbookStream } from "typed-xlsx"; const workbook = createWorkbookStream(); const table = await workbook .sheet("Invoices", { freezePane: { rows: 2 } }) .table("invoices", { title: "Invoices, Q1 2025", schema: invoiceSchema }); for await (const page of fetchInvoicePages()) { await table.commit({ rows: page }); } await workbook.writeToFile("./invoices.xlsx"); ``` The file is the same; the summary row is computed across every committed batch. Streaming supports every schema feature, including formulas, summary rows, sub-row expansion and excel-table mode. ## Next steps - [Defining Columns](https://typed-xlsx.vercel.app/columns/defining-columns): accessors, transforms, widths - [Formula Columns](https://typed-xlsx.vercel.app/formulas/formula-columns): the formula DSL - [Table Mode](https://typed-xlsx.vercel.app/excel-tables/table-mode): native Excel tables with filters - [Large Exports](https://typed-xlsx.vercel.app/performance/large-exports): when and how to stream # Comparison SheetJS, ExcelJS and typed-xlsx can all write `.xlsx` files. They differ in what you write. With SheetJS or ExcelJS you place values into cells. With typed-xlsx you describe a report as a typed schema, and the library places the cells. That makes typed-xlsx narrower, it only writes reports, and much stricter about them. ## At a glance | | typed-xlsx | SheetJS (Community) | ExcelJS | | ----------------------- | ------------------------------------------- | --------------------------- | ------------------------------ | | You write | a schema typed against your rows | arrays and cell objects | workbook, row and cell objects | | Reads `.xlsx` | no | yes | yes | | Other formats | no | CSV, XLS, ODS and many more | CSV | | Cell styles on write | yes | in SheetJS Pro | yes | | Formulas | typed DSL, references checked by TypeScript | strings | strings | | Summary and totals rows | declared per column | written by hand | written by hand | | Native Excel tables | yes | no | yes | | Native charts | typed marks over your tables | no | no | | Pivot tables | typed fields, with pivot charts | no | basic | | Streaming write | same schema, batch commits | not for `.xlsx` | separate streaming writer | | Runtime dependencies | none | none | several | ## Where typed-xlsx is the better fit **The report is the contract.** Columns, headers, formulas, styles and summaries live in one schema typed against your row type. Renaming a field breaks the build, not the export. **Formulas stay correct when columns move.** You write `refs.column("qty").mul(refs.column("unitPrice"))`; typed-xlsx writes `=(C2*D2)`, or a structured reference inside an Excel table. Referencing a column that doesn't exist, or one declared later, is a type error. **One schema, many exports.** The same schema serves different row sets, a different column selection per audience, a different theme, or a native Excel table, without copying column definitions. **Big exports use the same code.** `createWorkbookStream()` takes the same schema as `createWorkbook()`. See [Benchmarks](https://typed-xlsx.vercel.app/performance/benchmarks) for measured throughput. **Report features are built in.** Grouped headers, sub-row expansion, summary rows, badges, checkboxes, sparklines, images, hyperlinks, validation dropdowns, conditional formats and protection are schema options rather than cell-by-cell code. [Native charts and pivot tables](https://typed-xlsx.vercel.app/charts/overview) are declared from the tables you already have, with their columns checked by TypeScript. ## Where SheetJS or ExcelJS is the better fit - **Reading files.** typed-xlsx only writes. To parse uploads or import spreadsheets, use SheetJS or ExcelJS. - **Other formats.** SheetJS reads and writes far more formats than `.xlsx`. - **Editing an existing workbook.** Opening a file, changing it and saving it back is outside typed-xlsx's scope. - **Free-form sheets.** When the sheet isn't a table of records (a form, a dashboard laid out cell by cell, notes and rich text), a cell API is the more direct tool. # Schema Modes Every schema has a mode, and it decides what the data becomes in Excel: - **report** (the default) writes a laid-out range: a title, grouped headers, sub-row expansion, summary rows below the data - **excel-table** writes a native Excel table: filter buttons, banded rows, a totals row, structured references ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Order = { product: string; qty: number; price: number }; const report = createExcelSchema(); const table = createExcelSchema({ mode: "excel-table" }); ``` The column API is the same in both modes. Here is one schema written both ways: ::sheet-figure --- columns: - Product - Qty - Price - Total marks: - range: D3:D5 label: formulas use A1 references - range: A6:D6 label: a summary row, written by typed-xlsx below the data rows: - - Desk - 4 - 320 - =(B3*C3) - - Chair - 10 - 140 - =(B4*C4) - - Lamp - 6 - 45 - =(B5*C5) summary: - Total - =SUM(B3:B5) - null - =SUM(D3:D5) widths: - 90 - 100 - 70 - 140 sheet: Report mode title: Orders --- :: ::sheet-figure --- columns: - Product - Qty - Price - Total marks: - range: A1:D1 label: "a native table: filter and sort buttons on every header" - range: D2:D4 label: structured references, as Excel displays them - range: A5:D5 label: Excel's totals row, which recalculates when the table is filtered rows: - - Desk - 4 - 320 - =([@Qty]*[@Price]) - - Chair - 10 - 140 - =([@Qty]*[@Price]) - - Lamp - 6 - 45 - =([@Qty]*[@Price]) summary: - Total - =SUBTOTAL(109,[Qty]) - null - =SUBTOTAL(109,[Total]) widths: - 90 - 170 - 70 - 170 sheet: Excel table mode variant: table --- :: ## Compare the modes | | report | excel-table | | ------------------- | ------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------- | | Output | a plain range | a native Excel table object | | Rows below the data | [summary rows](https://typed-xlsx.vercel.app/columns/summary-rows), any number | one [totals row](https://typed-xlsx.vercel.app/excel-tables/totals-row) | | Sub-row expansion | yes | no | | Grouped headers | yes | groups are allowed, headers stay one row | | Filters | opt in with `autoFilter` | on by default | | Formula references | A1 (`=(B3*C3)`) | structured (`[@Qty]`) | | Look | cell styles and [themes](https://typed-xlsx.vercel.app/styling/themes) | an Excel [table style](https://typed-xlsx.vercel.app/excel-tables/table-styles), plus cell styles | | Streaming | yes | yes | ## Which one to pick Choose **excel-table** when people will sort, filter or pivot the data in Excel, or feed it to Power Query. The table keeps working as they add rows. Choose **report** when the sheet is something people read: it needs a title, several summary lines, or line items expanded under their parent record. ## Same formulas, different output You write formulas once with `refs.column()`. The mode decides how they are written into the file: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Order = { product: string; qty: number; price: number }; const reportSchema = createExcelSchema() .column("qty", { header: "Qty", accessor: "qty" }) .column("price", { header: "Price", accessor: "price" }) .column("total", { header: "Total", formula: ({ refs }) => refs.column("qty").mul(refs.column("price")), }) .build(); const tableSchema = createExcelSchema({ mode: "excel-table" }) .column("qty", { header: "Qty", accessor: "qty" }) .column("price", { header: "Price", accessor: "price" }) .column("total", { header: "Total", formula: ({ refs }) => refs.column("qty").mul(refs.column("price")), }) .build(); ``` In report mode the cell holds `=(A2*B2)`. In excel-table mode it holds a structured reference to the same row, which Excel displays as `=([@Qty]*[@Price])` and keeps correct when someone sorts or inserts rows. ## Mode and builder are separate choices The mode decides what the data looks like. The builder, `createWorkbook()` or `createWorkbookStream()`, decides how rows reach the file. All four combinations work. See [Execution Model](https://typed-xlsx.vercel.app/core-concepts/execution-model). # Row Model In report mode, one object from your `rows` array can produce several worksheet rows. Every formula API in typed-xlsx is built around that split: - a **logical row** is one object from `rows` - a **physical row** is one row of the worksheet `refs.column()` and `column.cells()` work on physical rows. `row.series()` and `column.rows()` work on logical rows. The rest of this page shows what each one writes into the file. ## One input row, several worksheet rows When an accessor returns an array, the logical row expands into one physical row per item. Columns with a single value are merged across the expanded rows. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ customer: string; monthlyAmounts: number[] }>() .column("customer", { header: "Customer", accessor: "customer" }) .column("monthlyAmount", { header: "Monthly amount", accessor: (row) => row.monthlyAmounts, }) .build(); ``` ::sheet-figure --- columns: - Customer - Monthly amount marks: - range: A2:A4 label: customer has one value, so it is merged across the three rows - range: B2:B4 label: monthlyAmounts has three values, one per physical row merges: - A2:A4 rows: - - Acme - 100 - - null - 120 - - null - 140 input: |- { customer: "Acme", monthlyAmounts: [100, 120, 140], } inputLabel: One logical row --- :: Sub-row expansion is report-mode only. In [excel-table mode](https://typed-xlsx.vercel.app/excel-tables/table-mode) every logical row stays one physical row. ## `refs.column()` points at the current physical row `refs.column(id)` references the cell of that column on the same worksheet row as the formula. On an expanded row, each physical row gets its own formula. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ qty: number[]; unitPrice: number[] }>() .column("qty", { header: "Qty", accessor: (row) => row.qty }) .column("unitPrice", { header: "Unit price", accessor: (row) => row.unitPrice }) .column("lineTotal", { header: "Line total", formula: ({ refs }) => refs.column("qty").mul(refs.column("unitPrice")), expansion: "expand", }) .build(); ``` ::sheet-figure --- columns: - Qty - Unit price - Line total marks: - range: C2:C4 label: one formula per physical row, each pointing at its own row rows: - - 2 - 50 - =(A2*B2) - - 3 - 40 - =(A3*B3) - - 1 - 80 - =(A4*B4) --- :: Use it for line totals, per-line margins, and any comparison that should follow each worksheet row. ## `row.series()` points at the whole logical row `row.series(id)` references every physical cell the current logical row produced for that column. It is the tool for one result per source record. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ customer: string; monthlyAmounts: number[] }>() .column("customer", { header: "Customer", accessor: "customer" }) .column("monthlyAmount", { header: "Monthly amount", accessor: (row) => row.monthlyAmounts, }) .column("rowAverage", { header: "Average", formula: ({ row, fx }) => fx.round(row.series("monthlyAmount").average(), 2), expansion: "single", }) .build(); ``` ::sheet-figure --- columns: - Customer - Monthly amount - Average marks: - range: B2:B4 label: row.series("monthlyAmount") covers the logical row's span - range: C2:C4 label: 'one result for the logical row, merged (expansion: "single")' merges: - A2:A4 - C2:C4 rows: - - Acme - 100 - =ROUND(AVERAGE(B2:B4),2) - - null - 120 - null - - null - 140 - null --- :: `row.series()` exposes `.sum()`, `.average()`, `.min()` and `.max()`. ## Both compose with the same operators `refs.column()` and `row.series()` return the same kind of formula operand, so `.mul()`, `.sub()`, `fx.round()` and the rest work on both. What changes is the cells they point at. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ qty: number[]; unitPrice: number[]; discountPct: number; }>() .column("qty", { header: "Qty", accessor: (row) => row.qty }) .column("unitPrice", { header: "Unit price", accessor: (row) => row.unitPrice }) .column("discountPct", { header: "Discount", accessor: "discountPct" }) .column("lineNet", { header: "Line net", formula: ({ refs, fx }) => refs .column("qty") .mul(refs.column("unitPrice")) .mul(fx.literal(1).sub(refs.column("discountPct"))), expansion: "expand", }) .column("orderAverage", { header: "Order average", formula: ({ row, fx }) => fx.round(row.series("lineNet").average(), 2), expansion: "single", }) .build(); ``` ::sheet-figure --- columns: - Qty - Unit price - Discount - Line net - Order average marks: - range: C2:C4 label: a merged value is referenced through its first row, C2 - range: D2:D4 label: computed per physical row - range: E2:E4 label: aggregates those results once per logical row merges: - C2:C4 - E2:E4 rows: - - 2 - 50 - 0.1 - =((A2*B2)*(1-C2)) - =ROUND(AVERAGE(D2:D4),2) - - 3 - 40 - null - =((A3*B3)*(1-C2)) - null - - 1 - 80 - null - =((A4*B4)*(1-C2)) - null widths: - 56 - 84 - 80 - 170 - 200 --- :: ## `expansion` decides how many cells get the result The formula decides what to compute. `expansion` decides where it is written on an expanded row: | Value | Writes | | ---------- | --------------------------------------------------------------------------------------------------------------------- | | `"single"` | once, on the first physical row, merged across the rest | | `"expand"` | on every physical row | | `"auto"` | the default: `single` when the formula reads a whole logical row (`row.series`), `expand` when it reads `refs.column` | ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ amounts: number[] }>() .column("amount", { header: "Amount", accessor: (row) => row.amounts }) .column("averageOnce", { header: "Average once", formula: ({ row }) => row.series("amount").average(), expansion: "single", }) .column("averageRepeated", { header: "Average repeated", formula: ({ row }) => row.series("amount").average(), expansion: "expand", }) .build(); ``` ::sheet-figure --- columns: - Amount - Average once - Average repeated marks: - range: B2:B4 label: single - range: C2:C4 label: expand merges: - B2:B4 rows: - - 100 - =AVERAGE(A2:A4) - =AVERAGE(A2:A4) - - 120 - null - =AVERAGE(A2:A4) - - 140 - null - =AVERAGE(A2:A4) --- :: ## Summaries make the same choice Summary formulas below the table pick between physical cells and logical rows too: - `column.cells()` aggregates every worksheet cell in the column - `column.rows()` resolves one value per logical row first, then aggregates those With two customers where Acme expanded into three rows, the two averages differ: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ customer: string; monthlyAmounts: number[] }>() .column("customer", { header: "Customer", accessor: "customer", summary: (summary) => [summary.label("Cells avg"), summary.label("Rows avg")], }) .column("monthlyAmount", { header: "Monthly amount", accessor: (row) => row.monthlyAmounts, summary: (summary) => [ summary.formula(({ column }) => column.cells().average()), summary.formula(({ column }) => column.rows().average((row) => row.cells().average()), ), ], }) .build(); ``` ::sheet-figure --- columns: - Customer - Monthly amount marks: - range: B6 label: "column.cells(): (100 + 120 + 140 + 300) / 4 = 165" - range: B7 label: "column.rows(): (120 + 300) / 2 = 210, each customer counts once" merges: - A2:A4 rows: - - Acme - 100 - - null - 120 - - null - 140 - - Globex - 300 summaries: - - Cells avg - =AVERAGE(B2:B5) - - Rows avg - =AVERAGE(AVERAGE(B2:B4),AVERAGE(B5:B5)) widths: - 110 - 330 --- :: See [Summary Rows](https://typed-xlsx.vercel.app/columns/summary-rows) for labels, reducers and styling. ## Which one to use | You want | Use | | ------------------------------------------------------------- | --------------------- | | a value per worksheet row (line totals, per-line checks) | `refs.column()` | | one value per source record (order totals, customer averages) | `row.series()` | | that value written once and merged | `expansion: "single"` | | that value repeated on every expanded row | `expansion: "expand"` | | a summary over every worksheet cell | `column.cells()` | | a summary where each source record counts once | `column.rows()` | Next: [Formula Columns](https://typed-xlsx.vercel.app/formulas/formula-columns) covers the full DSL, and [Scope & References](https://typed-xlsx.vercel.app/formulas/scope-and-references) covers which columns a formula may reference. # Schema Context Some inputs aren't part of a row: the regions to show, the reporting period, the currency, the locale, a feature flag. Declare them as the schema's context, and every table built from that schema must pass them in. ```ts createExcelSchema(); ``` The same `ctx` object reaches every callback that accepts it: | Where | Example | | --------------------------------------------------------------------------------------------- | ----------------------------------------------------- | | `dynamic()` builders | one column per region in `ctx.regions` | | `condition` | include a column only when `ctx.showFinance` is true | | accessors, `transform`, `format` | convert with `ctx.fxRate`, format with `ctx.currency` | | `style`, badge `label` callbacks | highlight `ctx.featuredRegion` | | `hyperlink` | build URLs from `ctx.baseUrl` | | `formula` | reference a rate passed at export time | | titles, headers, group headers, summary and totals labels, validation messages, link tooltips | translate with `ctx.t(key)` | ## Declare it and use it ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Deal = { amount: number; region: "EMEA" | "AMER" | "APAC"; }; type SchemaContext = { featuredRegion: Deal["region"]; currencySymbol: string; }; const schema = createExcelSchema() .column("regionOnly", { accessor: "region", condition: ({ ctx }) => ctx.featuredRegion === "EMEA", }) .column("amount", { header: ({ ctx }) => `${ctx.currencySymbol} amount`, accessor: "amount", format: ({ ctx }) => `${ctx.currencySymbol}#,##0.00`, style: ({ row, ctx }) => ({ font: { bold: row.region === ctx.featuredRegion, }, }), }) .build(); ``` Text callbacks run when the table receives its `context`, so one schema can serve several locales: ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Row = { status: string }; type Context = { t(key: string): string }; const schema = createExcelSchema() .column("status", { accessor: "status", header: ({ ctx }) => ctx.t("columns.status"), summary: (summary) => [summary.label(({ ctx }) => ctx.t("totals.label"))], }) .build(); createWorkbook() .sheet("Exports") .table("statuses", { context: { t: (key) => key }, rows: [], schema, title: ({ ctx }) => ctx.t("reports.statuses.title"), }); ``` ## Every table must pass the context If a schema declares a context type, every table built from it must provide `context`, even when `select` leaves out the columns that use it. The context belongs to the schema as a whole, so the check doesn't depend on the selection: ```ts twoslash // @errors: 2345 import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Row = { name: string; orgs: number[] }; const schema = createExcelSchema() .column("name", { accessor: "name" }) .dynamic("memberships", (builder, { ctx }) => { for (const id of ctx.orgIds) { builder.column(`org-${id}`, { accessor: (row) => row.orgs.includes(id) }); } }) .build(); createWorkbook() .sheet("Sheet") .table("sheet", { rows: [], schema, select: { exclude: ["memberships"] }, }); ``` ## Get the context type from a schema `SchemaContextOf` extracts the context a built schema requires, for example to type a function that prepares it: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; import type { SchemaContextOf } from "typed-xlsx"; const schema = createExcelSchema<{ name: string }, { locale: string }>() .column("name", { accessor: "name" }) .build(); type RequiredContext = SchemaContextOf; // ^? ``` # Execution Model typed-xlsx has two workbook builders. They accept the same schemas and support the same features. They differ in how rows reach the file. ## `createWorkbook()`: all rows at once You pass `rows` to `.table()`, then call an output method. The builder plans every row, writes the sheet XML and zips the workbook in one pass. ::flow-figure --- steps: - cap: Rows code: "rows: T[]" note: the whole array, in memory - cap: Plan code: .table(id, { rows }) note: expansion, merges, summaries - cap: Serialize code: sheet XML note: built in memory - cap: Output code: toBuffer() · writeToFile() note: zipped in one go accent: true --- :: ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Invoice = { invoiceId: string; customer: string; amount: number }; const schema = createExcelSchema() .column("invoiceId", { header: "Invoice #", accessor: "invoiceId" }) .column("customer", { header: "Customer", accessor: "customer" }) .column("amount", { header: "Amount", accessor: "amount", style: { numFmt: "$#,##0.00" }, }) .build(); const workbook = createWorkbook(); workbook.sheet("Invoices").table("invoices", { schema, rows: [{ invoiceId: "INV-001", customer: "Acme Corp", amount: 4200 }], }); const bytes = workbook.toUint8Array(); ``` Use it when the rows are already in memory and the report is a normal size. It has the simplest API: sheets and tables chain synchronously, and there is no commit loop. ## `createWorkbookStream()`: rows in batches You open a table, then `commit()` pages of rows as they arrive. Each batch is serialized and appended to a temporary spool, on disk by default. Finalizing streams the spool into the ZIP. ::flow-figure --- steps: - cap: Source code: pages() note: a cursor or paginated API, one page at a time - cap: Commit code: commit({ rows }) note: serialized right away - cap: Spool code: 'tempStorage: "file"' note: XML appended to a temp file - cap: Output code: writeToFile() · pipeTo() note: spool streamed into the ZIP accent: true --- :: ```ts twoslash import { createExcelSchema, createWorkbookStream } from "typed-xlsx"; type Order = { orderId: string; sku: string; qty: number }; declare function pages(): AsyncIterable; const schema = createExcelSchema() .column("orderId", { header: "Order ID", accessor: "orderId" }) .column("sku", { header: "SKU", accessor: "sku" }) .column("qty", { header: "Qty", accessor: "qty", summary: (s) => [s.formula("sum")] }) .build(); const workbook = createWorkbookStream(); const table = await workbook.sheet("Orders").table("orders", { schema }); for await (const page of pages()) { await table.commit({ rows: page }); } await workbook.writeToFile("./orders.xlsx"); ``` Use it when rows come from a database cursor or a paginated API, or when the export is large enough that holding every row would strain your runtime. ## What stays in memory With the buffered builder, memory grows with the whole report: the rows, the planned cells and the sheet XML. With the streaming builder, a committed batch is released once it is written to the spool. What remains is small and per-workbook (the style registry, summary accumulators), plus, depending on the schema: - the shared string table, unless you use inline strings - merge ranges from sub-row expansion - hyperlinks and embedded images - row ranges for summary formulas that read logical rows So memory grows slowly with the row count instead of staying flat. Measured on the current engine, an 11-column export with inline strings peaks at 274 MiB for 200k rows and 279 MiB for 500k rows; see [Benchmarks](https://typed-xlsx.vercel.app/performance/benchmarks) and [Large Exports](https://typed-xlsx.vercel.app/performance/large-exports). ## API differences | | `createWorkbook()` | `createWorkbookStream()` | | ------------------ | ----------------------------------------------- | ------------------------------------------------------------------------------------- | | Row input | `rows` on `.table()` | `table.commit({ rows })`, any number of times | | `.table()` returns | the sheet, chainable and synchronous | a `Promise` of the table handle | | Output | `toBuffer()`, `toUint8Array()`, `writeToFile()` | `writeToFile()`, `pipeTo()`, `pipeToNode()`, `toReadableStream()`, `toNodeReadable()` | | Summary rows | computed from all rows | accumulated across commits | | Features | every schema feature | every schema feature | The schema doesn't change between the two, so you can start buffered and switch to streaming without touching it. # Defining Columns A schema is a chain of `.column(id, options)` calls on `createExcelSchema()`, closed with `.build()`. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type User = { name: string; email: string; role: string }; const users = createExcelSchema() .column("name", { header: "Name", accessor: "name" }) .column("email", { header: "Email", accessor: "email" }) .column("role", { header: "Role", accessor: "role" }) .build(); ``` The column ID is how the rest of the API refers to the column: formulas, `select`, summaries. It must be unique in the schema; a duplicate throws as soon as it's declared. The header defaults to the ID. The built schema is a plain, immutable object. Export it from a module and reuse it across workbooks. ## From row to cell A cell's value goes through up to three steps, then gets its number format and style: ::flow-figure --- steps: - cap: Accessor code: accessor note: a field path or a function of the row - cap: Transform code: transform note: optional, receives the value and the row - cap: Default code: defaultValue note: used when the value is null or undefined - cap: Cell code: format · style note: number format and look accent: true --- :: ### Accessors A string accessor is a path into the row. typed-xlsx autocompletes every valid path, nested ones included, and rejects the rest: ```ts twoslash // @errors: 2322 import { createExcelSchema } from "typed-xlsx"; type Row = { user: { firstName: string }; company: { name: string } }; createExcelSchema() .column("firstName", { accessor: "user.firstName" }) .column("company", { accessor: "company.title" }) .build(); ``` A function accessor derives the value from the whole row: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Employee = { firstName: string; lastName: string; hiredAt: string }; const schema = createExcelSchema() .column("fullName", { header: "Employee", accessor: (row) => `${row.firstName} ${row.lastName}`, }) .column("hired", { header: "Hired", accessor: (row) => new Date(row.hiredAt) }) .build(); ``` An accessor that returns an array expands the row into several worksheet rows. See [Row Model](https://typed-xlsx.vercel.app/core-concepts/row-model). ### Transform and default value `transform` runs after the accessor and receives the typed value. `defaultValue` fills in when the result is `null` or `undefined`. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Account = { plan: "starter" | "pro" | "enterprise"; department: string | null }; const labels = { starter: "Starter", pro: "Pro", enterprise: "Enterprise" } as const; const schema = createExcelSchema() .column("plan", { accessor: "plan", transform: ({ value }) => labels[value], // ^? }) .column("department", { accessor: "department", defaultValue: "Unassigned" }) .build(); ``` ## Widths By default each column is as wide as its longest value. Bound it, or fix it: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ description: string; amount: number }>() .column("description", { accessor: "description", minWidth: 20, maxWidth: 60, style: { alignment: { wrapText: true, vertical: "top" } }, }) .column("amount", { accessor: "amount", width: 14, style: { numFmt: '"$"#,##0.00' } }) .build(); ``` Widths are in Excel character units. A fixed `width` wins over everything; otherwise the measured width is clamped between `minWidth` and `maxWidth`. Measuring ignores wrapping, so give wrapped text a `maxWidth`, or the column grows to fit its longest paragraph on one line. ## Columns that depend on the export `condition` includes or drops a column based on the schema [context](https://typed-xlsx.vercel.app/core-concepts/schema-context). It runs once per table, not per row. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Deal = { owner: string; amount: number; margin: number }; const schema = createExcelSchema() .column("owner", { accessor: "owner" }) .column("amount", { accessor: "amount", condition: ({ ctx }) => ctx.showFinance }) .column("margin", { accessor: "margin", condition: ({ ctx }) => ctx.showFinance }) .build(); createWorkbook() .sheet("Deals") .table("deals", { rows: [], schema, context: { showFinance: false } }); ``` To choose columns per export without a context flag, use [Column Selection](https://typed-xlsx.vercel.app/columns/column-selection). ## Cell types `type` turns a column into something other than plain values: | `type` | Renders | Page | | ------------- | ---------------------------------------- | ------------------------------------------------------------------------------------ | | `"badge"` | a styled status pill per value | [Badges and Checkboxes](https://typed-xlsx.vercel.app/columns/badges-and-checkboxes) | | `"checkbox"` | a native Excel checkbox | [Badges and Checkboxes](https://typed-xlsx.vercel.app/columns/badges-and-checkboxes) | | `"sparkline"` | a small line or column chart in the cell | [Sparklines](https://typed-xlsx.vercel.app/columns/sparklines) | | `"image"` | an embedded picture or an `IMAGE()` URL | [Images](https://typed-xlsx.vercel.app/columns/images) | Plain columns can also carry a [hyperlink](https://typed-xlsx.vercel.app/columns/hyperlinks) or a [validation dropdown](https://typed-xlsx.vercel.app/columns/data-validation). ## Column options | Option | Type | Purpose | | ---------------------- | --------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------ | | `header` | `string | ({ ctx }) => string` | header text, defaults to the column ID | | `accessor` | path or `(row) => value` | where the value comes from | | `transform` | `({ value, row, ctx }) => value` | reshape the value | | `defaultValue` | cell value | used for `null` and `undefined` | | `formula` | `({ refs, row, fx, ctx }) => …` | write an Excel formula instead of a value, see [Formula Columns](https://typed-xlsx.vercel.app/formulas/formula-columns) | | `format` | number format, or a function of the row and context | shorthand for `style.numFmt` | | `style` | `CellStyle` or `({ row, ctx }) => CellStyle` | [static](https://typed-xlsx.vercel.app/styling/cell-styles) or [per-row](https://typed-xlsx.vercel.app/styling/dynamic-styles) style | | `headerStyle` | `CellStyle` | style of the header cell | | `conditionalStyle` | builder | native [conditional formats](https://typed-xlsx.vercel.app/styling/conditional-styles) | | `summary` | `(s) => [...]` | [summary rows](https://typed-xlsx.vercel.app/columns/summary-rows), report mode | | `totalsRow` | `{ function }` or `{ label }` | [totals row](https://typed-xlsx.vercel.app/excel-tables/totals-row), excel-table mode | | `validation` | builder | [data validation](https://typed-xlsx.vercel.app/columns/data-validation) | | `hyperlink` | target or function | [hyperlinks](https://typed-xlsx.vercel.app/columns/hyperlinks) | | `expansion` | `"auto" | "single" | "expand"` | where a formula is written on expanded rows, see [Row Model](https://typed-xlsx.vercel.app/core-concepts/row-model) | | `width` | `number` | fixed width | | `minWidth`, `maxWidth` | `number` | bounds for the measured width | | `autoWidth` | `boolean`, default `true` | measure the content | | `condition` | `({ ctx }) => boolean` | include the column for this export or not | | `type` | `"badge" | "checkbox" | "sparkline" | "image"` | a [cell type](https://typed-xlsx.vercel.app/#cell-types) | # Column Groups `.group(id, build)` creates a structural group with stable child columns. Use `group()` when the child columns are known at schema definition time and you want a structural scope for headers, selection, and formulas. Groups also support `condition: ({ ctx }) => boolean` through the options object when the whole structural scope should be included or omitted from schema context. ## Structural groups with `.group()` `group()` receives a child builder and records a stable structural scope in the schema. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type SalesRow = { q1: number; q2: number; q3: number; q4: number; }; const schema = createExcelSchema() .group("quarters", { header: "Quarterly Revenue" }, (group) => { group.column("q1", { header: "Q1", accessor: "q1" }); group.column("q2", { header: "Q2", accessor: "q2" }); group.column("q3", { header: "Q3", accessor: "q3" }); group.column("q4", { header: "Q4", accessor: "q4" }); }) .build(); ``` ## Conditional structural groups Use `condition` when a whole structural group should exist only for some schema-context configurations. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Deal = { owner: string; amount: number; margin: number }; const schema = createExcelSchema() .column("owner", { accessor: "owner" }) .group( "finance", { header: "Finance", condition: ({ ctx }) => ctx.showFinance, }, (group) => { group.column("amount", { accessor: "amount" }); group.column("margin", { accessor: "margin" }); }, ) .build(); createWorkbook() .sheet("Deals") .table("deals", { rows: [], schema, context: { showFinance: true }, }); ``` Groups work in both schema modes: - `createExcelSchema()` / `mode: "report"` - `createExcelSchema({ mode: "excel-table" })` In excel-table mode, the rendered headers still stay physically flat even when the schema contains structural groups. ## Groups and formulas Formula columns inside `.group()` follow the same predecessor-based rules as top-level formulas. A formula declared inside a nested scope can reference: - any column declared before the scope in the outer chain - any column declared before it within the same group Later formulas can aggregate the structural group with `refs.group(...)` and helpers like `fx.sum(...)` or `fx.average(...)`. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Row = { baseRate: number; units: number }; const schema = createExcelSchema() .column("baseRate", { accessor: "baseRate" }) .column("units", { accessor: "units" }) .group("derived", (group) => { const withGrossRevenue = group.column("grossRevenue", { header: "Gross", formula: ({ refs, fx }) => fx.round(refs.column("units").mul(refs.column("baseRate")), 2), style: { numFmt: "$#,##0.00" }, }); withGrossRevenue.column("commission", { header: "Commission", formula: ({ refs, fx }) => fx.round(refs.column("grossRevenue").mul(0.1), 2), style: { numFmt: "$#,##0.00" }, }); }) .column("groupTotal", { header: "Group Total", formula: ({ refs, fx }) => fx.sum(refs.group("derived")), style: { numFmt: "$#,##0.00", font: { bold: true } }, }) .build(); ``` For the full scope rules and mode differences, see [Scope & References](https://typed-xlsx.vercel.app/formulas/scope-and-references). For runtime-generated columns, see [Dynamic Columns](https://typed-xlsx.vercel.app/columns/dynamic-columns). # Dynamic Columns `.dynamic(id, build)` creates runtime-generated columns from table-time `context`. Use it when the child columns depend on runtime inputs such as regions, months, organizations, or categories. Dynamic scopes also support `condition: ({ ctx }) => boolean` in the optional options object when the entire runtime-generated scope should be included or omitted from schema context. ## Runtime-generated columns with `.dynamic()` `dynamic()` receives the builder plus `{ ctx }`, where `ctx` is the schema-level context object declared on `createExcelSchema()`. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type User = { firstName: string; organizations: Array<{ id: number; name: string }>; }; type SchemaContext = { orgs: Array<{ id: number; name: string }>; }; const schema = createExcelSchema() .column("firstName", { accessor: "firstName", header: "Name" }) .dynamic("orgs", (builder, { ctx }) => { for (const org of ctx.orgs) { builder.column(`org-${org.id}`, { header: org.name, accessor: (row) => (row.organizations.some((o) => o.id === org.id) ? "Yes" : "No"), }); } }) .build(); ``` ## Injecting context at table time Dynamic scopes receive their data when the table is built, not when the schema is defined. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type User = { firstName: string; organizations: Array<{ id: number; name: string }>; }; type SchemaContext = { orgs: Array<{ id: number; name: string }>; }; const schema = createExcelSchema() .column("firstName", { accessor: "firstName", header: "Name" }) .dynamic("orgs", (builder, { ctx }) => { for (const org of ctx.orgs) { builder.column(`org-${org.id}`, { header: org.name, accessor: (row) => (row.organizations.some((o) => o.id === org.id) ? "✓" : ""), style: ({ row }) => ({ fill: { color: { rgb: row.organizations.some((o) => o.id === org.id) ? "DCFCE7" : "FEF2F2", }, }, alignment: { horizontal: "center" }, }), }); } }) .build(); const orgs = [ { id: 1, name: "Acme Corp" }, { id: 2, name: "Globex" }, { id: 3, name: "Initech" }, ]; const users: User[] = []; createWorkbook().sheet("Users").table("users", { rows: users, schema, context: { orgs }, }); ``` The schema definition stays static. The dynamic child columns are generated fresh from the `context` passed into `.table()`. For the full schema-context model, including where else `ctx` is available and why `context` stays required independently from `select`, see [Schema Context](https://typed-xlsx.vercel.app/core-concepts/schema-context). ## Use with column selection Selection works at the dynamic-scope level. Use the declared dynamic ID, not the generated child column IDs. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Row = { name: string; orgs: number[] }; const schema = createExcelSchema() .column("name", { accessor: "name" }) .dynamic("memberships", (builder, { ctx }) => { for (const id of ctx.orgIds) { builder.column(`org-${id}`, { accessor: (row) => row.orgs.includes(id) }); } }) .build(); createWorkbook() .sheet("Sheet") .table("sheet", { rows: [], schema, context: { orgIds: [] }, select: { exclude: ["memberships"] }, }); ``` Even though the only dynamic scope is excluded here, `context` is still required because it is part of the schema contract. ## Dynamic scopes and formulas Formula columns inside a dynamic scope follow the same predecessor-based rules as top-level formulas. A formula declared inside `.dynamic()` can reference: - any column declared before the scope in the outer chain - any column declared before it within the same dynamic scope Later formulas can aggregate the dynamic scope with `refs.dynamic(...)` and helpers like `fx.sum(...)` or `fx.average(...)`. For the full scope model, see [Scope & References](https://typed-xlsx.vercel.app/formulas/scope-and-references). # Column Selection The schema itself stays stable. Which columns appear in a given export is decided at the table, with `select`, so the same schema definition can serve multiple export shapes. ## Include only a subset of columns ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; const schema = createExcelSchema<{ email: string; firstName: string; lastName: string }>() .column("firstName", { accessor: "firstName" }) .column("lastName", { accessor: "lastName" }) .column("email", { accessor: "email" }) .build(); const workbook = createWorkbook(); workbook.sheet("Users").table("users", { rows: [], schema, select: { include: ["firstName", "email"], }, }); ``` ## Exclude a few columns from a wide schema ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; const schema = createExcelSchema<{ email: string; firstName: string; internalNote: string; }>() .column("firstName", { accessor: "firstName" }) .column("email", { accessor: "email" }) .column("internalNote", { accessor: "internalNote" }) .build(); const workbook = createWorkbook(); workbook.sheet("Public export").table("public-export", { rows: [], schema, select: { exclude: ["internalNote"], }, }); ``` This pattern makes it easy to define one rich schema and reuse it for several workbook views without cloning column definitions. Selections can target plain column IDs, structural group IDs from `.group()`, and dynamic-scope IDs from `.dynamic()`. See [Column Groups](https://typed-xlsx.vercel.app/columns/column-groups) and [Dynamic Columns](https://typed-xlsx.vercel.app/columns/dynamic-columns). ## Column IDs are statically typed Both `include` and `exclude` only accept IDs that were declared through `.column()`, `.group()`, or `.dynamic()`. Passing an unknown string is a compile-time error, never a silent runtime no-op. Hover over `SelectableColumns` to see the full set of valid IDs for a schema: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; import type { SchemaColumnId } from "typed-xlsx"; const schema = createExcelSchema<{ email: string; firstName: string; lastName: string }>() .column("firstName", { accessor: "firstName" }) .column("lastName", { accessor: "lastName" }) .column("email", { accessor: "email" }) .build(); type SelectableColumns = SchemaColumnId; // ^? ``` Providing a string outside that set is caught immediately: ```ts twoslash // @errors: 2322 import { createExcelSchema, createWorkbook } from "typed-xlsx"; const schema = createExcelSchema<{ email: string; firstName: string }>() .column("firstName", { accessor: "firstName" }) .column("email", { accessor: "email" }) .build(); createWorkbook() .sheet("Users") .table("users-invalid-selection", { rows: [], schema, select: { include: ["firstName", "nonExistent"], }, }); ``` # Summary Rows Summary rows sit below a report's data: totals, averages, counts, or any value you compute. Each column declares what it puts in each summary row. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Sale = { region: string; units: number; revenue: number }; const schema = createExcelSchema() .column("region", { header: "Region", accessor: "region", summary: (s) => [s.label("Total"), s.label("Best revenue")], }) .column("units", { header: "Units", accessor: "units", summary: (s) => [s.formula("sum"), s.empty()], }) .column("revenue", { header: "Revenue", accessor: "revenue", style: { numFmt: '"$"#,##0' }, summary: (s) => [ s.formula("sum"), s.cell({ init: () => 0, step: (max, row) => Math.max(max, row.revenue), finalize: (max) => max, }), ], }) .build(); ``` ::sheet-figure --- align: - l - r - r columns: - Region - Units - Revenue marks: - range: B5:C5 label: 'summary.formula("sum"): a live formula, recalculated if a value above changes' - range: C6 label: "summary.cell(…): computed in JavaScript while rows are written, stored as a value" rows: - - EMEA - 120 - $48,200 - - Americas - 95 - $61,400 - - APAC - 70 - $33,900 summaries: - - Total - =SUM(B2:B4) - =SUM(C2:C4) - - Best revenue - null - $61,400 widths: - 120 - 120 - 130 --- :: The array a column returns lines up by position: the first item goes in the first summary row, the second in the second, and so on. Summary rows exist in report mode only; an Excel table has a single [totals row](https://typed-xlsx.vercel.app/excel-tables/totals-row) instead. ## Formula or value | | `summary.formula()` | `summary.cell()` | | ------------------- | ----------------------------------- | ------------------------------------------------------------------- | | Writes | an Excel formula over the column | a value you compute with a reducer | | Updates when edited | yes, Excel recalculates it | no, it's a fixed value | | Good for | sums, averages, counts, min and max | logic across several fields, or anything Excel can't express simply | | Streaming | yes | yes, the reducer runs across every committed batch | ## Formula summaries Pass one of the shorthands, `"sum"`, `"average"`, `"count"`, `"min"` or `"max"`, or a callback for anything more: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ amount: number }>() .column("amount", { accessor: "amount", summary: (s) => [ s.formula("sum"), s.formula(({ column, fx }) => fx.round(column.cells().average(), 2)), ], }) .build(); ``` The callback receives `column` and the same `fx` functions as formula columns: | Call | Writes | | --------------------------------------------------- | --------------------------------------------- | | `column.cells().sum()` | `=SUM(C2:C1001)` over the column's data range | | `.average()`, `.count()`, `.min()`, `.max()` | the matching range function | | `fx.round(column.cells().sum(), 2)` | `=ROUND(SUM(C2:C1001),2)` | | `column.rows().average((row) => row.cells().sum())` | one value per logical row, then the average | The range always covers the table's actual data rows. `column.rows()` matters when rows expand into sub-rows; [Row Model](https://typed-xlsx.vercel.app/core-concepts/row-model#summaries-make-the-same-choice) shows how it differs from `column.cells()` on the same data. ## Value summaries `summary.cell()` is a reducer: `init` creates the accumulator, `step` runs once per row, `finalize` turns the result into the cell value. Use it when the summary needs several fields of the row: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Line = { gross: number; discount: number }; const schema = createExcelSchema() .column("gross", { header: "Gross", accessor: "gross", summary: (s) => [ s.cell({ init: () => ({ gross: 0, discount: 0 }), step: (acc, row) => ({ gross: acc.gross + row.gross, discount: acc.discount + row.discount, }), finalize: (acc) => acc.gross - acc.discount, style: { numFmt: '"$"#,##0.00', font: { bold: true } }, }), ], }) .build(); ``` The reducer never sees all rows at once, which is why it also works when streaming. ## Labels, blanks and spacers `summary.label(text)` writes text, typically in the first column. For a column with nothing to show: | Helper | Cell | Summary styling | Use it to | | ------------------ | ----- | ----------------------------- | ------------------------------------------------- | | `summary.empty()` | empty | yes, like the rest of the row | keep the row's look across a column with no value | | `summary.spacer()` | empty | no | leave a deliberate gap in the row | Labels can be computed from the schema [context](https://typed-xlsx.vercel.app/core-concepts/schema-context), for example `summary.label(({ ctx }) => ctx.t("total"))`. ## Styling Summary cells use the theme's `summary` slot by default. Each definition can also take: - `style`: a `CellStyle`; for `summary.cell()` it can be a function of the finalized value - `conditionalStyle`: a native Excel conditional format, re-evaluated as the value changes ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ amount: number }>() .column("amount", { accessor: "amount", summary: (summary) => [ summary.formula("sum", { style: { numFmt: '"$"#,##0.00', font: { bold: true } }, conditionalStyle: (conditional) => conditional.when(({ cell }) => cell.current().lt(0), { fill: { color: { rgb: "FEE2E2" } }, font: { color: { rgb: "991B1B" } }, }), }), ], }) .build(); ``` See [Cell Styles](https://typed-xlsx.vercel.app/styling/cell-styles) and [Conditional Styles](https://typed-xlsx.vercel.app/styling/conditional-styles). ## When summary rows appear A summary row is written when the schema is in report mode and at least one selected column defines a summary. Columns left out with `select` don't contribute. # Data Validation Use `validation` when a column should emit native Excel data-validation rules. Unlike JavaScript-side transforms, validation stays live after the workbook opens. That means Excel can still: - show dropdown lists - show input prompts when a cell is selected - block invalid edits with an error alert - reevaluate custom rules after manual edits :workbook-preview{artifact="kitchen-sink" caption="Dropdown lists and number rules on input columns, from the kitchen-sink example." sheet="10 Data Validation"} ## Validation builder The recommended API is the callback builder form: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Row = { amount: number; startDate: Date; status: "draft" | "active" | "archived"; }; const schema = createExcelSchema() .column("status", { header: () => "Status", accessor: "status", validation: (validation) => validation .list(["draft", "active", "archived"]) .prompt({ title: () => "Allowed values", message: () => "Choose draft, active, or archived", }) .error({ title: () => "Invalid status", message: () => "Use one of the allowed workflow states", }), }) .column("amount", { accessor: "amount", validation: (validation) => validation.integer().between(100, 100000).allowBlank(), }) .column("startDate", { accessor: "startDate", validation: (validation) => validation.date().gte(new Date(Date.UTC(2025, 0, 1))), }) .build(); ``` ## Supported validation kinds - `list([...])` - `integer()` - `decimal()` - `date()` - `textLength()` - `custom(({ row, refs, fx }) => ...)` `integer()` is the public API name even though Excel serializes this kind as `whole` internally. ## Comparison helpers These apply to numeric, date, text-length, and custom rules where appropriate: - `between(min, max)` - `notBetween(min, max)` - `eq(value)` - `neq(value)` - `gt(value)` - `gte(value)` - `lt(value)` - `lte(value)` ## Prompts and error alerts Prompt and error text support lazy callbacks, which is useful when labels come from your own i18n layer: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Context = { t(key: string): string }; const schema = createExcelSchema<{ status: string }, Context>() .column("status", { header: ({ ctx }) => ctx.t("columns.status"), accessor: "status", validation: (validation) => validation.list(["draft", "active", "archived"]).error({ title: ({ ctx }) => ctx.t("validation.status.title"), message: ({ ctx }) => ctx.t("validation.status.message"), }), }) .build(); ``` The callbacks receive the table's schema context. The library does not know about any i18n framework directly, so applications can pass their own `t` function. ## Custom validation rules Custom rules reuse the same formula DSL used by formula columns and conditional styles. That means you can compare the current column against earlier columns in the same row: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ amount: number; score: number }>() .column("amount", { accessor: "amount" }) .column("score", { accessor: "score", validation: (validation) => validation.custom(({ refs }) => refs.column("score").gte(refs.column("amount"))), }) .build(); ``` Like formulas, validation references are predecessor-only. A column can validate against columns declared before it. ## Excel-table mode `validation` also works in `createExcelSchema({ mode: "excel-table" })`. The rule authoring API stays the same. Only the emitted reference strategy changes under the hood so Excel-table rows still validate correctly. ## Kitchen sink example The kitchen sink workbook now includes a dedicated `Validation` sheet with: - a dropdown-backed `Status` column - an integer range on `Amount` - a minimum date rule on `Start Date` This is the easiest place to test the feature in Excel manually. # Hyperlinks Use `hyperlink` when a cell should keep its normal value but also carry native Excel hyperlink metadata. The cell value still comes from `accessor`, `transform`, or `formula`. The hyperlink target is separate. :workbook-preview{artifact="kitchen-sink" caption="External, mailto and in-workbook links from the kitchen-sink example." sheet="07 Hyperlinks"} ## Basic usage ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Row = { customerId: string; customerName: string; hasPortal: boolean; }; const schema = createExcelSchema() .column("customerName", { accessor: "customerName", hyperlink: ({ row }) => row.hasPortal ? { target: `https://example.com/customers/${row.customerId}`, tooltip: "Open customer record", } : null, }) .build(); ``` Supported forms: - `hyperlink: "https://example.com/..."` - `hyperlink: { target, tooltip?, style? }` - `hyperlink: ({ row, rowIndex, subRowIndex, ctx }) => string | { target, tooltip?, style? } | null | undefined` Returning `null` or `undefined` means that specific cell has no hyperlink. `tooltip` can be a string or a lazy callback, so shared schemas can resolve localized tooltip text when rows are planned. ## Link-local style overrides Linked cells get a default hyperlink appearance: - blue text - underline If you need a different look, put it directly on the hyperlink object: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ customerId: string; customerName: string; hasPortal: boolean; }>() .column("customerName", { accessor: "customerName", hyperlink: ({ row }) => row.hasPortal ? { target: `https://example.com/customers/${row.customerId}`, tooltip: "Open customer record", style: { font: { color: { rgb: "7C3AED" }, underline: false, bold: true, }, }, } : null, }) .build(); ``` That style only applies when the link exists. ## Internal and external links External links use worksheet relationships: ```ts twoslash hyperlink: () => ({ target: "https://example.com/report/123" }); ``` Internal workbook links use Excel locations: ```ts twoslash hyperlink: () => ({ target: "#'Summary'!A1", tooltip: "Jump to summary" }); ``` ## Notes - hyperlinks are independent from the rendered cell value - hyperlink targets are concrete strings at export time - formula-based hyperlink targets are intentionally not supported - hyperlink-local `style` is ignored when the callback returns `null` or `undefined` # Sparklines Use `type: "sparkline"` when a column should render a native Excel mini chart for each row: a 12-month trend next to each account, a win/loss strip next to each sales rep. These are real Excel sparklines, not images: they recalculate when the source cells change, and they respect the workbook theme. The sparkline target can be a sparkline-only column, or it can share a cell with a normal `accessor` or `formula` value. :workbook-preview{artifact="kitchen-sink" caption="Line sparklines drawn from typed source columns, from the kitchen-sink example." sheet="08 Sparklines"} ## Basic usage ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Row = { account: string; jan: number; feb: number; mar: number; }; const schema = createExcelSchema() .column("account", { accessor: "account" }) .column("jan", { accessor: "jan" }) .column("feb", { accessor: "feb" }) .column("mar", { accessor: "mar" }) .column("trend", { type: "sparkline", header: "Trend", width: 18, source: ["jan", "feb", "mar"], sparklineType: "line", emptyCells: "zero", style: { line: { color: "#2563EB", weight: 1.25 }, dots: false, high: { color: "#16A34A" }, low: { color: "#DC2626" }, }, }) .build(); ``` The source column IDs are checked by TypeScript. A sparkline can only reference columns declared before the sparkline column. ::callout{type="info"} Sparklines read better with a little vertical room. Set `defaults: { rowHeight: 30 }` on the table to give every body row a minimum height. See [Table style defaults](https://typed-xlsx.vercel.app/workbook/building-workbooks#table-style-defaults) . :: ## Source forms Use an explicit list when you want exact columns: ```ts .column("trend", { type: "sparkline", source: ["jan", "feb", "mar"], }) ``` Use a range when the source columns are contiguous: ```ts .column("trend", { type: "sparkline", source: { from: "jan", to: "mar" }, }) ``` Use a group or dynamic scope when the columns are produced structurally: ```ts .column("trend", { type: "sparkline", source: { group: "months" }, }) ``` ```ts .column("trend", { type: "sparkline", source: { dynamic: "months" }, }) ``` At export time, selected columns must still resolve to a non-empty contiguous range. If a source column is excluded by `select`, typed-xlsx throws a clear error instead of emitting a broken workbook. ## Styling and themes Sparkline colors come from the active spreadsheet theme by default. A table theme or schema theme can provide these tokens: - `sparklineSeries` - `sparklineMarkers` - `sparklineFirst` - `sparklineLast` - `sparklineHigh` - `sparklineLow` - `sparklineNegative` - `sparklineAxis` Column-level `style` options override the theme: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ jan: number; feb: number; mar: number }>() .column("jan", { accessor: "jan" }) .column("feb", { accessor: "feb" }) .column("mar", { accessor: "mar" }) .column("trend", { type: "sparkline", source: { from: "jan", to: "mar" }, style: { line: { color: "#2563EB", weight: 1.25 }, dots: { visible: true, color: "#1D4ED8" }, first: { color: "#64748B" }, last: { color: "#10B981" }, high: { color: "#16A34A" }, low: { color: "#DC2626" }, axis: { visible: true, color: "#94A3B8", min: { value: 0 }, max: "group", }, }, }) .build(); ``` For column and win/loss sparklines, use `style.series` for the main bar color and `style.negative` for negative values: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ won: number; lost: number; open: number }>() .column("won", { accessor: "won" }) .column("lost", { accessor: "lost" }) .column("open", { accessor: "open" }) .column("mix", { type: "sparkline", source: ["won", "lost", "open"], sparklineType: "winLoss", style: { series: "#16A34A", negative: { color: "#DC2626" }, axis: { visible: true, color: "#64748B" }, }, }) .build(); ``` ## Option reference Sparkline renderer options: | Option | Values | What it controls | | --------------- | ------------------------------------- | --------------------------------------------------------------------------------------------------- | | `type` | `"sparkline"` | Selects the sparkline renderer | | `source` | list, range, group, dynamic scope | Which columns feed the mini chart (see [Source forms](https://typed-xlsx.vercel.app/#source-forms)) | | `sparklineType` | `"line"` \| `"column"` \| `"winLoss"` | Chart shape, defaults to `"line"` | | `emptyCells` | `"gap"` \| `"zero"` \| `"span"` | How Excel draws missing values | | `style` | object | Colors, markers, and axis (see below) | Everything visual lives under `style`: | Style option | Accepts | What it controls | | ------------------------------ | --------------------------------------------- | --------------------------------------------- | | `style.series` | color string | Main series color (bars for column/winLoss) | | `style.line` | `{ color?, weight? }` | Line color and weight in points (line type) | | `style.markers` / `style.dots` | boolean or `{ visible?, color? }` | Data point markers (`dots` is an alias) | | `style.first` / `style.last` | boolean or `{ visible?, color? }` | Highlight the first / last point | | `style.high` / `style.low` | boolean or `{ visible?, color? }` | Highlight the highest / lowest point | | `style.negative` | boolean or `{ visible?, color? }` | Highlight negative values | | `style.axis` | boolean or `{ visible?, color?, min?, max? }` | Horizontal axis visibility, color, and bounds | | `style.hidden` | boolean | Include values from hidden rows/columns | | `style.rightToLeft` | boolean | Plot the series right-to-left | Axis bounds (`style.axis.min` / `style.axis.max`) can be `"individual"`, `"group"`, `"custom"`, or `{ type?, value? }`, matching Excel's own axis scaling modes. # Images Use `type: "image"` when a column should render a picture instead of normal text or numeric data: product thumbnails in a catalog export, logos in a partner directory, photos in an inspection report. typed-xlsx supports two image sources: | Mode | API | XLSX output | Best for | | -------------- | ------------------------------ | -------------------------------------------------------------------- | --------------------------------------------------------------- | | Embedded bytes | `type: "image"` | Stores PNG/JPEG bytes under `xl/media/*` and draws them in the sheet | Portable exports, offline workbooks, attachments | | URL formula | `type: "image", source: "url"` | Writes Excel's `IMAGE("https://...")` formula into the cell | Lightweight workbooks where live external images are acceptable | :workbook-preview{artifact="kitchen-sink" caption="Embedded pictures and URL-backed IMAGE() cells side by side, from the kitchen-sink example." sheet="09 Images Media"} ## Embedded images Embedded images are the default. The accessor must resolve to image bytes: ```ts twoslash import { createExcelSchema, type ImageValue } from "typed-xlsx"; type Product = { name: string; thumbnail?: Uint8Array; }; const schema = createExcelSchema() .column("thumbnail", { type: "image", accessor: "thumbnail", alt: "name", size: { width: 56, height: 56 }, padding: 3, }) .column("name", { accessor: "name", }) .build(); ``` The accessor can also resolve to a full `ImageValue` when a row needs to carry per-image metadata alongside the bytes: ```ts twoslash import type { ImageValue } from "typed-xlsx"; declare const pngBytes: Uint8Array; // ---cut--- const image: ImageValue = { data: pngBytes, mediaType: "image/png", alt: "Product thumbnail", }; ``` If the bytes have a recognizable PNG or JPEG signature, `mediaType` is optional. Otherwise pass `mediaType: "image/png"` or `mediaType: "image/jpeg"` on the column or the row value. For remote images that need to be portable, fetch them in your application and pass the bytes to typed-xlsx: ```ts twoslash async function fetchImageBytes(url: string) { const response = await fetch(url); return new Uint8Array(await response.arrayBuffer()); } ``` typed-xlsx does not fetch remote images for embedded mode. This keeps the buffered API synchronous and makes network failures explicit in your export pipeline. ## File size and compression Embedded images increase workbook size because the image bytes are part of the XLSX package. typed-xlsx deduplicates identical embedded image bytes across the workbook, so a repeated logo or thumbnail payload is written once under `xl/media/*` and referenced by each drawing. The rendered `size` controls the worksheet drawing box. It does not shrink the embedded image bytes. If you pass a 1900x1900 image and render it as 40x40, the workbook still carries the original 1900x1900 payload. typed-xlsx does not resize, transcode, or recompress image pixels. PNG and JPEG files are already compressed formats, so the biggest file-size wins usually come from preparing export-sized thumbnails before building the workbook: ```ts twoslash type Product = { name: string; rawImageUrl: string; }; declare function createThumbnail( url: string, size: { width: number; height: number }, ): Promise; async function prepareRows(products: Product[]) { return Promise.all( products.map(async (product) => ({ ...product, thumbnail: await createThumbnail(product.rawImageUrl, { width: 80, height: 80 }), })), ); } ``` Use URL images when embedding is not required and the workbook can depend on public remote images instead. ## URL images URL images opt into Excel's `IMAGE()` formula. The accessor must resolve to a URL string or `{ url, alt?, size?, fit? }`: ```ts twoslash import { createExcelSchema, type ImageUrlValue } from "typed-xlsx"; type Product = { name: string; thumbnailUrl?: string; }; const schema = createExcelSchema() .column("remotePreview", { type: "image", source: "url", accessor: "thumbnailUrl", alt: "name", size: { width: 56, height: 56 }, }) .column("name", { accessor: "name", }) .build(); const remoteImage: ImageUrlValue = { url: "https://cdn.example.com/products/sku-123.png", alt: "Product thumbnail", size: { width: 56, height: 56 }, }; ``` This mode does not embed image bytes. Excel fetches the image when the workbook opens or recalculates. ## Choosing a mode Use embedded images when: - the workbook must work offline - recipients may not have access to the original image host - the image URL requires cookies, headers, signed requests, or other authentication - the file is an attachment or a durable audit/export artifact Use URL images when: - the workbook can depend on network access - images are public HTTPS resources - you want smaller workbooks - it is acceptable for images to update or fail independently of the XLSX file ## URL constraints URL image columns inherit Excel `IMAGE()` and Office external-content behavior: - Excel support depends on the user's Excel version. - Authenticated URLs do not render. - Redirected URLs can be blocked for security reasons. - Long URLs may hit Excel formula/source limits. - Office security settings may block external content until the user trusts the workbook. Microsoft documents the `IMAGE()` function behavior in [IMAGE function - Microsoft Support](https://support.microsoft.com/en-us/excel/functions/image-function){rel=""nofollow""}, and external-content blocking in [Block or unblock external content in Office documents](https://support.microsoft.com/en-us/office/security-privacy/block-or-unblock-external-content-in-office-documents){rel=""nofollow""}. ## Layout options Both modes support: - `alt`: static text, row path, or row callback - `size`: `{ width, height }` in pixels - `fit`: `"contain"`, `"cover"`, or `"stretch"` as display intent Embedded images also support: - `mediaType` - `padding` URL images reject `mediaType` and `padding` because there is no media part in the XLSX package. # Badges and Checkboxes Status columns and boolean columns almost always end up hand-styled: one color per status value, native checkbox setup for booleans, repeated in every export that touches the field. Badge and checkbox columns move that mapping into the schema, next to the column it belongs to. `type: "badge"` writes ordinary styled values. `type: "checkbox"` writes boolean cells with Excel's native checkbox cell control metadata, so users can toggle the cells in Excel and formulas can consume the resulting `TRUE` / `FALSE` values. Both renderers work in buffered and streaming exports, including report-mode sub-row expansion. :workbook-preview{artifact="kitchen-sink" caption="The kitchen-sink example: status badges, and checkboxes that drive a formula." sheet="06 Badges Checkboxes"} ## Badge columns Badge columns are useful for status, tier, priority, and workflow-state fields: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Product = { sku: string; status: "Live" | "Low stock" | "Launch"; }; const schema = createExcelSchema() .column("sku", { accessor: "sku", }) .column("status", { type: "badge", accessor: "status", variants: { Live: { style: { fill: { color: { rgb: "DCFCE7" } }, font: { color: { rgb: "166534" }, bold: true }, }, }, "Low stock": { style: { fill: { color: { rgb: "FEF3C7" } }, font: { color: { rgb: "92400E" }, bold: true }, }, }, Launch: { label: "New", style: { fill: { color: { rgb: "DBEAFE" } }, font: { color: { rgb: "1D4ED8" }, bold: true }, }, }, }, }) .build(); ``` Each `variants` key is matched against `String(value)`. A variant can be a full `CellStyle`, or `{ label, style }` when the displayed text should differ from the source value: above, `Launch` rows display as `New`. `label` can also be a callback. That callback receives the row, the matched badge value, and the schema `ctx`, which is useful when labels come from an i18n function at export time: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; import type { BadgeVariantLabelContext } from "typed-xlsx"; type Product = { sku: string; status: "Live" | "Launch"; }; type SchemaContext = { t: (key: "status.live" | "status.launch") => string; }; type StatusLabelContext = BadgeVariantLabelContext< Product, Product["status"], SchemaContext >; const schema = createExcelSchema() .column("sku", { accessor: "sku" }) .column("status", { type: "badge", accessor: "status", variants: { Live: { label: ({ ctx }: StatusLabelContext) => ctx.t("status.live"), style: { fill: { color: { rgb: "DCFCE7" } }, font: { color: { rgb: "166534" }, bold: true }, }, }, Launch: { label: ({ ctx }: StatusLabelContext) => ctx.t("status.launch"), style: { fill: { color: { rgb: "DBEAFE" } }, font: { color: { rgb: "1D4ED8" }, bold: true }, }, }, }, }) .build(); ``` When the accessor value is a literal union, variant keys are type-checked against that union. Without `defaultVariant`, every union member must have a variant. With `defaultVariant`, `variants` can cover only the special cases and the fallback handles the rest: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Ticket = { status: "New" | "In progress" | "Blocked" | "Done"; }; const schema = createExcelSchema() .column("status", { type: "badge", accessor: "status", variants: { Blocked: { style: { fill: { color: { rgb: "FEE2E2" } }, font: { color: { rgb: "991B1B" }, bold: true }, }, }, Done: { style: { fill: { color: { rgb: "DCFCE7" } }, font: { color: { rgb: "166534" }, bold: true }, }, }, }, defaultVariant: { style: { fill: { color: { rgb: "F1F5F9" } }, font: { color: { rgb: "475569" } }, }, }, }) .build(); ``` ### Fallback styling When the source field is an open string (statuses coming from an API, user-defined tags), unknown values would render unstyled. Use `defaultVariant` to give them one shared fallback: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Ticket = { id: string; priority: string; // open set: new priorities may appear }; const schema = createExcelSchema() .column("id", { accessor: "id" }) .column("priority", { type: "badge", accessor: "priority", variants: { Critical: { style: { fill: { color: { rgb: "FEE2E2" } }, font: { color: { rgb: "991B1B" }, bold: true }, }, }, High: { style: { fill: { color: { rgb: "FEF3C7" } }, font: { color: { rgb: "92400E" }, bold: true }, }, }, }, defaultVariant: { style: { fill: { color: { rgb: "F1F5F9" } }, font: { color: { rgb: "475569" } }, }, }, }) .build(); ``` Known priorities get their declared colors; anything else renders with the neutral fallback instead of plain text. ## Checkbox columns Checkbox columns render boolean values as native Excel checkbox cells: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Task = { title: string; done: boolean; }; const schema = createExcelSchema() .column("done", { type: "checkbox", accessor: "done", }) .column("title", { accessor: "title", }) .build(); ``` Checked values serialize as boolean `TRUE`, unchecked values serialize as boolean `FALSE`, and `null` / `undefined` renders as an empty cell. Excel clients that support native cell checkboxes let users toggle the cell directly inside the workbook. That means later formula columns can reference checkbox columns directly: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Task = { title: string; done: boolean; }; const schema = createExcelSchema() .column("done", { type: "checkbox", accessor: "done", }) .column("state", { formula: ({ refs, fx }) => fx.if(refs.column("done").eq(true), "Done", "Open"), }) .column("title", { accessor: "title", }) .build(); ``` In Excel this behaves like `IF(A2=TRUE, "Done", "Open")`. If the user toggles the checkbox in Excel, dependent formulas recalculate from the updated boolean value. Checkbox support is implemented as native Excel cell-control metadata, not as drawing objects or legacy form controls. Spreadsheet clients that do not understand that metadata may show plain `TRUE` / `FALSE` values, but the underlying boolean data remains intact. ## Disabled checkbox cells Checkbox columns do not need a separate `disabled` option. In Excel, editability comes from sheet protection plus each cell's `style.protection.locked` value. Unlock the checkbox cells users may toggle, leave the rest locked, and protect the sheet: ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Task = { title: string; done: boolean; canToggle: boolean; }; const schema = createExcelSchema() .column("done", { type: "checkbox", accessor: "done", style: ({ row }) => row.canToggle ? { protection: { locked: false } } : { protection: { locked: true } }, }) .column("title", { accessor: "title", }) .build(); const workbook = createWorkbook(); workbook .sheet("Tasks", { protection: { selectLockedCells: false, selectUnlockedCells: true, }, }) .table("tasks", { schema, rows: [{ title: "Publish launch notes", done: false, canToggle: true }], }); ``` That supports static locked checkboxes and row- or context-based locked checkboxes at export time. Formula-based disabling is different: Excel conditional formatting can change visual styles, but it cannot toggle `locked` or `hidden`, so checkbox editability does not recalculate from formulas after the workbook is opened. ## Sub-row Expansion Both renderers support array-valued accessors in report mode: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Order = { orderId: string; lines: Array<{ sku: string; shipped: boolean }>; }; const schema = createExcelSchema() .column("orderId", { accessor: "orderId", }) .column("sku", { accessor: (row) => row.lines.map((line) => line.sku), }) .column("shipped", { type: "checkbox", accessor: (row) => row.lines.map((line) => line.shipped), }) .build(); ``` The checkbox values expand alongside the other array-valued columns. # Themes A theme is a named set of styles for the parts every report has: the title, headers, group headers, summary rows and body cells. Define it once, apply it to a schema or to a single table, and columns stop repeating the same `CellStyle` objects. ## How styles stack Every cell's style is merged from up to six layers. A higher layer overrides the properties it sets and keeps the rest from below. ::layer-figure --- layers: - cap: Conditional styles code: conditionalStyle note: Excel applies it on top whenever the rule is true accent: true - cap: Column style code: style, headerStyle note: static, or computed per row with ({ row, ctx }) => - cap: Table defaults code: table(…, { defaults }) note: per-export overrides of any slot - cap: Table theme code: table(…, { theme }) note: one workbook, several looks - cap: Schema theme code: .theme(theme) note: the report's default look - cap: Library defaults code: built in note: thin borders, bold headers bottomLabel: fallback topLabel: wins --- :: The pages for each layer: [Cell Styles](https://typed-xlsx.vercel.app/styling/cell-styles), [Dynamic Styles](https://typed-xlsx.vercel.app/styling/dynamic-styles), [Conditional Styles](https://typed-xlsx.vercel.app/styling/conditional-styles). ## Start from a preset `spreadsheetThemes` ships five ready themes: `classic` (the default), `slate`, `forest`, `rose` and `ocean`. ```ts twoslash import { createExcelSchema, spreadsheetThemes } from "typed-xlsx"; const schema = createExcelSchema<{ owner: string; amount: number }>() .theme(spreadsheetThemes.forest) .column("owner", { accessor: "owner" }) .column("amount", { accessor: "amount" }) .build(); ``` ## Change the tokens Every theme is built from tokens: a palette (`titleFill`, `headerFill`, `headerText`, `summaryFill`, `inputFill`, `hyperlink`, the sparkline colors and more) and two border styles. Overriding a token recolors every slot that uses it, so a brand theme is often just a few colors. ```ts twoslash import { defineSpreadsheetTheme } from "typed-xlsx"; const brand = defineSpreadsheetTheme({ tokens: { colors: { titleFill: "0E3B26", headerFill: "17603D", headerText: "FFFFFF", summaryFill: "E7F4EC", }, }, }); ``` ## Override slots Slots are the styled parts of a table: `title`, `groupHeader`, `groupHeaderFiller`, `header`, `summary`, and the body states `cellBase`, `cellLocked`, `cellUnlocked`, `cellHidden` and `hyperlink`. A slot takes a `CellStyle`, or a function of the tokens when it should follow the palette. `.extend()` derives a variant that changes only what it lists. ```ts twoslash import { createExcelSchema, defineSpreadsheetTheme, createWorkbook } from "typed-xlsx"; type Deal = { amount: number; owner: string }; const boardTheme = defineSpreadsheetTheme({ tokens: { colors: { headerFill: "0B1220", headerText: "F8FAFC" } }, slots: { title: ({ tokens }) => ({ fill: { color: { rgb: tokens.colors.headerFill } }, font: { color: { rgb: tokens.colors.headerText }, bold: true, size: 14 }, }), summary: { font: { bold: true } }, }, }); const watchlistTheme = boardTheme.extend({ slots: { title: { fill: { color: { rgb: "450A0A" } } }, groupHeader: { fill: { color: { rgb: "FECACA" } }, font: { color: { rgb: "9F1239" } } }, }, }); const schema = createExcelSchema() .theme(boardTheme) .column("owner", { accessor: "owner" }) .column("amount", { accessor: "amount" }) .build(); createWorkbook().sheet("Board").table("deals", { title: "Executive Watchlist", rows: [], schema, theme: watchlistTheme, }); ``` The watchlist keeps the board's header and summary and only changes the title and group header. ::callout{icon="i-lucide-info"} Tokens are read when slots are resolved. `defineSpreadsheetTheme({ tokens })` recolors every default slot, but tokens passed to `.extend()` only reach the slots passed in that same call. To recolor a whole theme, define a new one from tokens. :: ## Reuse a slot anywhere `theme.slot(name)` returns the slot's `CellStyle`, so a column can borrow it. Pass a second argument to adjust it. ```ts twoslash import { defineSpreadsheetTheme } from "typed-xlsx"; const theme = defineSpreadsheetTheme(); theme.slot("header"); theme.slot("header", { alignment: { horizontal: "left" } }); ``` ## Pivot slots Themes also style [pivot tables](https://typed-xlsx.vercel.app/charts/pivot-tables#styles-and-themes) through `pivotBody`, `pivotFrame`, `pivotHeader`, `pivotSubheading`, `pivotTotal` and `pivotStripe`. Each is bound to a table slot by default, such as `pivotHeader` to `header`, so a pivot matches the tables of its theme, and keeps following them when you `extend` the theme. Pivot slots take colours, font styles, fills and borders, which is what an Excel pivot style can change. ## Schema theme or table theme Put the theme on the schema when the report should always look the same. Put it on the table when one workbook shows the same schema in several looks, for example a neutral sheet and a red watchlist. ```ts twoslash import { createExcelSchema, defineSpreadsheetTheme, createWorkbook } from "typed-xlsx"; type Deal = { amount: number; owner: string }; const baseTheme = defineSpreadsheetTheme({ slots: { header: { font: { bold: true, color: { rgb: "F8FAFC" } }, fill: { color: { rgb: "1E293B" } }, }, }, }); const warmTheme = baseTheme.extend({ slots: { header: { fill: { color: { rgb: "7C2D12" } } } }, }); const schema = createExcelSchema() .theme(baseTheme) .column("owner", { accessor: "owner" }) .column("amount", { accessor: "amount" }) .build(); const workbook = createWorkbook(); workbook.sheet("Board").table("deals", { rows: [], schema }); workbook .sheet("Watchlist") .table("deals-watch", { rows: [], schema, theme: warmTheme }); ``` ## Themes don't protect cells Themes and conditional styles are visual. Locking and hiding cells is set through `style.protection` on the column and only takes effect on a protected sheet. See [Protection](https://typed-xlsx.vercel.app/workbook/protection). # Cell Styles Use cell styles when the formatting is the same for every cell in the column. This is the right tool for: - number formats - alignment - borders - stable fills - standard header styling If you want reusable semantic defaults rather than repeating literal style objects, start with [Themes](https://typed-xlsx.vercel.app/styling/themes). ## `CellStyle` ```ts twoslash import type { CellStyle } from "typed-xlsx"; const style: CellStyle = { font: { name: "Calibri", size: 11, bold: false, italic: false, underline: false, strike: false, color: { rgb: "000000" }, }, fill: { color: { rgb: "FFFFFF" }, }, border: { top: { style: "thin", color: { rgb: "CCCCCC" } }, right: { style: "thin", color: { rgb: "CCCCCC" } }, bottom: { style: "thin", color: { rgb: "CCCCCC" } }, left: { style: "thin", color: { rgb: "CCCCCC" } }, }, alignment: { horizontal: "left", vertical: "center", wrapText: false, }, numFmt: "General", }; ``` Every field is optional. Only provide the pieces you want to override. ## Static data-cell styles ```ts twoslash import { createExcelSchema, type CellStyle } from "typed-xlsx"; const moneyStyle: CellStyle = { numFmt: "$#,##0.00", alignment: { horizontal: "right" }, }; const dateStyle: CellStyle = { numFmt: "dd/mm/yyyy", alignment: { horizontal: "center" }, }; const schema = createExcelSchema<{ amount: number; createdAt: Date }>() .column("amount", { accessor: "amount", style: moneyStyle, }) .column("createdAt", { accessor: "createdAt", style: dateStyle, }) .build(); ``` ## Header styles Use `headerStyle` to style the header independently from the data cells. ```ts twoslash import { createExcelSchema, type CellStyle } from "typed-xlsx"; const darkHeader: CellStyle = { fill: { color: { rgb: "1E3A5F" } }, font: { bold: true, color: { rgb: "FFFFFF" } }, alignment: { horizontal: "center" }, }; const schema = createExcelSchema<{ name: string; amount: number }>() .column("name", { accessor: "name", headerStyle: darkHeader }) .column("amount", { accessor: "amount", headerStyle: darkHeader, style: { numFmt: "$#,##0.00" }, }) .build(); ``` ## Summary styles Summaries use the same `CellStyle` shape: - reducer summaries support static `style`, dynamic `style(value)`, and `conditionalStyle` - formula summaries support static `style` and `conditionalStyle` Use this page for the style object itself, then see [Conditional Styles](https://typed-xlsx.vercel.app/styling/conditional-styles) when the summary should stay live inside Excel. ## Border styles reference `border.{top,right,bottom,left}.style` accepts any of: `"thin"` · `"medium"` · `"thick"` · `"dashed"` · `"dotted"` · `"double"` · `"hair"` · `"dashDot"` · `"dashDotDot"` ## Number format strings `numFmt` follows standard Excel format syntax. Common patterns: | Format | Example output | | ------------------ | -------------- | | `"#,##0"` | 1,234 | | `"#,##0.00"` | 1,234.56 | | `"$#,##0.00"` | $1,234.56 | | `"0%"` | 75% | | `"0.00%"` | 75.00% | | `"dd/mm/yyyy"` | 31/12/2024 | | `"d mmm yyyy"` | 31 Dec 2024 | | `"yyyy-mm-dd"` | 2024-12-31 | | `"hh:mm:ss"` | 14:30:00 | | `"#,##0.00\" €\""` | 1,234.56 € | ## Column width Width is configured alongside the style fields. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ name: string; notes: string }>() .column("name", { accessor: "name", width: 25 }) .column("notes", { accessor: "notes", width: 60, style: { alignment: { wrapText: true } }, }) .build(); ``` Use `autoWidth: true` to let the engine compute width from the column data. Combine `minWidth` and `maxWidth` to bound the auto-computed value. # Dynamic Styles Pass a function to `style` when formatting depends on the source row at export time. If your schema declares `createExcelSchema()`, the same schema-level `context` object is also available here through `ctx`. It is shared across the whole schema, not reserved for `dynamic(...)`. The function receives: - `row` - `rowIndex` - `subRowIndex` - `ctx` This is the right model when JavaScript already knows the information needed to choose the style. ## Row-dependent styling ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Order = { status: "paid" | "pending" | "overdue"; amount: number; }; const statusColors: Record = { paid: "166534", pending: "A16207", overdue: "B42318", }; const schema = createExcelSchema() .column("status", { accessor: "status", style: ({ row }) => ({ font: { bold: row.status === "overdue", color: { rgb: statusColors[row.status] }, }, fill: { color: { rgb: row.status === "overdue" ? "FEF2F2" : "FFFFFF" }, }, }), }) .column("amount", { accessor: "amount", style: ({ row }) => ({ numFmt: "$#,##0.00", font: { bold: row.status === "overdue" }, }), }) .build(); ``` ## Context-aware styling ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Order = { amount: number; region: "EMEA" | "AMER" | "APAC"; }; type SchemaContext = { featuredRegion: Order["region"]; }; const schema = createExcelSchema() .column("region", { accessor: "region" }) .column("amount", { accessor: "amount", style: ({ row, ctx }) => ({ numFmt: "$#,##0.00", font: { bold: row.region === ctx.featuredRegion, }, fill: { color: { rgb: row.region === ctx.featuredRegion ? "EFF6FF" : "FFFFFF" }, }, }), }) .build(); ``` ## Sub-row-aware styling When an accessor expands into sub-rows, `subRowIndex` lets the style function align with the physical row being emitted. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Order = { items: Array<{ qty: number; fulfilled: boolean; unitPrice: number }>; }; const schema = createExcelSchema() .column("qty", { accessor: (row) => row.items.map((item) => item.qty), }) .column("lineTotal", { accessor: (row) => row.items.map((item) => item.qty * item.unitPrice), style: ({ row, subRowIndex }) => { const item = row.items[subRowIndex]; return { numFmt: "$#,##0.00", font: { color: { rgb: item?.fulfilled ? "166534" : "B42318" }, }, }; }, }) .build(); ``` ## Use dynamic styles when Use `style: ({ row, subRowIndex, ctx }) => ...` when: - the styling decision is already known during export - you want full JavaScript freedom - the workbook does not need to keep reevaluating the style after open If the style should remain live in Excel and react to formulas or user edits, use [Conditional Styles](https://typed-xlsx.vercel.app/styling/conditional-styles) instead. # Conditional Styles Use `conditionalStyle` when styling should stay live in Excel and react to formula results or later user edits. Unlike `style`, which is resolved during export, `conditionalStyle` emits native Excel conditional formatting rules for the column range. ## Why this is different from dynamic `style` - `style: ({ row, subRowIndex, ctx }) => ...` runs once during export in JavaScript - `conditionalStyle` runs later in Excel because the emitted workbook contains conditional formatting rules That makes `conditionalStyle` the right tool for: - styling formula columns by their computed result - highlighting cells that should react to manual edits in Excel - keeping workbook logic and workbook styling live together ## Formula-based conditional styling `conditionalStyle` reuses the same formula condition DSL as formula columns: - `refs.column(...)` - `fx.and(...)` - `fx.or(...)` - `fx.not(...)` - comparison operators like `.lt()`, `.gte()`, `.eq()` ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Deal = { amount: number; quota: number; status: "open" | "won" | "at-risk"; }; const schema = createExcelSchema() .column("amount", { accessor: "amount", style: { numFmt: "$#,##0.00", alignment: { horizontal: "right" }, }, }) .column("quota", { accessor: "quota", style: { numFmt: "$#,##0.00", alignment: { horizontal: "right" }, }, }) .column("status", { accessor: "status", }) .column("attainment", { formula: ({ refs, fx }) => fx.safeDiv(refs.column("amount"), refs.column("quota")), style: { numFmt: "0.0%", alignment: { horizontal: "right" }, }, conditionalStyle: (conditional) => conditional .when(({ refs }) => refs.column("attainment").lt(0.5), { fill: { color: { rgb: "FEE2E2" } }, font: { color: { rgb: "991B1B" }, bold: true }, }) .when( ({ refs, fx }) => fx.and(refs.column("attainment").gte(1), refs.column("status").eq("won")), { fill: { color: { rgb: "DCFCE7" } }, font: { color: { rgb: "166534" }, bold: true }, }, ), }) .build(); ``` ## Layering with base styles `style` remains the base style for the column. Matching conditional rules overlay on top of that base formatting. That means a column can keep: - number formats - alignment - borders while `conditionalStyle` changes only the fields you specify, such as `fill` and `font`. ## Summary cells Summary cells support `conditionalStyle` too. - reducer summaries support static `style`, dynamic `style(value)`, and `conditionalStyle` - formula summaries support static `style` and `conditionalStyle` For summary cells, the condition context is `({ cell, fx })` rather than `({ row, fx })`: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ amount: number }>() .column("amount", { accessor: "amount", summary: (summary) => [ summary.formula("sum", { style: { numFmt: "$#,##0.00", font: { bold: true }, }, conditionalStyle: (conditional) => conditional.when(({ cell }) => cell.current().gte(1000), { fill: { color: { rgb: "DCFCE7" } }, font: { color: { rgb: "166534" }, bold: true }, }), }), ], }) .build(); ``` # Formula Columns A formula column doesn't compute anything in JavaScript. It describes a formula, and typed-xlsx writes it into each cell with the right references. Excel computes the value when the file opens, and again whenever someone edits a cell it depends on. Formulas are optional. If the value doesn't need to update when someone edits the file, compute it in TypeScript with an accessor function instead; see [Computed in TypeScript or live in Excel](https://typed-xlsx.vercel.app/getting-started/introduction#computed-in-typescript-or-live-in-excel). ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type Deal = { dealId: string; contractValue: number; discountPct: number }; const deals = createExcelSchema() .column("dealId", { header: "Deal", accessor: "dealId" }) .column("contractValue", { header: "Contract", accessor: "contractValue" }) .column("discountPct", { header: "Discount", accessor: "discountPct" }) .column("netRevenue", { header: "Net revenue", formula: ({ refs, fx }) => fx.round( refs.column("contractValue").mul(fx.literal(1).sub(refs.column("discountPct"))), 2, ), }) .column("tier", { header: "Tier", formula: ({ refs, fx }) => fx.if(refs.column("netRevenue").gte(50000), "STRATEGIC", "GROWTH"), }) .build(); ``` `tier` reads `netRevenue`, which is itself a formula. Each row gets its own copy with the references resolved: ::sheet-figure --- align: - l - r - r - l - l columns: - Deal - Contract - Discount - Net revenue - Tier marks: - range: D2:D3 label: net revenue, from Contract and Discount on the same row - range: E2:E3 label: the tier, from the net revenue formula next to it rows: - - D-101 - 60000 - 0.1 - =ROUND((B2*(1-C2)),2) - =IF((D2>=50000),"STRATEGIC","GROWTH") - - D-102 - 24000 - 0 - =ROUND((B3*(1-C3)),2) - =IF((D3>=50000),"STRATEGIC","GROWTH") widths: - 60 - 78 - 78 - 166 - 276 --- :: ## Who sees the formula result typed-xlsx stores formulas in the file without a computed value. Excel, LibreOffice, Numbers and Google Sheets calculate them when the file opens. File previews (macOS Quick Look, Gmail or Outlook previews, Google Drive preview) and libraries that read stored values (pandas, openpyxl) show those cells as empty. If the reader needs the numbers without opening a spreadsheet app, compute the value in TypeScript instead: use an accessor function on the column, or a `summary` with `s.cell()`. ## The formula callback `formula` receives four helpers: | Helper | What it gives you | | ------ | ------------------------------------------------------------------------------------------------------------------------------- | | `refs` | references to other columns: `refs.column(id)`, and whole scopes with `refs.group(id)`, `refs.dynamic(id)` | | `fx` | functions: `round`, `abs`, `min`, `max`, `if`, `and`, `or`, `not`, `safeDiv`, `literal`, and scope aggregates | | `row` | the logical row, for `row.series(id)` on expanded rows (see [Row Model](https://typed-xlsx.vercel.app/core-concepts/row-model)) | | `ctx` | the schema [context](https://typed-xlsx.vercel.app/core-concepts/schema-context), for values chosen at export time | ## Reference earlier columns `refs.column(id)` points at another column's cell on the same row. Only columns declared earlier in the chain are accepted, and formula columns can reference other formula columns: ```ts twoslash // @errors: 2345 import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ qty: number; unitPrice: number }>() .column("total", { formula: ({ refs }) => refs.column("qty").mul(refs.column("unitPrice")), }) .column("qty", { accessor: "qty" }) .column("unitPrice", { accessor: "unitPrice" }) .build(); ``` Declaring `total` after `qty` and `unitPrice` fixes it. The order rule is what lets typed-xlsx guarantee a formula never points at a column that doesn't exist. The full rules, including groups and dynamic columns, are in [Scope & References](https://typed-xlsx.vercel.app/formulas/scope-and-references). ## Arithmetic and literals Every operand has `.add()`, `.sub()`, `.mul()` and `.div()`. Numbers can be passed directly; wrap a number in `fx.literal()` when it has to come first: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ amount: number; discount: number }>() .column("amount", { accessor: "amount" }) .column("discount", { accessor: "discount" }) .column("withVat", { formula: ({ refs }) => refs.column("amount").mul(1.2) }) .column("net", { formula: ({ refs, fx }) => fx.literal(1).sub(refs.column("discount")).mul(refs.column("amount")), }) .build(); ``` ## Functions | Call | Writes | | -------------------------------------------------------------------------------------------- | ---------------------------------------------- | | `fx.round(value, decimals?)` | `ROUND(value,decimals)`, 0 decimals by default | | `fx.abs(value)` | `ABS(value)` | | `fx.min(...values)`, `fx.max(...values)` | `MIN(…)`, `MAX(…)` | | `fx.if(condition, whenTrue, whenFalse)` | `IF(condition,whenTrue,whenFalse)` | | `fx.and(...)`, `fx.or(...)`, `fx.not(c)` | `AND(…)`, `OR(…)`, `NOT(…)` | | `fx.safeDiv(a, b, fallback?)` | `IF((b<>0),(a/b),fallback)`, fallback 0 | | `fx.sum`, `fx.average`, `fx.count`, `fx.min`, `fx.max` on `refs.group()` or `refs.dynamic()` | the function over every column of the scope | | `fx.literal(value)` | a constant, as an operand | ## Conditions Comparisons, `.eq()`, `.neq()`, `.gt()`, `.gte()`, `.lt()` and `.lte()`, return a condition you can combine with `.and()`, `.or()` and `.not()`, then pass to `fx.if()`: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ churnRisk: number; daysOverdue: number }>() .column("churnRisk", { accessor: "churnRisk" }) .column("daysOverdue", { accessor: "daysOverdue" }) .column("alert", { header: "Alert", formula: ({ refs, fx }) => fx.if( refs.column("churnRisk").gte(0.7).or(refs.column("daysOverdue").gt(30)), "ACTION REQUIRED", fx.if(refs.column("daysOverdue").gt(0), "MONITOR", "OK"), ), }) .build(); ``` ## Division that can't fail `fx.safeDiv(a, b)` writes `IF((b<>0),(a/b),0)`, so an empty or zero denominator shows 0 instead of `#DIV/0!`. Pass a fallback, or a stricter guard: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ revenue: number; cost: number }>() .column("revenue", { accessor: "revenue" }) .column("cost", { accessor: "cost" }) .column("marginPct", { formula: ({ refs, fx }) => fx.safeDiv(refs.column("revenue").sub(refs.column("cost")), refs.column("revenue"), { fallback: 0, when: ({ denominator }) => denominator.gt(0), }), style: { numFmt: "0.0%" }, }) .build(); ``` ## Totals across a group A group or dynamic scope can be aggregated as a whole, so a total keeps working when the scope gains columns: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ amer: number; apac: number; emea: number }>() .group("regions", { header: "Regions" }, (group) => { group.column("amer", { header: "AMER", accessor: "amer" }); group.column("apac", { header: "APAC", accessor: "apac" }); group.column("emea", { header: "EMEA", accessor: "emea" }); }) .column("total", { header: "Total", formula: ({ refs, fx }) => fx.sum(refs.group("regions")), }) .build(); ``` This writes `=SUM(A3,B3,C3)`. With [dynamic columns](https://typed-xlsx.vercel.app/columns/dynamic-columns), `fx.sum(refs.dynamic("regions"))` covers however many region columns the export generates. ## Formulas in Excel tables The same schema in [excel-table mode](https://typed-xlsx.vercel.app/core-concepts/schema-modes) writes structured references instead of A1 references, displayed by Excel as `=[@Qty]*[@Price]`. Nothing changes in your code. ## Styling from a formula `conditionalStyle` uses the same conditions to write native Excel conditional formats, so the color follows the value when someone edits the sheet. See [Conditional Styles](https://typed-xlsx.vercel.app/styling/conditional-styles). # Scope & References This page is the canonical reference for the formula DSL, formula scope, and mode-specific output. For a guided introduction with worked examples, see [Formula Columns](https://typed-xlsx.vercel.app/formulas/formula-columns). For the logical-row vs physical-row mental model, see [Row Model](https://typed-xlsx.vercel.app/core-concepts/row-model). ## Entry points Every formula column callback receives `{ row, refs, fx, ctx }`: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ qty: number; price: number }>() .column("qty", { accessor: "qty" }) .column("price", { accessor: "price" }) .column("total", { formula: ({ refs, fx }) => { // ^? return refs.column("qty").mul(refs.column("price")); }, }) .build(); ``` | Parameter | Type | Description | | --------- | ------------------- | ---------------------------------------------------------------------------------- | | `row` | `FormulaRowContext` | Row-level helpers like `row.series(...)` and `row.if(...)` | | `refs` | `FormulaRefs` | Scope selectors for `refs.column(...)`, `refs.group(...)`, and `refs.dynamic(...)` | | `fx` | `FormulaFunctions` | Spreadsheet functions (`ROUND`, `IF`, `SUM`, `AVERAGE`, …) | | `ctx` | Schema context type | The schema-level context object from `createExcelSchema()` | --- ## `refs`: scope selectors ### `refs.column(columnId)` References the value of another column in the same row. Returns a `FormulaOperand`. The column ID is type-checked. Referencing a non-existent column or a column declared after the current one is a TypeScript error. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ units: number; rate: number }>() .column("units", { accessor: "units" }) .column("rate", { accessor: "rate" }) .column("cost", { formula: ({ refs }) => refs.column("units").mul(refs.column("rate")), }) .build(); ``` ### Primitive literals String, number, and boolean primitives can be passed directly into formula operations. They are embedded as Excel literals automatically. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ amount: number }>() .column("amount", { accessor: "amount" }) .column("withVat", { formula: ({ refs }) => refs.column("amount").mul(1.2), }) .build(); ``` ### `refs.group(groupId)` and `refs.dynamic(dynamicId)` Return a scope reference that can be passed into aggregate helpers like `fx.sum(...)`, `fx.average(...)`, `fx.min(...)`, `fx.max(...)`, and `fx.count(...)`. Use `refs.group(...)` for structural groups created with `.group()`. Use `refs.dynamic(...)` for runtime-generated scopes created with `.dynamic()`. See [Column Groups](https://typed-xlsx.vercel.app/columns/column-groups) for structural groups and [Dynamic Columns](https://typed-xlsx.vercel.app/columns/dynamic-columns) for runtime-generated scopes. --- ## `row`: row context The `row` parameter provides row-level helpers that operate on the current logical row. ### `row.series(columnId)` References the full expanded physical-row span for a column within the current logical row. Returns a `FormulaSeriesContext` with `.sum()`, `.average()`, `.min()`, `.max()`, and `.count()`. Use `row.series(...)` for logical-row aggregates over expanded rows. ### `row.if(condition, whenTrue, whenFalse)` Inline `IF`: equivalent to `fx.if()`. Returns a `FormulaOperand`. Available directly on `row` for convenience. --- ## `FormulaOperand`: arithmetic and comparison A `FormulaOperand` is returned by `refs.column()` and most `fx.*` functions. It exposes arithmetic operators, comparison operators, and unary functions chainably. ### Arithmetic | Method | Excel equivalent | Example | | ------------- | ---------------- | ------------------------------------------------ | | `.add(right)` | `A + B` | `refs.column("a").add(refs.column("b"))` | | `.sub(right)` | `A - B` | `refs.column("a").sub(1)` | | `.mul(right)` | `A * B` | `refs.column("qty").mul(refs.column("price"))` | | `.div(right)` | `A / B` | `refs.column("total").div(refs.column("count"))` | `right` can be a `FormulaOperand`, a `FormulaExpr`, or a primitive (`string | number | boolean`). Primitives are automatically wrapped in a literal. For guarded division, use `fx.safeDiv(left, right, fallback?)` or `fx.safeDiv(left, right, { fallback, when })` instead of changing `.div()` semantics globally. ### Unary | Method | Excel equivalent | Example | | ------------ | ---------------- | ------------------------------ | | `.abs()` | `ABS(A)` | `refs.column("delta").abs()` | | `.round(n?)` | `ROUND(A, n)` | `refs.column("rate").round(4)` | `n` defaults to `0`. ### Comparison: returns `FormulaCondition` | Method | Excel equivalent | Example | | ------------- | ---------------- | ------------------------------------ | | `.eq(right)` | `A = B` | `refs.column("status").eq("active")` | | `.neq(right)` | `A <> B` | `refs.column("code").neq(0)` | | `.gt(right)` | `A > B` | `refs.column("score").gt(90)` | | `.gte(right)` | `A >= B` | `refs.column("mrr").gte(1000)` | | `.lt(right)` | `A < B` | `refs.column("age").lt(30)` | | `.lte(right)` | `A <= B` | `refs.column("risk").lte(0.5)` | --- ## `FormulaCondition`: boolean logic `FormulaCondition` is returned by comparison operators. It can be passed directly to `fx.if()`, or chained. ### Chaining | Method | Excel equivalent | Example | | ------------- | ---------------- | -------------------------------------------------------------- | | `.and(right)` | `AND(A, B)` | `refs.column("a").gt(0).and(refs.column("b").lt(100))` | | `.or(right)` | `OR(A, B)` | `refs.column("tier").eq("A").or(refs.column("mrr").gte(5000))` | | `.not()` | `NOT(A)` | `refs.column("active").eq(true).not()` | Conditions can be chained multiple times: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ score: number; tier: string; active: boolean }>() .column("score", { accessor: "score" }) .column("tier", { accessor: "tier" }) .column("active", { accessor: "active" }) .column("eligible", { formula: ({ refs, fx }) => fx.if( refs .column("active") .eq(true) .and(refs.column("score").gte(80)) .and(refs.column("tier").eq("premium")), "YES", "NO", ), }) .build(); ``` --- ## `fx`: formula functions ### `fx.if(condition, whenTrue, whenFalse)` ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ revenue: number; target: number }>() .column("revenue", { accessor: "revenue" }) .column("target", { accessor: "target" }) .column("status", { formula: ({ refs, fx }) => fx.if(refs.column("revenue").gte(refs.column("target")), "ON TRACK", "BEHIND"), }) .build(); ``` Nested `if` calls create `IF(…, …, IF(…))` chains: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ score: number }>() .column("score", { accessor: "score" }) .column("grade", { formula: ({ refs, fx }) => fx.if( refs.column("score").gte(90), "A", fx.if( refs.column("score").gte(75), "B", fx.if(refs.column("score").gte(60), "C", "F"), ), ), }) .build(); ``` ### `fx.safeDiv(numerator, denominator, fallback?)` Wraps a division in `IF(denominator<>0, numerator/denominator, fallback)`. `fallback` defaults to `0`. You can also pass an options object to customize the fallback and guard condition: - `fallback`: value returned when the guard fails - `when`: custom condition to use instead of `denominator <> 0` `when` may be either: - a direct condition expression - a callback that receives the already-supplied `numerator` and `denominator` operands ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ amount: number; quota: number }>() .column("amount", { accessor: "amount" }) .column("quota", { accessor: "quota" }) .column("attainment", { formula: ({ refs, fx }) => fx.safeDiv(refs.column("amount"), refs.column("quota")), }) .build(); ``` ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ revenue: number; cost: number }>() .column("revenue", { accessor: "revenue" }) .column("cost", { accessor: "cost" }) .column("marginPct", { formula: ({ refs, fx }) => fx.safeDiv(refs.column("revenue").sub(refs.column("cost")), refs.column("revenue"), { fallback: 0, when: ({ denominator }) => denominator.gt(0), }), }) .build(); ``` ### `fx.round(value, decimals?)` Wraps a value in `ROUND(value, n)`. `decimals` defaults to `0`. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ amount: number; rate: number }>() .column("amount", { accessor: "amount" }) .column("rate", { accessor: "rate" }) .column("result", { formula: ({ refs, fx }) => fx.round(refs.column("amount").mul(refs.column("rate")), 2), }) .build(); ``` ### `fx.abs(value)` Wraps a value in `ABS(value)`. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ baseline: number; current: number }>() .column("baseline", { accessor: "baseline" }) .column("current", { accessor: "current" }) .column("deviation", { formula: ({ refs, fx }) => fx.abs(refs.column("current").sub(refs.column("baseline"))), }) .build(); ``` ### `fx.min(...values)` and `fx.max(...values)` Accept any number of operands, literals, or conditions. ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ bid: number; ask: number; mid: number }>() .column("bid", { accessor: "bid" }) .column("ask", { accessor: "ask" }) .column("mid", { accessor: "mid" }) .column("spread", { formula: ({ refs, fx }) => fx.max(refs.column("ask").sub(refs.column("bid")), 0), }) .build(); ``` ### `fx.and(...conditions)` and `fx.or(...conditions)` Variadic `AND` / `OR`: accept multiple `FormulaCondition` or `FormulaExpr` values: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ a: number; b: number; c: number }>() .column("a", { accessor: "a" }) .column("b", { accessor: "b" }) .column("c", { accessor: "c" }) .column("allPositive", { formula: ({ refs, fx }) => fx.if( fx.and(refs.column("a").gt(0), refs.column("b").gt(0), refs.column("c").gt(0)), "YES", "NO", ), }) .build(); ``` ### `fx.not(condition)` Wraps a condition in `NOT(…)`: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; createExcelSchema<{ blocked: boolean }>() .column("blocked", { type: "checkbox", accessor: "blocked" }) .column("allowed", { formula: ({ refs, fx }) => fx.if(fx.not(refs.column("blocked").eq(true)), "ALLOWED", "BLOCKED"), }) .build(); ``` --- ## Scope aggregates When columns are declared inside a `.group()` or `.dynamic()`, a later formula column can aggregate all of that scope's columns by combining `refs.group(...)` or `refs.dynamic(...)` with `fx.sum(...)`, `fx.average(...)`, `fx.min(...)`, `fx.max(...)`, or `fx.count(...)`: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; type SalesRow = { q1: number; q2: number; q3: number; q4: number }; const schema = createExcelSchema() .group("quarters", (g) => { g.column("q1", { header: "Q1", accessor: "q1" }); g.column("q2", { header: "Q2", accessor: "q2" }); g.column("q3", { header: "Q3", accessor: "q3" }); g.column("q4", { header: "Q4", accessor: "q4" }); }) .column("annual", { header: "Annual", formula: ({ refs, fx }) => fx.sum(refs.group("quarters")), style: { font: { bold: true } }, }) .column("avgQuarter", { header: "Avg / Quarter", formula: ({ refs, fx }) => fx.round(fx.average(refs.group("quarters")), 0), }) .build(); ``` ### Available scope aggregates | Helper | Excel output | | ----------------- | ------------------------------------ | | `fx.sum(ref)` | Sum of scope columns in this row | | `fx.average(ref)` | Average of scope columns in this row | | `fx.min(ref)` | Minimum of scope columns in this row | | `fx.max(ref)` | Maximum of scope columns in this row | | `fx.count(ref)` | Count of non-empty scope columns | Scope aggregates work identically in report mode and excel-table mode: only the emitted cell reference style differs. --- ## Summary formulas Summary rows have their own formula context (`{ column, fx }`): see [Summary Rows](https://typed-xlsx.vercel.app/columns/summary-rows). --- ## Formula scope and mode output ### Logical rows and physical rows Report mode can expand one logical row into multiple physical worksheet rows. That row model is explained in depth in [Row Model](https://typed-xlsx.vercel.app/core-concepts/row-model), including when to use `refs.column(...)`, `row.series(...)`, `column.cells()`, and `column.rows()`. ### Predecessor-based scope Formula columns can only reference columns declared **before them** in the same schema. This rule is enforced by TypeScript at declaration time through the schema builder's accumulated type state. ### Group and dynamic scope Columns declared inside a `.group()` or `.dynamic()` follow the same predecessor rule, but with an outer scope and a local scope. ### A1 vs structured references The formula DSL is identical in both modes. What changes is the emitted Excel formula string: - report mode emits A1-style references based on physical worksheet position - excel-table mode emits structured references based on column headers ### Runtime validation TypeScript catches most scope violations at declaration time, but some errors can only be detected at output time: | Situation | Behavior | | -------------------------------------------------------------------------- | --------------------- | | Referenced column not declared yet | TypeScript error | | Referenced column excluded via `select.exclude` | Throws at output time | | Referenced column inside a group or dynamic scope, referenced from outside | TypeScript error | | Group ID referenced before the group is declared | TypeScript error | | Dynamic ID referenced before the dynamic scope is declared | TypeScript error | | Formula column in excel-table mode with sub-row accessor | Throws at output time | ### Practical rule of thumb - use `refs.column(...)` for per-physical-row formulas - use `row.series(...)` for per-logical-row formulas over expanded cells - use `column.cells()` in summaries when you want physical-row math - use `column.rows()` in summaries when you want logical-row math --- ## Type reference | Type | Description | | ----------------------- | --------------------------------------------------------------------------------------------------- | | `FormulaOperand` | Arithmetic result, supports `.add/sub/mul/div`, comparisons, `.abs/.round` | | `FormulaCondition` | Boolean result, supports `.and/.or/.not`, passable to `fx.if` | | `FormulaValue` | `string | number | boolean | FormulaOperand | FormulaExpr`: accepted everywhere a value is expected | | `FormulaConditionValue` | `FormulaCondition | FormulaExpr`: accepted in boolean positions | | `FormulaRowContext` | `{ series, if }`: the `row` parameter | | `FormulaRefs` | `{ column, group, dynamic }`: the `refs` parameter | | `FormulaFunctions` | `{ literal, if, round, abs, sum, average, count, min, max, and, or, not }`: the `fx` parameter | | `FormulaSeriesContext` | `{ sum, average, min, max, count }`: returned by `row.series()` | # Table Mode Excel table mode wraps your data in a native Excel `` object (`xl/tables/table1.xml` in the OOXML package). This is the same object you create in Excel when you press **Ctrl+T** or **Format as Table**. The result is a first-class Excel data table: sortable and filterable with native dropdowns, styled with a theme (Light, Medium, or Dark presets), optionally capped with a totals row, and referenced by name in formulas across the workbook. :workbook-preview{artifact="kitchen-sink" caption="A native Excel table with filters, banding and a totals row, from the kitchen-sink example." sheet="12 Native Excel Table"} ## Creating an excel-table schema Pass `{ mode: "excel-table" }` to `createExcelSchema`: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const dealSchema = createExcelSchema<{ dealId: string; account: string; stage: string; amount: number; closeDate: string; }>({ mode: "excel-table" }) .column("dealId", { header: "Deal ID", accessor: "dealId" }) .column("account", { header: "Account", accessor: "account" }) .column("stage", { header: "Stage", accessor: "stage" }) .column("amount", { header: "Amount", accessor: "amount", style: { numFmt: "$#,##0.00" }, }) .column("closeDate", { header: "Close Date", accessor: "closeDate" }) .build(); ``` The schema is now an `ExcelTableSchemaDefinition`. Pass it to a workbook builder the same way as a report schema: the builder detects the mode and routes it through the excel-table output path. ## Using the schema in a workbook ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; const dealSchema = createExcelSchema<{ dealId: string; account: string; amount: number; }>({ mode: "excel-table" }) .column("dealId", { header: "Deal ID", accessor: "dealId" }) .column("account", { header: "Account", accessor: "account" }) .column("amount", { header: "Amount", accessor: "amount", style: { numFmt: "$#,##0.00" }, }) .build(); const workbook = createWorkbook(); workbook.sheet("Pipeline").table("deals", { schema: dealSchema, rows: [ { dealId: "D-001", account: "Acme Corp", amount: 24000 }, { dealId: "D-002", account: "Globex Inc", amount: 18500 }, ], name: "Pipeline", // Excel table name (optional, defaults to the table ID) style: "TableStyleMedium2", // default autoFilter: true, // default for excel-table mode totalsRow: false, // default }); const buffer = workbook.toBuffer(); ``` ## Constraints vs report mode Excel table mode is intentionally more constrained. These constraints come from the OOXML spec: Excel simply doesn't allow them inside a `
` object: | Feature | Report mode | Excel table mode | | ------------------- | ----------- | --------------------------- | | Summary rows | Yes | No: use totals row | | Sub-row expansion | Yes | No: throws at output time | | Merged header cells | Yes | Structural groups stay flat | If you attempt to use a schema with sub-row expansion (an accessor that returns an array) in excel-table mode, the library throws at output time with a descriptive error. ## Formula columns in excel-table mode Formula columns work exactly as in report mode. The DSL is identical. The only difference is the emitted formula string: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ qty: number; unitPrice: number }>({ mode: "excel-table", }) .column("qty", { header: "Qty", accessor: "qty" }) .column("unitPrice", { header: "Unit Price", accessor: "unitPrice" }) .column("lineTotal", { header: "Line Total", // Emits: =[@Qty]*[@UnitPrice] (structured reference, not A1 style) formula: ({ refs }) => refs.column("qty").mul(refs.column("unitPrice")), style: { numFmt: "$#,##0.00" }, }) .build(); ``` Structured references (`[@Qty]`) remain correct when users insert rows, sort the table, or move it, which makes them more robust than A1 references in interactive Excel files. ## Column groups in excel-table mode Structural groups (`.group()`) and runtime-generated scopes (`.dynamic()`) are supported in excel-table mode with flat headers only. Runtime-generated columns are useful for grouped selection and scope formulas like `fx.sum(refs.dynamic("id"))`. Excel table mode still renders a flat single header row: there is no merged/group header band. One useful mental model is that you make two independent choices: 1. choose schema mode: report or excel-table 2. choose workbook builder: buffered or streaming ## Streaming support Excel-table schemas work with `createWorkbookStream()` the same way as report schemas. Commit row batches with `.commit({ rows })` and finalize with `.writeToFile()` or another output method. The excel-table XML is assembled after the last commit. ## AutoFilter AutoFilter adds native Excel filter and sort dropdowns to the column headers. | Mode | AutoFilter default | How to change | | ----------- | ------------------ | ------------------------------------------ | | Report | **Off** | Set `autoFilter: true` on the table input | | Excel table | **On** | Set `autoFilter: false` on the table input | ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; const schema = createExcelSchema<{ product: string; stock: number }>({ mode: "excel-table", }) .column("product", { accessor: "product" }) .column("stock", { accessor: "stock" }) .build(); const workbook = createWorkbook(); workbook.sheet("Inventory").table("inventory", { schema, rows: [{ product: "Widget A", stock: 120 }], autoFilter: false, }); ``` AutoFilter is silently disabled in report-mode tables that use sub-row expansion because Excel cannot filter over merged cells. # Table Styles Excel tables support 60 built-in style presets organized in three tiers: Light (21 variants), Medium (28 variants), and Dark (11 variants). These map directly to Excel's **Format as Table** style gallery. ## Setting a style Pass `style` to the table input when building a workbook: ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; const schema = createExcelSchema<{ name: string; amount: number }>({ mode: "excel-table" }) .column("name", { accessor: "name" }) .column("amount", { accessor: "amount", style: { numFmt: "$#,##0.00" } }) .build(); const workbook = createWorkbook(); workbook.sheet("Report").table("data", { schema, rows: [{ name: "Acme Corp", amount: 12500 }], style: "TableStyleMedium2", // Default }); const buffer = workbook.toBuffer(); ``` The default is `"TableStyleMedium2"`: the blue banded-row style that Excel applies by default. ## Style reference ### Light styles `TableStyleLight1` through `TableStyleLight21`: no fill on data rows, colored header. ### Medium styles `TableStyleMedium1` through `TableStyleMedium28`: banded rows with a colored header. The most commonly used tier. Highlights: The first seven styles of a tier follow the default Office palette: 1 dark gray, 2 blue, 3 orange, 4 gray, 5 gold, 6 light blue, 7 green. For example: - `TableStyleMedium2`: blue, Excel's default - `TableStyleMedium3`: orange - `TableStyleMedium7`: green ### Dark styles `TableStyleDark1` through `TableStyleDark11`: dark header and banded rows. Use for high-contrast dashboards or executive reports. ## Combining with per-cell styles Native table style sets the banded-row background and header fill. Per-cell `style` on columns applies on top of the table style: you can override font, number format, alignment, and borders at the cell level without affecting the table theme: For the full column styling model, see [Cell Styles](https://typed-xlsx.vercel.app/styling/cell-styles), [Dynamic Styles](https://typed-xlsx.vercel.app/styling/dynamic-styles), and [Conditional Styles](https://typed-xlsx.vercel.app/styling/conditional-styles). ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; const schema = createExcelSchema<{ account: string; mrr: number; churnRisk: number; }>({ mode: "excel-table" }) .column("account", { header: "Account", accessor: "account" }) .column("mrr", { header: "MRR", accessor: "mrr", style: { numFmt: "$#,##0.00" }, }) .column("churnRisk", { header: "Churn Risk", accessor: "churnRisk", // Per-row conditional styling on top of the table theme style: ({ row }) => ({ numFmt: "0%", font: { bold: row.churnRisk >= 0.7, color: { rgb: row.churnRisk >= 0.7 ? "B42318" : "166534" }, }, }), }) .build(); const workbook = createWorkbook(); workbook.sheet("Accounts").table("accounts", { schema, rows: [ { account: "Acme Corp", mrr: 4200, churnRisk: 0.15 }, { account: "Globex Inc", mrr: 850, churnRisk: 0.82 }, ], style: "TableStyleMedium6", }); const buffer = workbook.toBuffer(); ``` # Totals Row Excel tables support a native totals row: a pinned row at the bottom of the table that shows aggregate values. Clicking the totals row cell in Excel exposes a dropdown to change the aggregate function. When the user filters or sorts the table, the totals update automatically. This is the excel-table mode alternative to [summary rows](https://typed-xlsx.vercel.app/columns/summary-rows) in report mode. ## Enabling the totals row Set `totalsRow: true` on the table input: ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; const schema = createExcelSchema<{ orderId: string; customer: string; amount: number; units: number; }>({ mode: "excel-table" }) .column("orderId", { header: "Order ID", accessor: "orderId" }) .column("customer", { header: "Customer", accessor: "customer" }) .column("amount", { header: "Amount", accessor: "amount", style: { numFmt: "$#,##0.00" }, }) .column("units", { header: "Units", accessor: "units" }) .build(); const workbook = createWorkbook(); workbook.sheet("Orders").table("orders", { schema, rows: [ { orderId: "ORD-001", customer: "Acme Corp", amount: 4200, units: 12 }, { orderId: "ORD-002", customer: "Globex Inc", amount: 8750, units: 25 }, ], totalsRow: true, // Enables the totals row }); const buffer = workbook.toBuffer(); ``` With `totalsRow: true` and no per-column configuration, the totals row is visible but all cells are blank: Excel shows the row without any aggregate. ## Per-column totals Configure what each column shows in the totals row using the `totalsRow` option on the column definition: ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; const schema = createExcelSchema<{ dealId: string; account: string; stage: string; amount: number; probability: number; }>({ mode: "excel-table" }) .column("dealId", { header: "Deal ID", accessor: "dealId", totalsRow: { label: "Totals" }, // Static label in the totals cell }) .column("account", { header: "Account", accessor: "account", totalsRow: { function: "count" }, // COUNT of non-empty cells }) .column("stage", { accessor: "stage", // No totalsRow: cell is blank }) .column("amount", { header: "Amount", accessor: "amount", style: { numFmt: "$#,##0.00" }, totalsRow: { function: "sum" }, // SUM of all amounts }) .column("probability", { header: "Probability", accessor: "probability", style: { numFmt: "0%" }, totalsRow: { function: "average" }, // AVERAGE probability }) .build(); const workbook = createWorkbook(); workbook.sheet("Pipeline").table("deals", { schema, rows: [ { dealId: "D-001", account: "Acme", stage: "Proposal", amount: 24000, probability: 0.6, }, { dealId: "D-002", account: "Globex", stage: "Closed Won", amount: 18500, probability: 1, }, ], totalsRow: true, style: "TableStyleMedium6", }); const buffer = workbook.toBuffer(); ``` ## Aggregate functions | Function | Description | | ------------- | --------------------------------- | | `"sum"` | Sum of all values in the column | | `"average"` | Average of all values | | `"count"` | Count of non-empty cells | | `"countNums"` | Count of cells containing numbers | | `"min"` | Minimum value | | `"max"` | Maximum value | | `"stdDev"` | Standard deviation | | `"var"` | Variance | All aggregate functions use Excel's `SUBTOTAL()` under the hood: they respect any active autoFilter so the total updates when rows are filtered. ## Static label `{ label: "string" }` places a static text label in the totals cell. This does not aggregate anything: it is purely decorative. Use it to label the row on the first or last text column: ```ts twoslash import { createExcelSchema } from "typed-xlsx"; const schema = createExcelSchema<{ name: string; revenue: number }>({ mode: "excel-table" }) .column("name", { accessor: "name", totalsRow: { label: "TOTAL" }, }) .column("revenue", { accessor: "revenue", style: { numFmt: "$#,##0.00" }, totalsRow: { function: "sum" }, }) .build(); ``` ## Worked example: SaaS metrics dashboard A subscription analytics table with meaningful aggregates on every relevant column: ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; const metricsSchema = createExcelSchema<{ workspace: string; plan: string; mrr: number; seats: number; activatedSeats: number; churnRisk: number; }>({ mode: "excel-table" }) .column("workspace", { header: "Workspace", accessor: "workspace", totalsRow: { label: "Totals" }, }) .column("plan", { header: "Plan", accessor: "plan", totalsRow: { function: "count" }, }) .column("mrr", { header: "MRR", accessor: "mrr", style: { numFmt: "$#,##0.00" }, totalsRow: { function: "sum" }, }) .column("seats", { header: "Seats", accessor: "seats", totalsRow: { function: "sum" }, }) .column("activatedSeats", { header: "Activated", accessor: "activatedSeats", totalsRow: { function: "sum" }, }) .column("churnRisk", { header: "Churn Risk", accessor: "churnRisk", style: { numFmt: "0%" }, totalsRow: { function: "average" }, }) .build(); ``` # Charts Charts plot the tables your workbook already contains. A chart is a list of **marks** (bars, lines, areas, scatter points or pie slices), and each mark reads one table through `refs`, the same way a formula reads columns. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Month = { month: string; revenue: number; cost: number }; type Target = { month: string; target: number }; const sales = createExcelSchema() .column("month", { header: "Month", accessor: "month" }) .column("revenue", { header: "Revenue", accessor: "revenue", style: { numFmt: "#,##0" } }) .column("cost", { header: "Cost", accessor: "cost", style: { numFmt: "#,##0" } }) .build(); const targets = createExcelSchema() .column("month", { header: "Month", accessor: "month" }) .column("target", { header: "Target", accessor: "target" }) .build(); const salesRows: Month[] = [ { month: "Jan", revenue: 182000, cost: 121000 }, { month: "Feb", revenue: 195000, cost: 128000 }, ]; const targetRows: Target[] = [ { month: "Jan", target: 180000 }, { month: "Feb", target: 190000 }, ]; // ---cut--- createWorkbook() .sheet("2026") .table("sales", { schema: sales, rows: salesRows }) .table("targets", { schema: targets, rows: targetRows }) .chart("revenue", ({ refs }) => ({ title: "Revenue vs target", marks: [ refs.table("sales").bar({ x: "month", y: "revenue" }), refs.table("targets").line({ x: "month", y: "target", name: "Target" }), ], })); ``` The result is a native Excel chart. It points at the table's cells, so it follows edits to the data and readers can restyle it in Excel. ## How charts reach tables `.chart()` sits in the same chain as `.sheet()` and `.table()`: - `refs.table(id)` reads a table declared earlier **on the current sheet**. - `refs.sheet(name).table(id)` reads a table declared earlier **on another sheet**. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Month = { month: string; revenue: number }; const sales = createExcelSchema() .column("month", { header: "Month", accessor: "month" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .build(); const rows2026: Month[] = [{ month: "Jan", revenue: 182000 }]; const rows2025: Month[] = [{ month: "Jan", revenue: 161000 }]; // ---cut--- createWorkbook() .sheet("2026") .table("sales", { schema: sales, rows: rows2026 }) .sheet("2025") .table("sales", { schema: sales, rows: rows2025 }) .sheet("Dashboard") .chart("yearOverYear", ({ refs }) => ({ title: "Revenue 2026 vs 2025", marks: [ refs.sheet("2026").table("sales").bar({ x: "month", y: "revenue", name: "2026" }), refs.sheet("2025").table("sales").bar({ x: "month", y: "revenue", name: "2025" }), ], })); ``` Table ids only need to be unique within a sheet, so both sheets above can have a `sales` table. Sheet names must be unique in the chain. ::callout{type="info"} `refs` only sees what the chain declared before the chart. Calling `workbook.sheet(name)` in separate statements still works for tables, but charts that combine tables need one unbroken chain. :: ## What TypeScript checks Every name is checked against the table it comes from, after that table's `select` is applied. Value columns must hold numbers: the schema remembers what each column writes into its cells, including the result of a `transform` and of a formula. ```ts twoslash // @errors: 2322 import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Month = { month: string; revenue: number }; const sales = createExcelSchema() .column("month", { header: "Month", accessor: "month" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .column("label", { accessor: "revenue", transform: ({ value }) => `${value} €` }) .build(); // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows: [] }) .chart("revenue", ({ refs }) => ({ // "label" holds text after its transform, so it cannot be plotted marks: [refs.table("sales").bar({ x: "month", y: "label" })], })); ``` The compiler also rejects: | Mistake | Message | | ------------------------------------------------------------------ | ------------------------------------------------------------------------------- | | A column excluded by the table's `select` | the id is not offered | | A table declared later, or on another sheet without `refs.sheet()` | the id is not offered | | A duplicate sheet name or table id | `✗ sheet "2026" already exists`, `✗ table "sales" already exists on this sheet` | | A pie mixed with other marks | `✗ a pie or doughnut chart takes a single mark` | | Scatter or horizontal-bar marks mixed with other marks | `✗ scatter marks cannot share a chart with other marks` | | Marks whose x columns hold different kinds of values | `✗ marks disagree on the x axis: dates` | ## How marks line up Marks from different tables line up **row by row on their own x column**. In the first example, the target line's months come from the `targets` table, and they must match the `sales` months in the same order. TypeScript checks that both x columns hold the same kind of value. When the workbook is written, the library compares the values themselves and stops with a clear error instead of drawing a misleading chart: ```txt Chart "revenue": "2026/targets" row 2 is "Mar" but "2026/sales" row 2 is "Feb". Marks line up row by row on their x column. ``` ## What gets plotted - Only body rows: titles, headers, summary rows and native totals rows stay out of the chart. - Series are named after their column header, linked to the header cell. Pass `name` to override it. - Formula columns are plotted too. Excel computes their values when the file opens. - A table with no rows gives an empty chart instead of an error. - Tables that expand rows into sub-rows (array accessors) cannot be charted. ## Placement and size Without `at`, a chart goes to the right of the sheet's tables, and further charts stack below it. Sheets without tables start at `B2`. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Month = { month: string; revenue: number }; const sales = createExcelSchema() .column("month", { accessor: "month" }) .column("revenue", { accessor: "revenue" }) .build(); // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows: [] }) .chart("revenue", ({ refs }) => ({ at: "H2", size: { width: 720, height: 360 }, marks: [refs.table("sales").line({ x: "month", y: "revenue" })], })); ``` `size` is in pixels, so the chart keeps its size whatever the column widths. ## Streaming With `createWorkbookStream()`, each streamed table creates marks directly, and `sheet.chart()` takes the chart as an object. Charts are written when the workbook is finalized, so they can read tables of any sheet. ```ts twoslash import { createExcelSchema, createWorkbookStream } from "typed-xlsx"; type Month = { month: string; revenue: number; cost: number }; const schema = createExcelSchema() .column("month", { header: "Month", accessor: "month" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .column("cost", { header: "Cost", accessor: "cost" }) .build(); declare const batches: AsyncIterable; // ---cut--- const workbook = createWorkbookStream(); const monthly = await workbook.sheet("Monthly").table("monthly", { schema }); for await (const rows of batches) { await monthly.commit({ rows }); } workbook.sheet("Dashboard").chart("trend", { title: "Revenue and cost", marks: [monthly.line({ x: "month", y: ["revenue", "cost"] })], }); await workbook.writeToFile("./monthly.xlsx"); ``` Streamed rows are gone by the time charts are written, so streamed charts carry no cached values (Excel computes them on open), and marks from different tables are only checked to have the same number of rows. # Marks A mark is one layer of a chart. Create it from a table with `refs.table(id)`, then name the table's columns: `x` for categories and `y` for the values to plot. `y` only accepts columns that hold numbers. ## Bar ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Month = { month: string; revenue: number; cost: number }; const sales = createExcelSchema() .column("month", { header: "Month", accessor: "month" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .column("cost", { header: "Cost", accessor: "cost" }) .build(); // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows: [] }) .chart("grouped", ({ refs }) => ({ marks: [refs.table("sales").bar({ x: "month", y: ["revenue", "cost"] })], })) .chart("stacked", ({ refs }) => ({ marks: [refs.table("sales").bar({ x: "month", y: ["revenue", "cost"], stack: true })], })) .chart("ranked", ({ refs }) => ({ marks: [refs.table("sales").bar({ x: "month", y: "revenue", horizontal: true })], })); ``` | Option | Effect | | ------------ | --------------------------------------------------------------------------------------------- | | `stack` | `true` stacks the series, `"percent"` stacks them as shares of 100% | | `horizontal` | Draws bars sideways, first row on top. Horizontal bars cannot share a chart with other marks. | | `gap` | Space between bar groups, as a percentage of a bar's width (0-500, default 150) | ## Line and area ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Month = { month: string; revenue: number; cost: number; margin: number }; const sales = createExcelSchema() .column("month", { header: "Month", accessor: "month" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .column("cost", { header: "Cost", accessor: "cost" }) .column("margin", { header: "Margin", accessor: "margin" }) .build(); // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows: [] }) .chart("trend", ({ refs }) => ({ marks: [ refs.table("sales").line({ x: "month", y: "revenue", smooth: true }), refs.table("sales").line({ x: "month", y: "cost", dashed: true, markers: false }), ], })) .chart("composition", ({ refs }) => ({ marks: [refs.table("sales").area({ x: "month", y: ["cost", "margin"], stack: true })], })); ``` Lines take `smooth`, `markers` (on by default), `dashed`, `width` (in points) and `stack`. Areas take `stack`. ## Scatter A scatter mark places each row by two numbers, so its `x` must be numeric too. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Deal = { name: string; discount: number; winRate: number }; const deals = createExcelSchema() .column("name", { header: "Deal", accessor: "name" }) .column("discount", { header: "Discount", accessor: "discount", style: { numFmt: "0%" } }) .column("winRate", { header: "Win rate", accessor: "winRate", style: { numFmt: "0%" } }) .build(); // ---cut--- createWorkbook() .sheet("Deals") .table("deals", { schema: deals, rows: [] }) .chart("discounts", ({ refs }) => ({ title: "Discount vs win rate", marks: [refs.table("deals").scatter({ x: "discount", y: "winRate" })], })); ``` Pass `lines: true` to join the points. Scatter marks cannot share a chart with other marks. ## Pie and doughnut Pies take `label` and `value` instead of `x` and `y`. A pie or doughnut is the only mark of its chart. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Region = { region: string; revenue: number }; const regions = createExcelSchema() .column("region", { header: "Region", accessor: "region" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .build(); // ---cut--- createWorkbook() .sheet("Regions") .table("regions", { schema: regions, rows: [] }) .chart("share", ({ refs }) => ({ marks: [ refs.table("regions").pie({ label: "region", value: "revenue", labels: { percent: true } }), ], })) .chart("shareRing", ({ refs }) => ({ marks: [refs.table("regions").doughnut({ label: "region", value: "revenue", hole: 60 })], })); ``` `colors` sets the slice colors in row order. ## Several series `y` takes one column or a list. Use `{ column, name, color }` to label or color one series of a list; `name` and `color` on the mark itself only apply when `y` is a single column. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Month = { month: string; revenue: number; cost: number }; const sales = createExcelSchema() .column("month", { header: "Month", accessor: "month" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .column("cost", { header: "Cost", accessor: "cost" }) .build(); // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows: [] }) .chart("series", ({ refs }) => ({ marks: [ refs.table("sales").bar({ x: "month", y: ["revenue", { column: "cost", name: "Costs", color: "#94A3B8" }], }), ], })); ``` ## Dynamic columns Columns generated by a [dynamic block](https://typed-xlsx.vercel.app/columns/dynamic-columns) can be plotted in two directions. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Territory = { territory: string; byMonth: Record }; const territories = createExcelSchema() .column("territory", { header: "Territory", accessor: "territory" }) .dynamic("months", (builder, { ctx }) => { for (const month of ctx.months) { builder.column(month, { header: month, accessor: (row) => row.byMonth[month] ?? 0 }); } }) .build(); // ---cut--- createWorkbook() .sheet("Territories") .table("territories", { schema: territories, rows: [], context: { months: ["Jan", "Feb"] } }) // Months on the x axis, one line per territory row .chart("trend", ({ refs }) => ({ marks: [refs.table("territories").line({ x: { dynamic: "months" }, series: "territory" })], })) // Territories on the x axis, one stacked series per month column .chart("mix", ({ refs }) => ({ marks: [ refs.table("territories").bar({ x: "territory", y: { dynamic: "months" }, stack: true }), ], })); ``` With `x: { dynamic }`, each row becomes a series named by the `series` column, so a chart holds at most 255 rows. ## Combining marks Bars, lines and areas can share a chart when their x columns hold the same kind of value. Marks are drawn in order, and `axis: "y2"` puts a mark on a second value axis on the right. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Month = { month: string; revenue: number; marginRate: number }; const sales = createExcelSchema() .column("month", { header: "Month", accessor: "month" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .column("marginRate", { header: "Margin %", accessor: "marginRate", style: { numFmt: "0%" } }) .build(); // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows: [] }) .chart("combo", ({ refs }) => ({ marks: [ refs.table("sales").bar({ x: "month", y: "revenue" }), refs.table("sales").line({ x: "month", y: "marginRate", axis: "y2" }), ], axes: { y2: { format: "0%" } }, })); ``` # Axes and Styling Chart-level options sit next to `marks`. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Month = { month: string; revenue: number; target: number; marginRate: number }; const sales = createExcelSchema() .column("month", { header: "Month", accessor: "month" }) .column("revenue", { header: "Revenue", accessor: "revenue", style: { numFmt: "#,##0" } }) .column("target", { header: "Target", accessor: "target", style: { numFmt: "#,##0" } }) .column("marginRate", { header: "Margin %", accessor: "marginRate", style: { numFmt: "0%" } }) .build(); // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows: [] }) .chart("performance", ({ refs }) => ({ title: "Revenue, target and margin", marks: [ refs.table("sales").bar({ x: "month", y: "revenue", color: "#2563EB" }), refs.table("sales").line({ x: "month", y: "target", color: "#94A3B8", dashed: true }), refs.table("sales").line({ x: "month", y: "marginRate", color: "#F59E0B", axis: "y2" }), ], axes: { y: { title: "EUR", min: 0 }, y2: { title: "Margin", format: "0%" }, }, legend: "top", })); ``` ## Axes `axes.x` is the category axis (or the horizontal value axis of a scatter chart), `axes.y` the main value axis, and `axes.y2` the secondary axis used by marks with `axis: "y2"`. | Option | Applies to | Effect | | ------------ | ---------- | ----------------------------------------------- | | `title` | all | Axis title | | `hidden` | all | Hides the axis | | `gridlines` | all | Major gridlines. On by default for `y` | | `reverse` | all | Reverses the order | | `min`, `max` | value axes | Fixed bounds | | `step` | value axes | Distance between ticks | | `format` | value axes | Excel number format such as `"#,##0"` or `"0%"` | | `logBase` | value axes | Logarithmic scale, such as `10` | Without `format`, axes use the number format of the plotted cells, so a column styled with `numFmt: "0%"` gets a percentage axis. ## Legend The legend shows at the bottom when a chart has several series or slices, and is hidden for a single series. Set `legend` to `"top"`, `"bottom"`, `"left"`, `"right"`, or `false`. ## Colors Set `color` on a mark (single series) or on a `{ column, color }` entry, as a hex string such as `"#2563EB"`. A chart-level `colors` list colors the remaining series in order, and the slices of a pie. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Region = { region: string; revenue: number }; const regions = createExcelSchema() .column("region", { header: "Region", accessor: "region" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .build(); // ---cut--- createWorkbook() .sheet("Regions") .table("regions", { schema: regions, rows: [] }) .chart("share", ({ refs }) => ({ marks: [refs.table("regions").pie({ label: "region", value: "revenue" })], colors: ["#2563EB", "#10B981", "#F59E0B", "#8B5CF6"], })); ``` Without colors, Excel uses the workbook theme's palette. ## Data labels `labels: true` shows each point's value. Pass an object to choose what to show: ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Region = { region: string; revenue: number }; const regions = createExcelSchema() .column("region", { header: "Region", accessor: "region" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .build(); // ---cut--- createWorkbook() .sheet("Regions") .table("regions", { schema: regions, rows: [] }) .chart("ranked", ({ refs }) => ({ marks: [ refs.table("regions").bar({ x: "region", y: "revenue", horizontal: true, labels: { value: true, format: "#,##0" }, }), ], })); ``` | Option | Effect | | ---------- | ---------------------------------------------- | | `value` | The value (on by default) | | `category` | The category or slice label | | `series` | The series name | | `percent` | The share of the total, for pies and doughnuts | | `format` | Number format of the labels | # Pivot Tables A pivot table summarizes a table: one row per region, one column per month, the sum of revenue in each cell. Readers can then filter it, drag fields around or switch the sum to an average in Excel. `refs.table(id).pivot(...)` describes a pivot over a table declared earlier in the chain, and `.pivot(id, ...)` places it on a sheet. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Sale = { region: string; month: string; channel: string; revenue: number }; const sales = createExcelSchema() .column("region", { header: "Region", accessor: "region" }) .column("month", { header: "Month", accessor: "month" }) .column("channel", { header: "Channel", accessor: "channel" }) .column("revenue", { header: "Revenue", accessor: "revenue", style: { numFmt: "#,##0" } }) .build(); const rows: Sale[] = []; // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows }) .sheet("Analysis") .pivot("byRegion", ({ refs }) => refs .sheet("Sales") .table("sales") .pivot({ rows: ["region"], columns: ["month"], values: { revenue: "sum" }, filters: ["channel"], }), ); ``` ## Options | Option | Effect | | ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `rows` | Fields listed down the left, outermost first, or a [date grouped by year, quarter or month](https://typed-xlsx.vercel.app/#dates). Inner items are indented under their outer item, which shows the subtotal. | | `columns` | One field spread across the top, one column per item. It can be a grouped date. | | `values` | What to aggregate, keyed by column, as an amount or a [percentage of a total](https://typed-xlsx.vercel.app/#percentages). | | `filters` | Fields shown above the pivot, which readers use to filter it. | | `sort` | `"ascending"` (default), `"descending"`, or `"source"` for the order rows first appear in. | | `totals` | Grand totals. `false` hides both, or `{ row: false }` hides the bottom row, `{ column: false }` the right column. | | `style` | Built-in pivot style such as `"PivotStyleMedium9"`. | | `theme` | A [theme](https://typed-xlsx.vercel.app/#styles-and-themes) whose pivot slots style the pivot. Defaults to the source table's theme. | | `banded` | Banded rows (`true`), or `{ rows, columns }`. | | `grandTotalLabel` | Label of the grand totals. Defaults to `"Grand Total"`. | | `at` | Top-left cell, filters included. Defaults to below the sheet's tables and pivots, keeping two rows free above the pivot as Excel does. An `at` that overlaps a table or another pivot throws. | ## Values Each key of `values` is a column; its value is the aggregation. Use an object to name or format it, and a list to aggregate a column several ways. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Sale = { region: string; product: string; revenue: number; orders: number }; const sales = createExcelSchema() .column("region", { header: "Region", accessor: "region" }) .column("product", { header: "Product", accessor: "product" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .column("orders", { header: "Orders", accessor: "orders" }) .build(); // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows: [] }) .pivot("summary", ({ refs }) => refs.table("sales").pivot({ rows: ["region", "product"], values: { revenue: ["sum", { aggregate: "average", name: "Average sale", format: "#,##0.00" }], orders: { aggregate: "max", name: "Biggest order" }, product: "count", }, }), ); ``` The aggregations are `sum`, `average`, `min`, `max` and `count`. Only `count` works on text, dates or booleans. Values are named "Sum of Revenue", "Count of Product", and so on, unless you pass `name`. Sums, averages, minimums and maximums use the source column's number format unless you pass `format`. ## Percentages `show` turns a value into its share of a total. The pivot keeps the amounts underneath, so the percentages follow when readers filter it. | `show` | Each cell divided by | Adds up to 100% | | ------------------- | ------------------------ | --------------- | | `"percentOfTotal"` | the grand total | the whole pivot | | `"percentOfRow"` | its row's grand total | each row | | `"percentOfColumn"` | its column's grand total | each column | ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Sale = { region: string; channel: string; revenue: number }; const sales = createExcelSchema() .column("region", { header: "Region", accessor: "region" }) .column("channel", { header: "Channel", accessor: "channel" }) .column("revenue", { header: "Revenue", accessor: "revenue", style: { numFmt: "#,##0" } }) .build(); // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows: [] }) .pivot("channelMix", ({ refs }) => refs.table("sales").pivot({ rows: ["region"], columns: ["channel"], values: { // The amount, then each channel's share of the region's revenue. revenue: ["sum", { aggregate: "sum", show: "percentOfRow" }], }, }), ); ``` Percentages are formatted `"0.0%"` unless you pass `format`, and named "Sum of Revenue (% of row)" unless you pass `name`. Without a column field every row is 100%, so `"percentOfRow"` needs one: TypeScript says `✗ "percentOfRow" needs a column field`. ## Dates A date column can be grouped by `"year"`, `"quarter"` or `"month"`. Rows can nest several units, from the largest: `["year", "quarter", "month"]` lists years, the quarters of each year, then their months. Readers can collapse a year or a quarter in Excel. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Sale = { orderedAt: Date; region: string; revenue: number }; const sales = createExcelSchema() .column("orderedAt", { header: "Ordered", accessor: "orderedAt", style: { numFmt: "yyyy-mm-dd" }, }) .column("region", { header: "Region", accessor: "region" }) .column("revenue", { header: "Revenue", accessor: "revenue", style: { numFmt: "#,##0" } }) .build(); // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows: [] }) .sheet("Analysis") .pivot("byMonth", ({ refs }) => refs .sheet("Sales") .table("sales") .pivot({ rows: [{ field: "orderedAt", by: ["year", "month"] }], columns: ["region"], values: { revenue: "sum" }, }), ) .pivot("byYear", ({ refs }) => refs .sheet("Sales") .table("sales") .pivot({ rows: ["region"], columns: [{ field: "orderedAt", by: "year" }], values: { revenue: { aggregate: "sum", show: "percentOfColumn" } }, }), ); ``` - A column or a filter takes one unit. Rows take one or several. - `by: "month"` alone puts January of every year together. Add `"year"` to keep years apart. - Groups follow the calendar whatever `sort` says, and dates are grouped in UTC, as they are written. - Excel names the groups in the reader's language when it refreshes the pivot: "Qtr1" and "Jan" become "Trimestre1" and "janv" in French. - Every row needs a date. With a single blank cell, Excel silently drops the grouping and lists each date. So only date columns that cannot be blank can be grouped, such as a required date or one with a `defaultValue`, and a blank found while writing throws an error. ## Styles and themes A pivot picks its look in this order: 1. `style`: one of Excel's built-in pivot styles, such as `"PivotStyleMedium9"`. 2. `theme`: a [spreadsheet theme](https://typed-xlsx.vercel.app/styling/themes). 3. The theme of its source table, from the table or its schema. 4. The classic theme, which tables without a theme use too. So a pivot always matches the tables around it without extra code, and `style` is there when you want one of Excel's built-in looks instead. A theme styles pivots through six slots, each bound by default to a table slot of the same theme: | Slot | Styles | Bound by default to | | ----------------- | ------------------------------------------------------------- | ------------------------------------ | | `pivotBody` | Every cell. Its border is drawn around each cell. | `cellBase` | | `pivotFrame` | The border around the whole pivot. | nothing: the body's border | | `pivotHeader` | Header rows and filter labels: colours and fonts. | `header`, without its border | | `pivotSubheading` | Rows that hold a subtotal, such as a year above its quarters. | bold text | | `pivotTotal` | The grand total row and column. | `summary` | | `pivotStripe` | Banded rows or columns, when the pivot sets `banded`. | the `stripeFill` token, a light grey | Override a slot like any other, with `extend`. The override sits on the binding, so `pivotHeader: { font: { italic: true } }` keeps the header's fill and still follows it. ```ts twoslash import { createExcelSchema, createWorkbook, spreadsheetThemes } from "typed-xlsx"; type Sale = { region: string; product: string; revenue: number }; const sales = createExcelSchema() .column("region", { header: "Region", accessor: "region" }) .column("product", { header: "Product", accessor: "product" }) .column("revenue", { header: "Revenue", accessor: "revenue", style: { numFmt: "#,##0" } }) .build(); // ---cut--- const navy = { style: "medium" as const, color: { rgb: "0B1220" } }; const line = { style: "thin" as const, color: { rgb: "CBD5E1" } }; const reportTheme = spreadsheetThemes.slate.extend({ slots: { pivotFrame: { border: { top: navy, right: navy, bottom: navy, left: navy } }, pivotBody: { border: { top: line, right: line, bottom: line, left: line } }, pivotTotal: { fill: { color: { rgb: "C7D2FE" } } }, }, }); createWorkbook() .sheet("Sales") // The pivot below follows this table's theme... .table("sales", { schema: sales, rows: [], theme: spreadsheetThemes.slate }) .pivot("byRegion", ({ refs }) => refs .table("sales") .pivot({ rows: ["region"], columns: ["product"], values: { revenue: "sum" } }), ) // ...this one uses its own, with banded rows. .pivot("framed", ({ refs }) => refs.table("sales").pivot({ rows: ["region"], columns: ["product"], values: { revenue: "sum" }, theme: reportTheme, banded: true, }), ); ``` The theme becomes a custom pivot style in the file, so readers see it in Excel's pivot style gallery and keep it when they refresh or rearrange the pivot. What a pivot style can change is narrower than a cell style, and the types follow it: - Pivot slots take font colour, bold, italic, underline and strike, fills and borders. Font names and sizes, number formats and alignment are not part of a pivot style: numbers keep their `format` per value. - `pivotHeader` takes no border. Excel draws a header border on the first header row only, so header lines come from `pivotBody` and `pivotFrame`. - Along the pivot's edges, the frame wins over the totals' and subheadings' own borders. - Excel restyles the pivot when it refreshes it. The cells written into the file carry the same colours, so apps that do not refresh pivots show them too. - Columns keep the widths written with the pivot, wide enough for its headers: Excel does not narrow them when it refreshes. ## What TypeScript checks The fields are checked against the source table after its `select` is applied: ```ts twoslash // @errors: 2322 import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Sale = { region: string; revenue: number }; const sales = createExcelSchema() .column("region", { header: "Region", accessor: "region" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .build(); // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows: [] }) .pivot("bad", ({ refs }) => // "region" holds text, so it can only be counted refs.table("sales").pivot({ rows: ["region"], values: { region: "sum" } }), ); ``` The compiler also rejects an unknown column, a value with no aggregation (`values: { revenue: [] }`), a field used twice across rows, columns and filters (`✗ field "region" is used twice`), more than one column field, a pivot without values, and a pivot id already used on the sheet. For dates, it rejects grouping a column that is not a date or may be blank, units out of order such as `["month", "year"]`, and several units on a column or a filter. It also rejects a pivot with both `style` and `theme` (`✗ a pivot takes a built-in style or a theme, not both`), and pivot slots given a number format, a font size or, for `pivotHeader`, a border. ## Pivot charts `refs.pivot(id)` gives the marks of a pivot declared earlier. A pivot chart plots the whole pivot: row items on the x axis, one series per column item and value. Excel keeps it in sync with the pivot, so filtering the pivot updates the chart. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Sale = { region: string; month: string; revenue: number }; const sales = createExcelSchema() .column("region", { header: "Region", accessor: "region" }) .column("month", { header: "Month", accessor: "month" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .build(); // ---cut--- createWorkbook() .sheet("Sales") .table("sales", { schema: sales, rows: [] }) .sheet("Analysis") .pivot("byRegion", ({ refs }) => refs .sheet("Sales") .table("sales") .pivot({ rows: ["region"], columns: ["month"], values: { revenue: "sum" }, sort: "source", }), ) .chart("byRegionChart", ({ refs }) => ({ title: "Revenue by region", marks: [refs.pivot("byRegion").bar({ stack: true })], })); ``` Pivot marks are `bar`, `line`, `area`, `pie` and `doughnut`, with the same styling options as table marks. A pivot mark is the only mark of its chart, and a pie needs a pivot without columns and with a single value: both are checked by TypeScript. ## How a pivot is written - Excel rebuilds the pivot from its source table when the file opens, so the source stays the single truth and readers can refresh after editing it. - The library also writes the totals into the cells, so the file reads correctly before that refresh, for example in previews and apps that do not refresh pivots. - Items keep the order from `sort`. Items that appear after a refresh in Excel are added at the end. - In report mode, formula columns are computed by Excel: they can be values (their cells stay empty until the refresh), not rows, columns or filters. - A pivot over a native Excel table points at the table by name, so it follows rows added in Excel. - Tables that expand rows into sub-rows cannot be pivoted. ## Streaming A streamed table makes pivots with `sales.pivot(...)`, and `sheet.pivot(id, ...)` returns the pivot's marks. Rows are tallied as they are committed and are not kept, so declare the pivot before the first commit. ```ts twoslash import { createExcelSchema, createWorkbookStream } from "typed-xlsx"; type Sale = { region: string; month: string; revenue: number }; const schema = createExcelSchema() .column("region", { header: "Region", accessor: "region" }) .column("month", { header: "Month", accessor: "month" }) .column("revenue", { header: "Revenue", accessor: "revenue" }) .build(); declare const batches: AsyncIterable; // ---cut--- const workbook = createWorkbookStream(); const sales = await workbook.sheet("Sales").table("sales", { schema }); const analysis = workbook.sheet("Analysis"); const byRegion = analysis.pivot( "byRegion", sales.pivot({ rows: ["region"], columns: ["month"], values: { revenue: "sum" } }), ); analysis.chart("byRegionChart", { marks: [byRegion.bar({ stack: true })] }); for await (const rows of batches) { await sales.commit({ rows }); } await workbook.writeToFile("./sales.xlsx"); ``` # Building Workbooks A workbook holds sheets, a sheet holds one or more tables, and each table is a schema plus rows. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type User = { name: string; email: string; role: string }; const users = createExcelSchema() .column("name", { header: "Name", accessor: "name" }) .column("email", { header: "Email", accessor: "email" }) .column("role", { header: "Role", accessor: "role" }) .build(); const workbook = createWorkbook(); workbook.sheet("Users").table("users", { schema: users, rows: [ { name: "Alice Martin", email: "alice@acme.com", role: "Admin" }, { name: "Bob Chen", email: "bob@acme.com", role: "Viewer" }, ], }); await workbook.writeToFile("./users.xlsx"); ``` This page covers the buffered builder. For batches, the streaming builder has the same sheet and table options; see [Streaming Intro](https://typed-xlsx.vercel.app/streaming/streaming-intro). ## Several sheets Each `.sheet()` call adds a tab. Sheets are independent: their own schemas, row types and options. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; const orders = createExcelSchema<{ orderId: string; total: number }>() .column("orderId", { header: "Order ID", accessor: "orderId" }) .column("total", { header: "Total", accessor: "total", style: { numFmt: '"$"#,##0.00' } }) .build(); const products = createExcelSchema<{ sku: string; stock: number }>() .column("sku", { header: "SKU", accessor: "sku" }) .column("stock", { header: "In stock", accessor: "stock" }) .build(); const workbook = createWorkbook(); workbook.sheet("Orders").table("orders", { schema: orders, rows: [] }); workbook.sheet("Inventory").table("products", { schema: products, rows: [] }); ``` ## Several tables on one sheet Call `.table()` again on the same sheet. Tables stack top to bottom, one empty row apart: ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; const kpis = createExcelSchema<{ metric: string; value: number }>() .column("metric", { header: "Metric", accessor: "metric" }) .column("value", { header: "Value", accessor: "value" }) .build(); const sheet = createWorkbook().sheet("Dashboard"); sheet.table("q1", { title: "Q1", schema: kpis, rows: [ { metric: "MRR", value: 48000 }, { metric: "ARR", value: 576000 }, ], }); sheet.table("q2", { title: "Q2", schema: kpis, rows: [{ metric: "MRR", value: 52000 }] }); ``` ::sheet-figure --- marks: - range: A1:B4 label: table q1 - range: A6:B8 label: table q2, after one empty row (tableRowGap) merges: - A1:B1 - A6:B6 regions: A1:B1: title A2:B2: head A6:B6: title A7:B7: head rows: - - Q1 - null - - Metric - Value - - MRR - 48000 - - ARR - 576000 - - null - null - - Q2 - null - - Metric - Value - - MRR - 52000 widths: - 110 - 110 --- :: To place them side by side instead, set `tablesPerRow` on the sheet. Tables then fill each row of the layout left to right: ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; const kpis = createExcelSchema<{ metric: string; value: number }>() .column("metric", { header: "Metric", accessor: "metric" }) .column("value", { header: "Value", accessor: "value" }) .build(); createWorkbook() .sheet("KPIs", { tablesPerRow: 2, tableColumnGap: 1 }) .table("revenue", { title: "Revenue", schema: kpis, rows: [{ metric: "MRR", value: 48000 }], }) .table("churn", { title: "Churn", schema: kpis, rows: [{ metric: "Churn", value: 0.023 }], }); ``` ::sheet-figure --- marks: - range: A1:B3 label: table revenue - range: D1:E3 label: table churn, one empty column to the right (tableColumnGap) merges: - A1:B1 - D1:E1 regions: A1:B1: title A2:B2: head D1:E1: title D2:E2: head rows: - - Revenue - null - null - Churn - null - - Metric - Value - null - Metric - Value - - MRR - 48000 - null - Churn - 0.023 widths: - 90 - 90 - 40 - 90 - 90 --- :: ## Table options The second argument of `.table(id, options)`: | Option | Purpose | | ---------------------------- | ------------------------------------------------------------------------------------------------------------------------- | | `schema` | the schema to render | | `rows` | the rows, buffered builder only (streaming uses `commit()`) | | `title` | a title row above the headers, text or `({ ctx }) => string`; report mode | | `select` | `{ include }` or `{ exclude }` column IDs, see [Column Selection](https://typed-xlsx.vercel.app/columns/column-selection) | | `context` | the schema's context, required when the schema declares one | | `theme` | a [theme](https://typed-xlsx.vercel.app/styling/themes) for this table, over the schema's | | `defaults` | table-wide style defaults, below | | `autoFilter` | filter buttons on the header row | | `name`, `style`, `totalsRow` | excel-table mode only, see [Table Mode](https://typed-xlsx.vercel.app/excel-tables/table-mode) | ## Table defaults `defaults` restyles one table without touching column styles: the header, the summary rows, the body cells by protection state, and a minimum row height. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Renewal = { account: string; currentArr: number; targetArr: number }; const renewals = createExcelSchema() .column("account", { accessor: "account" }) .column("currentArr", { accessor: "currentArr", style: { numFmt: '"$"#,##0' } }) .column("targetArr", { accessor: "targetArr", style: { numFmt: '"$"#,##0', protection: { locked: false } }, }) .column("uplift", { formula: ({ refs, fx }) => fx.safeDiv(refs.column("targetArr"), refs.column("currentArr")).sub(1), style: { numFmt: "0.0%", protection: { hidden: true } }, }) .build(); createWorkbook() .sheet("Renewals", { protection: true }) .table("renewals", { schema: renewals, rows: [{ account: "Acme", currentArr: 100000, targetArr: 112000 }], defaults: { rowHeight: 24, header: { preset: "header.accent" }, summary: { preset: "summary.subtle" }, cells: { unlocked: { preset: "cell.input" }, locked: { preset: "cell.locked" }, hidden: { preset: "cell.hidden" }, }, }, }); ``` Body cells pick their default from their protection: `hidden` when `protection.hidden` is true, `unlocked` when `protection.locked` is false, `locked` otherwise. Here the editable target ARR gets the input look and the formula gets the hidden look. Presets: `header.accent`, `header.inverse`, `summary.subtle`, `cell.input`, `cell.locked`, `cell.hidden`. Any slot also accepts a plain `{ style }`. `rowHeight` is a minimum in points; wrapped rows still grow. ## Write the file | Method | Returns | Runs in | | ------------------- | --------------- | -------------------------- | | `toUint8Array()` | `Uint8Array` | any runtime | | `toBuffer()` | Node `Buffer` | runtimes with `Buffer` | | `writeToFile(path)` | `Promise` | runtimes with a filesystem | The streaming builder has more outputs, including Node and Web streams; see [Output Targets](https://typed-xlsx.vercel.app/streaming/output-targets). # Sheet Options `.sheet(name, options)` takes the same options in the buffered and streaming builders. | Option | Default | Purpose | | ---------------- | ------- | ------------------------------------------------------------------------------------------ | | `freezePane` | | `{ rows, columns }` that stay visible while scrolling | | `tablesPerRow` | `1` | how many tables sit side by side before wrapping to a new row | | `tableColumnGap` | `1` | empty columns between side-by-side tables | | `tableRowGap` | `1` | empty rows between stacked tables | | `rightToLeft` | `false` | mirror the sheet for right-to-left languages | | `protection` | | `true` or permissions, see [Protection](https://typed-xlsx.vercel.app/workbook/protection) | ## Freeze panes Freeze the rows a reader needs while scrolling, usually the title and header rows, and the identifying columns on wide reports. ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Account = { account: string; jan: number; feb: number; mar: number }; const schema = createExcelSchema() .column("account", { header: "Account", accessor: "account" }) .column("jan", { header: "Jan", accessor: "jan" }) .column("feb", { header: "Feb", accessor: "feb" }) .column("mar", { header: "Mar", accessor: "mar" }) .build(); createWorkbook() .sheet("Revenue", { freezePane: { rows: 2, columns: 1 } }) .table("revenue", { title: "Monthly revenue", schema, rows: [] }); ``` ::sheet-figure --- columns: - Account - Jan - Feb - Mar freeze: rows: 2 cols: 1 marks: - range: A1:D2 label: "frozen rows: the title and the header stay on screen" - range: A3:A5 label: "frozen column: the account name stays visible when scrolling right" rows: - - Acme - 4200 - 4400 - 4900 - - Globex - 8750 - 8100 - 9300 - - Initech - 1200 - 1300 - 1250 widths: - 110 - 80 - 80 - 80 title: Monthly revenue --- :: `freezePane` counts worksheet rows and columns, so with a table title the header is row 2. ## Layout of several tables `tablesPerRow`, `tableColumnGap` and `tableRowGap` decide where each `.table()` lands. [Building Workbooks](https://typed-xlsx.vercel.app/workbook/building-workbooks#several-tables-on-one-sheet) shows both layouts. ```ts twoslash import { createWorkbook } from "typed-xlsx"; createWorkbook().sheet("Dashboard", { tablesPerRow: 3, tableColumnGap: 2, tableRowGap: 2 }); ``` ## Right-to-left ```ts twoslash import { createWorkbook } from "typed-xlsx"; createWorkbook().sheet("تقرير", { rightToLeft: true }); ``` ## Names and limits Sheet names are cleaned (`\ / * ? : [ ] |` become spaces), cut to 31 characters and made unique ignoring case, so `Sales` and `sales` become `Sales` and `sales (2)`. `History` is reserved by Excel, so a sheet with that name becomes `History (2)`. A hyperlink such as `#'Sheet'!A1` must use the final name. Excel allows 1,048,576 rows and 16,384 columns per sheet. A workbook that would pass either limit throws an error naming the sheet instead of writing a file Excel cannot open. # Protection Protection has three levels, and the cell level only works inside a protected sheet: ::layer-figure --- layers: - cap: Workbook code: createWorkbook({ protection }) note: "locks the structure: no adding, removing or renaming sheets" - cap: Sheet code: .sheet(name, { protection }) note: makes locked cells read-only and hides hidden formulas accent: true - cap: Cell code: "style: { protection }" note: "marks a cell as editable (locked: false) or its formula as hidden" --- :: :workbook-preview{artifact="kitchen-sink" caption="A protected sheet where only the input column is editable, from the kitchen-sink example." sheet="11 Protected Inputs"} ## Choose which cells stay editable Every cell is locked by default. In a protected sheet, unlock the inputs people should fill in, and hide formulas you don't want shown in the formula bar: ```ts twoslash import { createExcelSchema, createWorkbook } from "typed-xlsx"; type Renewal = { account: string; target: number }; const schema = createExcelSchema() .column("account", { header: "Account", accessor: "account" }) .column("target", { header: "Target ARR", accessor: "target", style: { protection: { locked: false } }, }) .column("uplift", { header: "Uplift", formula: ({ refs }) => refs.column("target").mul(0.08), style: { protection: { hidden: true } }, }) .build(); createWorkbook() .sheet("Renewals", { protection: { password: "ops", selectLockedCells: false } }) .table("renewals", { schema, rows: [{ account: "Acme", target: 112000 }] }); ``` Here people can only select and edit Target ARR; the Uplift formula is computed but not visible. To make the editable cells stand out, give unlocked cells a look with [table defaults](https://typed-xlsx.vercel.app/workbook/building-workbooks#table-defaults) or the theme's `cellUnlocked` slot. ## Sheet protection `protection: true` protects the sheet with Excel's default permissions. An object sets a password and individual permissions: ```ts twoslash import { createWorkbook } from "typed-xlsx"; createWorkbook().sheet("Data", { protection: { password: "sheet-secret", selectUnlockedCells: true, selectLockedCells: false, sort: true, autoFilter: true, formatCells: false, insertRows: false, deleteRows: false, }, }); ``` The streaming builder's `.sheet()` takes the same option. ## Workbook protection Workbook protection is about the tabs, not the cells: ```ts twoslash import { createWorkbook } from "typed-xlsx"; const workbook = createWorkbook({ protection: { password: "workbook-secret", structure: true }, }); ``` ::callout{icon="i-lucide-info"} Excel's sheet passwords deter accidental edits; they are not encryption. Anyone with the file can remove them with common tools. :: Conditional styles can't change protection: whether a cell is locked or hidden is always set through `style`. # Streaming Intro `createWorkbookStream()` writes a workbook from batches of rows. You open a table, commit pages as your data source returns them, and finalize once. Nothing about the schema changes. ::flow-figure --- steps: - cap: Open code: sheet.table(id, { schema }) note: returns a table handle - cap: Commit code: commit({ rows }) note: plans, serializes and spools one batch - cap: Repeat code: for await (…) note: as many batches as you have - cap: Finalize code: writeToFile() · pipeTo() note: summary rows, then the ZIP is streamed out accent: true --- :: ## A complete stream ```ts twoslash import { createExcelSchema, createWorkbookStream } from "typed-xlsx"; type Sale = { date: string; region: string; revenue: number }; declare function salesPages(): AsyncIterable; const schema = createExcelSchema() .column("date", { header: "Date", accessor: "date" }) .column("region", { header: "Region", accessor: "region" }) .column("revenue", { header: "Revenue", accessor: "revenue", style: { numFmt: '"$"#,##0.00' }, summary: (s) => [s.formula("sum")], }) .build(); const workbook = createWorkbookStream(); const table = await workbook .sheet("Sales", { freezePane: { rows: 1 } }) .table("sales", { schema }); for await (const page of salesPages()) { await table.commit({ rows: page }); } await workbook.writeToFile("./sales.xlsx"); ``` Two differences from the buffered builder: `.table()` is awaited and returns a handle instead of the sheet, and rows go through `commit()` instead of a `rows` option. ## What a commit does Each `commit()` runs the batch through the same planner as the buffered builder: sub-row expansion, merges, widths, styles and formulas. It then serializes the rows to sheet XML, appends them to the sheet's temporary spool, and advances every summary reducer. When the promise resolves, the batch has been written and can be garbage collected. Await each commit before starting the next one on the same table; that is how backpressure reaches your data source. ## What finalizing does The output method (`writeToFile()`, `pipeTo()`, `pipeToNode()`, `toReadableStream()` or `toNodeReadable()`) finishes the summary rows and totals, then streams every part of the workbook into the ZIP. Call exactly one output method per workbook. See [Output Targets](https://typed-xlsx.vercel.app/streaming/output-targets). ## What the stream builder supports Everything the buffered builder does: report and excel-table modes, formula columns, summary rows and totals rows, sub-row expansion, groups and dynamic columns, column selection, themes and styles, conditional formats, validation, hyperlinks, images, sparklines, protection, freeze panes and several tables per sheet. ## Next - [Output Targets](https://typed-xlsx.vercel.app/streaming/output-targets): files, HTTP responses, S3, Web streams - [Memory Tuning](https://typed-xlsx.vercel.app/streaming/memory-tuning): temp storage and string modes - [Large Exports](https://typed-xlsx.vercel.app/performance/large-exports) and [Benchmarks](https://typed-xlsx.vercel.app/performance/benchmarks) # Output Targets The stream builder supports five finalization methods, all mutually exclusive: only one output can be started per workbook instance. ## Write to file ```ts twoslash // @noErrors import { createWorkbookStream } from "typed-xlsx"; const workbook = createWorkbookStream(); // ---cut--- await workbook.writeToFile("./output/report.xlsx"); ``` The file is created (or overwritten) at the given path. Intermediate directories must exist. ## Pipe to a Node.js writable ```ts twoslash // @noErrors import { createWorkbookStream } from "typed-xlsx"; import { createWriteStream } from "node:fs"; const workbook = createWorkbookStream(); // ---cut--- const dest = createWriteStream("./report.xlsx"); await workbook.pipeToNode(dest); ``` Any `NodeJS.WritableStream` is accepted. The stream is ended automatically when the workbook finishes. **Express response:** ```ts twoslash // @noErrors import { createExcelSchema, createWorkbookStream } from "typed-xlsx"; import type { Request, Response } from "express"; const schema = createExcelSchema<{ name: string; amount: number }>() .column("name", { accessor: "name" }) .column("amount", { accessor: "amount", style: { numFmt: "#,##0.00" } }) .build(); export async function downloadReport(req: Request, res: Response) { res.setHeader( "Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", ); res.setHeader("Content-Disposition", 'attachment; filename="report.xlsx"'); const workbook = createWorkbookStream({ tempStorage: "file" }); const table = await workbook.sheet("Report").table("data", { schema }); for await (const batch of fetchRows()) { await table.commit({ rows: batch }); } await workbook.pipeToNode(res); } ``` **Fastify response:** ```ts twoslash // @noErrors import { createExcelSchema, createWorkbookStream } from "typed-xlsx"; import type { FastifyReply } from "fastify"; const schema = createExcelSchema<{ name: string }>() .column("name", { accessor: "name" }) .build(); export async function handler(_req: unknown, reply: FastifyReply) { reply.header( "Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", ); reply.header("Content-Disposition", 'attachment; filename="report.xlsx"'); const workbook = createWorkbookStream(); const table = await workbook.sheet("Data").table("rows", { schema }); await table.commit({ rows: [{ name: "Ada" }] }); const stream = workbook.toNodeReadable(); return reply.send(stream); } ``` ## Pipe to a Web Streams writable ```ts twoslash // @noErrors import { createWorkbookStream } from "typed-xlsx"; const workbook = createWorkbookStream(); // ---cut--- const { writable } = new TransformStream(); await workbook.pipeTo(writable); ``` **Hono / Cloudflare Workers response:** ```ts twoslash // @noErrors import { createExcelSchema, createWorkbookStream } from "typed-xlsx"; const schema = createExcelSchema<{ name: string }>() .column("name", { accessor: "name" }) .build(); export default { async fetch(_req: Request): Promise { const workbook = createWorkbookStream({ tempStorage: "memory" }); const table = await workbook.sheet("Data").table("rows", { schema }); await table.commit({ rows: [{ name: "Ada" }] }); const { readable, writable } = new TransformStream(); workbook.pipeTo(writable); // don't await: starts async return new Response(readable, { headers: { "Content-Type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Content-Disposition": 'attachment; filename="report.xlsx"', }, }); }, }; ``` ## Get a Node.js Readable ```ts twoslash import { createWorkbookStream } from "typed-xlsx"; const workbook = createWorkbookStream(); // ---cut--- const readable = workbook.toNodeReadable(); // pipe to any further transform or upload readable.pipe(process.stdout); ``` ## Get a Web ReadableStream ```ts twoslash import { createWorkbookStream } from "typed-xlsx"; const workbook = createWorkbookStream(); // ---cut--- const readable = workbook.toReadableStream(); ``` ## One output per workbook Calling any finalization method a second time throws an error. ```ts twoslash // @noErrors import { createWorkbookStream } from "typed-xlsx"; const workbook = createWorkbookStream(); // ---cut--- await workbook.writeToFile("./a.xlsx"); // Calling again would throw: "Workbook stream output has already started." ``` If you need the same data in two places, finalize once and copy the file, or pipe through a PassThrough/tee stream. # Memory Tuning `createWorkbookStream()` takes five options. Four of them control where temporary data lives and how strings are stored; `protection` sets workbook protection and does not affect memory. The defaults suit most exports; change them when memory is tight or there is no writable filesystem. For guidance with measured numbers, see [Large Exports](https://typed-xlsx.vercel.app/performance/large-exports). ## `tempStorage` Controls where sheet data is spooled while rows are being committed. | Value | Description | | ------------------ | ---------------------------------------------------------------------------------------------------------- | | `"file"` (default) | Each sheet's OOXML fragments are written to a temp file on disk. Disk I/O is the limiting factor, not RAM. | | `"memory"` | Fragments are held in memory buffers. Faster I/O but heap grows with total serialized XML size. | Keep `"file"` unless the runtime has no writable filesystem. With `"memory"`, memory grows with the size of the serialized sheet. ## `tempDirectory` When `tempStorage: "file"` is active, `tempDirectory` controls where the intermediate sheet spools are written. Each workbook writes into its own private subfolder of that directory (created with owner-only permissions), so two exports can share one `tempDirectory`. The subfolder and its files are deleted when the export finishes, fails, or you call `dispose()`; the directory you pass is never removed. ```ts twoslash import { createWorkbookStream } from "typed-xlsx"; const workbook = createWorkbookStream({ tempStorage: "file", tempDirectory: "/tmp/typed-xlsx-spool", }); ``` ```ts twoslash import { createWorkbookStream } from "typed-xlsx"; // Large export: keep intermediate XML off the heap const large = createWorkbookStream({ tempStorage: "file" }); // Small export running in a constrained environment where disk is slow const small = createWorkbookStream({ tempStorage: "memory" }); ``` ## `memoryProfile` A convenience preset that picks a string-table strategy for you. | Value | String mode | Trade-off | | ---------------------- | ----------- | ----------------------------------------------------- | | `"balanced"` (default) | `"shared"` | Smaller output file, shared string table kept in heap | | `"low-memory"` | `"inline"` | No shared string table in heap, larger output file | | `"compact-file"` | `"shared"` | Same as balanced: optimizes for file size | ```ts twoslash import { createWorkbookStream } from "typed-xlsx"; // Minimize heap: useful in memory-constrained environments (edge, Lambda) const lowMem = createWorkbookStream({ memoryProfile: "low-memory" }); // Minimize output file size const compact = createWorkbookStream({ memoryProfile: "compact-file" }); ``` ## `strings` Overrides `memoryProfile` and sets the string storage strategy directly. | Value | Behavior | | ---------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `"shared"` | All string cells written to a shared string table (`sharedStrings.xml`). Excel deduplicates repeated strings: smaller file for data with many repeated values. | | `"inline"` | String values written inline in each cell element. No shared string table is built in memory. | | `"auto"` | Falls back to the `memoryProfile` preset. | ```ts twoslash import { createWorkbookStream } from "typed-xlsx"; // Explicit shared strings: repeated string values (status, region, category) compress well const sharedStrings = createWorkbookStream({ strings: "shared" }); // Explicit inline: no heap overhead for string deduplication const inlineStrings = createWorkbookStream({ strings: "inline" }); ``` When both `memoryProfile` and `strings` are set, `strings` takes precedence. ## Combining options For a large export in a memory-constrained environment: ```ts twoslash import { createWorkbookStream } from "typed-xlsx"; const workbook = createWorkbookStream({ tempStorage: "file", memoryProfile: "low-memory", // inline strings, no shared table in heap }); ``` For a medium export where file size matters more than memory: ```ts twoslash import { createWorkbookStream } from "typed-xlsx"; const workbook = createWorkbookStream({ tempStorage: "memory", // fast I/O strings: "shared", // deduplicate strings, smaller output }); ``` ## What stays in memory Whatever the options, a stream export keeps: - the batch being committed, until `commit()` resolves - the style registry and summary accumulators, which are small - the shared string table, with `strings: "shared"` - merge ranges, hyperlinks and images, and the row ranges of summary formulas that read logical rows With file spooling and inline strings, a flat export's memory barely grows with the row count: 274 MiB at 200k rows and 279 MiB at 500k in the [benchmarks](https://typed-xlsx.vercel.app/performance/benchmarks). Schemas that use the features in the last bullet grow more. # Stream Examples ## Upload directly to S3 Use the AWS SDK v3 `Upload` helper with a Node.js Readable from `toNodeReadable()`. The upload streams the XLSX directly to S3 without writing a temp file to disk. ```ts twoslash import { createExcelSchema, createWorkbookStream } from "typed-xlsx"; declare class S3Client { constructor(config: { region: string }); } declare class Upload { constructor(input: { client: unknown; params: { Bucket: string; Key: string; Body: NodeJS.ReadableStream; ContentType: string; }; }); done(): Promise; } declare function fetchRowsCursor(): AsyncIterable< Array<{ id: string; amount: number }> >; const schema = createExcelSchema<{ id: string; amount: number }>() .column("id", { accessor: "id" }) .column("amount", { accessor: "amount", style: { numFmt: "#,##0.00" } }) .build(); const s3 = new S3Client({ region: "us-east-1" }); async function uploadReportToS3(key: string) { const workbook = createWorkbookStream({ tempStorage: "file", memoryProfile: "low-memory", }); const table = await workbook.sheet("Report").table("data", { schema }); for await (const batch of fetchRowsCursor()) { await table.commit({ rows: batch }); } const upload = new Upload({ client: s3, params: { Bucket: "my-reports-bucket", Key: key, Body: workbook.toNodeReadable(), ContentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", }, }); await upload.done(); } ``` For most S3 use cases, prefer the SDK `Upload` helper above instead of implementing multipart logic yourself. ## Stream with progress tracking Wrap `.commit()` calls with a counter to report progress back to a job queue, SSE connection, or log. ```ts twoslash import { createExcelSchema, createWorkbookStream } from "typed-xlsx"; declare function getRowCount(): Promise; declare function fetchRowsCursor(): AsyncIterable>; const schema = createExcelSchema<{ id: string; value: number }>() .column("id", { accessor: "id" }) .column("value", { accessor: "value" }) .build(); async function exportWithProgress(jobId: string, onProgress: (pct: number) => void) { const workbook = createWorkbookStream({ tempStorage: "file" }); const table = await workbook.sheet("Export").table("data", { schema }); const totalRows = await getRowCount(); let committed = 0; for await (const batch of fetchRowsCursor()) { await table.commit({ rows: batch }); committed += batch.length; onProgress(Math.round((committed / totalRows) * 100)); } await workbook.writeToFile(`./exports/${jobId}.xlsx`); } ``` ## Parallel schema preparation, serial commit Schema building is pure TypeScript: it does no async I/O. You can build schemas and even begin creating stream tables concurrently, as long as you commit rows to each table serially. ```ts twoslash import { createExcelSchema, createWorkbookStream } from "typed-xlsx"; declare function fetchOrders(): AsyncIterable>; declare function fetchReturns(): AsyncIterable>; const ordersSchema = createExcelSchema<{ id: string; total: number }>() .column("id", { accessor: "id" }) .column("total", { accessor: "total" }) .build(); const returnsSchema = createExcelSchema<{ orderId: string; reason: string }>() .column("orderId", { accessor: "orderId" }) .column("reason", { accessor: "reason" }) .build(); const workbook = createWorkbookStream(); // Create both tables concurrently: safe, each is on its own sheet const [ordersTable, returnsTable] = await Promise.all([ workbook.sheet("Orders").table("orders", { schema: ordersSchema }), workbook.sheet("Returns").table("returns", { schema: returnsSchema }), ]); // Fetch and commit both pipelines concurrently await Promise.all([ (async () => { for await (const batch of fetchOrders()) { await ordersTable.commit({ rows: batch }); } })(), (async () => { for await (const batch of fetchReturns()) { await returnsTable.commit({ rows: batch }); } })(), ]); await workbook.writeToFile("./report.xlsx"); ``` For HTTP response streaming, use the output-target examples in the streaming docs. The underlying pattern is the same: commit rows in batches, then finalize with exactly one of `pipeToNode()` or `toNodeReadable()` once the workbook is ready. # Benchmarks These numbers come from the streaming benchmark in `packages/benchmark`, run on 2026-09-22 against version 4.2.0 and the current engine with the same harness, schema and data. The charts read the published report directly. ## Export time Each workload writes an 11-column report with styles, dates, number formats and summary rows, committed in 5,000-row batches. Every notes cell contains XML-reserved characters and Unicode, so escaping is part of the measurement. :benchmark-chart{metric="time"} The current engine is 3.2× to 3.4× faster across the four workloads. The gain is in row processing and spooling: finalizing the ZIP still takes about one second at 200k rows and 2.4 seconds at 500k, as before. ## Peak memory :benchmark-chart{metric="rss"} With inline strings, peak memory barely moves between 200k and 500k rows (274 and 279 MiB). Shared strings trade memory for a smaller file: the string table stays in memory until the workbook is finalized. See [Large Exports](https://typed-xlsx.vercel.app/performance/large-exports) for how to choose. Memory is not constant for every schema. Shared strings, merged sub-rows, images, hyperlinks and row-aware summary formulas keep their own metadata for the whole export. ## A real application export The same engines exported a production-shaped report from a local PostgreSQL database: 202,058 records, 25 columns, 41 batches, with file spooling and the low-memory profile. The whole worker job went from 40.3 s to 19.4 s. :benchmark-chart{metric="app"} | Phase | 4.2.0 | Current | | ---------------------- | ------- | ------- | | Fetch hydrated records | 11.08 s | 10.58 s | | Commit XLSX rows | 27.21 s | 6.88 s | | Finalize XLSX | 1.66 s | 1.54 s | | Whole job | 40.33 s | 19.36 s | Writing the five million cells went from 27.2 s to 6.9 s, so fetching from the database is now the slowest phase. Both versions produced byte-identical files (21,832,191 bytes). ## How it was measured - Node 24.13.0 on an Apple M5 with 32 GiB of RAM, macOS. - Baseline and current engine run serially in alternating fresh processes. - 200k-row results are the median of three runs. The 500k-row case is a single run per engine, so read it as a scaling check. - Peak memory is the process high-water mark (`maxRSS`). - All 20 benchmark workbooks are byte-identical between the two engines. These are local measurements, not a production or concurrent-load guarantee. The full report, raw JSON and the commands to reproduce it are in [`packages/benchmark/artifacts/reports/stream`](https://github.com/ChronicStone/typed-xlsx/tree/main/packages/benchmark/artifacts/reports/stream){rel=""nofollow""}. # Large Exports Most reports never need tuning. This page is for exports in the hundreds of thousands of rows, or for runtimes with a tight memory limit. ## Start with the builder `createWorkbook()` holds every row in memory and plans the whole sheet at once. That is the simplest API and fine for most reports. Switch to `createWorkbookStream()` when the rows come from a cursor or a paginated API, or when holding them all at once is too much for your runtime. It accepts the same schema; only the row input changes. ```ts twoslash import { createExcelSchema, createWorkbookStream } from "typed-xlsx"; type Order = { id: string; amount: number }; declare function pages(): AsyncIterable; const schema = createExcelSchema() .column("id", { accessor: "id" }) .column("amount", { accessor: "amount", summary: (s) => [s.formula("sum")] }) .build(); const workbook = createWorkbookStream(); const table = await workbook.sheet("Orders").table("orders", { schema }); for await (const page of pages()) { await table.commit({ rows: page }); } await workbook.writeToFile("./orders.xlsx"); ``` ## Shared or inline strings This is the setting that moves memory the most. | `strings` | What it does | Measured at 200k rows (see [Benchmarks](https://typed-xlsx.vercel.app/performance/benchmarks)) | | -------------------- | -------------------------------------------------- | ---------------------------------------------------------------------------------------------- | | `"shared"` (default) | one deduplicated string table, kept until finalize | 3.45 s, 415 MiB peak | | `"inline"` | strings written into each cell, no table in memory | 3.72 s, 274 MiB peak | Use `"inline"`, or `memoryProfile: "low-memory"`, when memory is the constraint. Keep `"shared"` when the file size matters and many cells repeat the same strings (statuses, regions, categories). ```ts twoslash import { createWorkbookStream } from "typed-xlsx"; const workbook = createWorkbookStream({ memoryProfile: "low-memory" }); ``` ## Keep temporary data on disk By default, committed rows are serialized to a temporary file and streamed into the ZIP at the end. Keep that default. `tempStorage: "memory"` holds the serialized XML in memory instead, and only makes sense when there is no writable filesystem. ## Batch at your source's page size Commit the pages your database or API already returns. Re-batching in your own loop adds work without saving memory. 1,000 to 10,000 rows per commit is a good range; the benchmarks use 5,000. ## What still grows with the row count Even with inline strings and file spooling, some features keep metadata for the whole export: - merged cells from sub-row expansion - hyperlinks and embedded images - summary formulas that reference logical rows (`column.rows()`) - shared strings, if you keep the default None of them stop an export from streaming; they make memory grow slowly with the row count instead of staying flat. ## Plan for sub-row expansion An accessor that returns arrays multiplies physical rows. 100k orders with five lines each is a 500k-row sheet, and Excel stops at 1,048,576 rows per sheet. Split very large expanded exports across sheets. ## Checklist - `createWorkbookStream()` for cursors, pagination, or exports that don't fit in memory - `memoryProfile: "low-memory"` when memory is the limit, the default when file size is - default file spooling, unless there is no writable filesystem - commit your source's natural page size - fixed `width` instead of `autoWidth` on very wide schemas, so widths aren't measured from every cell Option details: [Memory Tuning](https://typed-xlsx.vercel.app/streaming/memory-tuning). # v0 → v1 v1 is a complete ground-up rewrite. The `xlsx-js-style` / SheetJS dependency is gone. Every layer from schema definition to OOXML serialization to ZIP packaging has been rebuilt from scratch. This page covers every breaking change with before/after examples. --- ## Entry point renames The class-based static constructors are replaced with plain functions. | v0 | v1 | | -------------------------------- | ------------------------ | | `ExcelSchemaBuilder.create()` | `createExcelSchema()` | | `ExcelBuilder.create()` | `createWorkbook()` | ```ts // v0 import { ExcelSchemaBuilder, ExcelBuilder } from "typed-xlsx"; const schema = ExcelSchemaBuilder.create().column(...).build(); const workbook = ExcelBuilder.create(); // v1 import { createExcelSchema, createWorkbook } from "typed-xlsx"; const schema = createExcelSchema().column(...).build(); const workbook = createWorkbook(); ``` `createExcelSchema()` now defaults to report mode. Native Excel table schemas use `createExcelSchema({ mode: "excel-table" })`. --- ## `key` → `accessor` `key` accepted only a typed dot-path string: a column's value was always a single field. Any column that needed a computed or multi-field value required a separate `transform` step. `accessor` accepts the same typed dot-path string **or** a function, at the same definition site. The function form removes the need for `transform` in the majority of cases. ```ts // v0: path only, computed values need a separate transform ExcelSchemaBuilder.create().column("fullName", { key: "firstName", transform: (val, row) => `${row.firstName} ${row.lastName}`, }); // v1: path or function, unified at the accessor createExcelSchema().column("fullName", { accessor: (row) => `${row.firstName} ${row.lastName}`, header: "Full Name", }); ``` Path access works exactly as before: ```ts // v0 .column("city", { key: "address.city" }) // v1 .column("city", { accessor: "address.city" }) ``` --- ## `label` → `header` ```ts // v0 .column("name", { key: "name", label: "Full Name" }) // v1 .column("name", { accessor: "name", header: "Full Name" }) ``` --- ## `default` → `defaultValue` ```ts // v0 .column("score", { key: "score", default: 0 }) // v1 .column("score", { accessor: "score", defaultValue: 0 }) ``` --- ## `cellStyle` → `style` ```ts // v0 .column("amount", { key: "amount", cellStyle: { numFmt: "$#,##0.00" }, }) // v1 .column("amount", { accessor: "amount", style: { numFmt: "$#,##0.00" }, }) ``` `headerStyle` is unchanged. --- ## `CellStyle` shape: `fill.fgColor` → `fill.color` v0 used `xlsx-js-style`'s internal style shape. v1 defines and owns its `CellStyle` type. The only structural change is the fill color key. ```ts // v0 (xlsx-js-style shape) cellStyle: { fill: { fgColor: { rgb: "FF0000" } }, font: { bold: true }, } // v1 (CellStyle) style: { fill: { color: { rgb: "FF0000" } }, font: { bold: true }, } ``` All other style fields (`font`, `border`, `alignment`, `numFmt`) have the same shape. --- ## `transform`: signature extended The `transform` function gained access to the full row as its second argument. Update any transform that needed row context but was working around the limitation. ```ts // v0: only the extracted value, no row access transform: (value, rowIndex) => value.toUpperCase(); // v1: object callback with value, full row, rowIndex, and schema context transform: ({ value, row, rowIndex, ctx }) => value.toUpperCase(); ``` --- ## `withTransformers()` and `withFormatters()` removed Named transformer and formatter registries are gone. Pass the function directly in the column definition, or extract it as a plain TypeScript constant. ```ts // v0 ExcelSchemaBuilder.create() .withTransformers({ upper: (v: string) => v.toUpperCase() }) .column("name", { key: "name", transform: "upper" }) .build(); // v1 const upper = (v: string) => v.toUpperCase(); createExcelSchema() .column("name", { accessor: "name", transform: ({ value }) => upper(value) }) .build(); ``` --- ## Summary API: reducer model The v0 `value` function received the full `T[]` array. This required all rows to be resident at once, making streaming impossible. v1 uses a three-function reducer (`init` / `step` / `finalize`) that the engine drives row-by-row. This is what enables summary support in stream mode. The `cellStyle` field on the summary definition is also renamed to `style`. ```ts // v0 .column("balance", { key: "balance", summary: [ { value: data => data.reduce((acc, row) => acc + row.balance, 0), format: '"$"#,##0.00', cellStyle: { font: { bold: true } }, }, { value: data => data.reduce((acc, row) => acc + row.balance, 0) * 1.2, format: '"$"#,##0.00', }, ], }) // v1 .column("balance", { accessor: "balance", summary: (summary) => [ summary.cell({ init: () => 0, step: (acc, row) => acc + row.balance, finalize: acc => acc, format: '"$"#,##0.00', style: { font: { bold: true } }, }), summary.cell({ init: () => 0, step: (acc, row) => acc + row.balance, finalize: acc => acc * 1.2, format: '"$"#,##0.00', }), ], }) ``` If you need a label column, use helpers like `summary.label("Total")`. Raw `SummaryDefinition` objects and arrays are still accepted, but the callback-builder form is the preferred public API. --- ## Column selection: `include` / `exclude` v0 used a per-column boolean map. Every column in the schema needed a key in the object, set to `true` or `false`. v1 uses explicit `include` or `exclude` arrays. Specify only what you want to show or hide. ```ts // v0: must enumerate every column .addTable({ schema, data: rows, select: { firstName: true, lastName: true, email: true, internalId: false }, }) // v1: state only what you care about .table("users", { schema, rows, select: { include: ["firstName", "lastName", "email"] }, // or equivalently: // select: { exclude: ["internalId"] }, }) ``` --- ## Table input: field renames and method rename | v0 field / method | v1 equivalent | | -------------------- | ------------------------------------------------- | | `.addTable({ ... })` | `.table("table-id", { ... })` | | `data: T[]` | `rows: T[]` | | `titleStyle` | removed: `title` is a plain string only | | `summary?: boolean` | removed: summaries render when defined on columns | ```ts // v0 workbook.sheet("Report").addTable({ schema, data: rows, title: "Monthly Report", titleStyle: { font: { bold: true } }, summary: true, }); // v1 workbook.sheet("Report").table("report", { schema, rows, title: "Monthly Report", // plain string only, no titleStyle }); ``` --- ## Sheet options | v0 | v1 | | --------------------- | --------------------------- | | `tablesPerRow` | `tablesPerRow` (unchanged) | | `tableSeparatorWidth` | `tableColumnGap` | | `rtl` on `.build()` | `rightToLeft` on `.sheet()` | | `rowHeight` | removed | | `extraLength` | `tableColumnGap` | ```ts // v0 ExcelBuilder.create() .sheet("Report", { tablesPerRow: 2, tableSeparatorWidth: 2 }) .addTable(...) .build({ output: "buffer", rtl: true }) // v1 createWorkbook() .sheet("Report", { tablesPerRow: 2, tableColumnGap: 2, rightToLeft: true }) .table(...) .toBuffer() ``` --- ## Output methods v0 `.build()` required an `output` type parameter and returned the result synchronously with a type that depended on that parameter. v1 separates output into explicit terminal methods on the workbook. ```ts // v0 const buffer = workbook.build({ output: "buffer" }); const uint8 = workbook.build({ output: "uint8array" }); await workbook.build({ output: "file", filePath: "./report.xlsx" }); // v1 const buffer = workbook.toBuffer(); const uint8 = workbook.toUint8Array(); await workbook.writeToFile("./report.xlsx"); ``` --- ## Dynamic columns The context type is now explicit in the callback for runtime-generated columns. Context is still injected at table creation time via the `context` field. In v1, groups are also part of the typed formula scope: - formulas inside a group can reference previous outer columns - later formulas can aggregate a previous group with scope helpers ```ts // v0 schema.group("dates", (builder, ctx: Record) => { for (const [key, label] of Object.entries(ctx)) builder.column(key, { key: () => ..., label }) }) // v1 createExcelSchema }>() .dynamic("dates", (builder, { ctx }) => { for (const [key, label] of Object.entries(ctx.dates)) builder.column(key, { accessor: () => ..., header: label }) }) .build() ``` --- ## Stream workbook `createWorkbookStream()` is new in v1. There is no equivalent in v0: the old `streaming: true` flag on `.build()` still materialized the full workbook before writing. See the [Streaming Intro](https://typed-xlsx.vercel.app/streaming/streaming-intro) section for full documentation. --- ## Removed exports | v0 export | Status in v1 | | -------------------- | --------------------------------------- | | `ExcelSchemaBuilder` | Removed: use `createExcelSchema()` | | `ExcelBuilder` | Removed: use `createWorkbook()` | | `TransformersMap` | Removed: no longer needed | | `FormattersMap` | Removed: no longer needed | | `FormatterPreset` | Removed: no longer needed | | `TOutputType` | Removed: output is via explicit methods | | `ExcelBuildParams` | Removed | | `ExcelBuildOutput` | Removed | | `SheetTableBuilder` | Removed | `CellStyle` is still exported. Its shape changed (see above). --- ## Quick reference | Change | v0 | v1 | | ------------------- | -------------------------------------- | ----------------------------------------------- | | Schema factory | `ExcelSchemaBuilder.create()` | `createExcelSchema()` | | Workbook factory | `ExcelBuilder.create()` | `createWorkbook()` | | Column field path | `key` | `accessor` | | Column header | `label` | `header` | | Column fallback | `default` | `defaultValue` | | Column style | `cellStyle` | `style` | | Fill color | `fill.fgColor.rgb` | `fill.color.rgb` | | Transform signature | `(value, rowIndex)` | `({ value, row, rowIndex, ctx })` | | Row array field | `data` | `rows` | | Add table method | `.addTable()` | `.table()` | | Column selection | `{ col: true/false }` | `{ include: [] }` / `{ exclude: [] }` | | Summary reducer | `value: (data) => ...` | `init / step / finalize` | | Summary style | `cellStyle` | `style` | | Table title style | `titleStyle` | removed | | Summary toggle | `summary: boolean` on table | removed: implicit when columns define summaries | | Sheet column gap | `tableSeparatorWidth` / `extraLength` | `tableColumnGap` | | RTL option | `.build({ rtl })` | `.sheet(name, { rightToLeft })` | | Buffer output | `.build({ output: "buffer" })` | `.toBuffer()` | | Uint8Array output | `.build({ output: "uint8array" })` | `.toUint8Array()` | | File output | `.build({ output: "file", filePath })` | `.writeToFile(path)` | # v1 → v2 v2 is not a rewrite: the core schema/workbook model from v1 is untouched. Instead v2 extends the engine with native Excel features: live formulas, Excel tables, conditional formatting, data validation, hyperlinks, and worksheet/workbook protection. Most v1 code runs on v2 without changes. This page covers the conceptual shifts, new APIs to adopt, and practical constraints to be aware of. --- ## Schema mode is now an explicit choice v1 schemas always emitted styled worksheet ranges (report mode). v2 adds a second mode: native Excel tables. ```ts // v1: implicit report mode (still works in v2) createExcelSchema().column(...).build() // v2: explicit excel-table mode createExcelSchema({ mode: "excel-table" }).column(...).build() ``` Both modes share the same column/group/summary builder. The difference is in what the engine emits: | Concern | Report mode | Excel-table mode | | ----------------- | ---------------------- | --------------------------------------------------- | | Output | Styled worksheet range | Native OOXML `
` object | | Formula refs | A1-style (`=C2*D2`) | Structured refs (`=[@[Qty]]*[@[Unit Price]]`) | | autoFilter | Opt-in | On by default | | Sub-row expansion | Supported | Not supported | | Summary rows | `summary()` on columns | `totalsRow` on columns + `totalsRow: true` on table | If your v1 schemas do not use `{ mode: "excel-table" }`, they behave identically to before. --- ## Formulas replace precomputed values v1 had no formula API. Computed columns required resolving values in JavaScript before export. v2 adds a typed formula DSL that emits live Excel formulas. ```ts // v1: precompute in JS .column("net", { accessor: (row) => row.contractValue * (1 - row.discountPct), style: { numFmt: '"$"#,##0.00' }, }) // v2: live Excel formula .column("net", { formula: ({ refs, fx }) => fx.round( refs.column("contractValue").mul(fx.literal(1).sub(refs.column("discountPct"))), 2, ), style: { numFmt: '"$"#,##0.00' }, }) ``` Key APIs: | API | Purpose | | ------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------ | | `refs.column("columnId")` | Reference the current row's cell for a preceding column | | `row.series("columnId")` | Reference the full expanded range of a column within the current logical row (report mode) | | `fx.sum(refs.group("groupId"))` / `fx.sum(refs.dynamic("dynamicId"))` | Aggregate all columns in a structural group or dynamic scope | | `fx.round`, `fx.abs`, `fx.if`, `fx.and`, `fx.or`, `fx.not`, `fx.min`, `fx.max` | Reusable formula helpers | | `fx.literal(n)` | Wrap a constant value for use in formula chains | Formula columns with `accessor` still work, but `formula` and `accessor` are mutually exclusive on a single column. --- ## Summary rows can now be live formulas v1 summaries used a JS reducer (`init` / `step` / `finalize`). That still works. v2 adds formula-based summaries that emit live Excel formulas in the summary row. ```ts // v1: JS reducer (still works in v2) summary: (s) => [ s.cell({ init: () => 0, step: (acc, row) => acc + row.revenue, finalize: (acc) => acc, style: { numFmt: '"$"#,##0.00' }, }), ]; // v2: live formula summary summary: (s) => [ s.formula("sum", { style: { numFmt: '"$"#,##0.00', font: { bold: true } }, }), ]; // v2: custom formula summary summary: (s) => [ s.formula(({ column, fx }) => fx.round(column.cells().average(), 2), { style: { numFmt: '"$"#,##0.00' }, }), ]; ``` Formula summaries also understand logical vs physical rows: | API | Meaning | | ---------------- | ------------------------------------------------------------------------ | | `column.cells()` | Aggregates physical worksheet cells | | `column.rows()` | Aggregates logical source rows (relevant when sub-row expansion is used) | ::callout{type="info"} Formula summaries are report-mode only. In excel-table mode, use `totalsRow` on columns instead. :: --- ## Excel-table mode: new column and table options If you adopt `{ mode: "excel-table" }`, these options become available: **On columns:** ```ts .column("units", { accessor: "units", totalsRow: { function: "sum" }, // native totals row aggregate }) .column("label", { accessor: "rep", totalsRow: { label: "TOTAL" }, // static label in totals row }) ``` **On table input:** ```ts .table("forecast", { schema, rows, name: "ForecastTable", // native table name style: "TableStyleMedium2", // native table style totalsRow: true, // enable totals row autoFilter: true, // on by default, can opt out }) ``` --- ## Conditional styling: native Excel conditional formatting v1 had `dynamicStyle`, which resolved styles once during export. That still works. v2 adds `conditionalStyle`, which emits native Excel conditional formatting rules that stay reactive after export. ```ts // v1: resolved once during export (still works in v2) .column("amount", { accessor: "amount", dynamicStyle: (row) => row.amount > 10000 ? { font: { bold: true } } : undefined, }) // v2: native Excel conditional formatting (live in Excel) .column("attainment", { formula: ({ refs, fx }) => fx.safeDiv(refs.column("amount"), refs.column("quota")), style: { numFmt: "0.0%" }, conditionalStyle: (conditional) => conditional .when(({ refs }) => refs.column("attainment").lt(0.5), { fill: { color: { rgb: "FEE2E2" } }, font: { color: { rgb: "991B1B" }, bold: true }, }) .when(({ refs, fx }) => fx.and(refs.column("attainment").gte(1), refs.column("status").eq("won")), { fill: { color: { rgb: "DCFCE7" } }, font: { color: { rgb: "166534" }, bold: true }, }, ), }) ``` ::callout{type="warning"} `conditionalStyle` cannot toggle protection flags ( `locked` / `hidden` ). Use base `style` for those. :: --- ## Data validation DSL v2 introduces native Excel data validation on columns. ```ts .column("status", { accessor: "status", validation: (v) => v.list(["draft", "active", "archived"]) .prompt({ title: () => "Allowed values", message: () => "Choose draft, active, or archived", }) .error({ title: () => "Invalid status", message: () => "Use one of the allowed workflow states", }), }) .column("amount", { accessor: "amount", validation: (v) => v.integer().between(100, 100000).allowBlank(), }) .column("startDate", { accessor: "startDate", validation: (v) => v.date().gte(new Date(Date.UTC(2025, 0, 1))), }) ``` Custom formula-based validation can reference other columns: ```ts .column("score", { accessor: "score", validation: (v) => v.custom(({ refs }) => refs.column("score").gte(refs.column("amount"))), }) ``` --- ## Hyperlinks v2 adds native hyperlink support. Link metadata is separate from the rendered cell value. ```ts .column("customerName", { accessor: "customerName", hyperlink: ({ row }) => row.hasPortal ? { target: `https://example.com/customers/${row.customerId}`, tooltip: "Open customer record", style: { font: { color: { rgb: "7C3AED" }, underline: false, bold: true }, }, } : null, }) ``` --- ## Worksheet and workbook protection v2 adds sheet-level and workbook-level protection, plus cell-level protection flags in the style object. **Workbook protection:** ```ts createWorkbook({ protection: { password: "workbook-secret", structure: true, }, }); ``` **Sheet protection:** ```ts .sheet("Protected", { protection: { password: "sheet-secret", selectUnlockedCells: true, selectLockedCells: false, }, }) ``` **Cell-level protection via style:** ```ts // Editable input cell .column("input", { accessor: "input", style: { protection: { locked: false } }, }) // Hidden formula cell .column("formulaValue", { formula: ({ refs }) => refs.column("input").mul(2), style: { protection: { hidden: true } }, }) ``` --- ## Table-wide style defaults v2 adds a `defaults` option on table inputs for consistent theming across header, summary, and body cells. ```ts .table("renewals", { schema, rows, defaults: { header: { preset: "header.accent" }, summary: { preset: "summary.subtle" }, cells: { unlocked: { preset: "cell.input" }, locked: { preset: "cell.locked" }, hidden: { preset: "cell.hidden" }, }, }, }) ``` --- ## Grouped formulas v1 groups were mainly used for generated column structure. v2 adds explicit scope selectors so formulas can aggregate structural groups and runtime dynamic scopes. ```ts // v1: groups generate columns, no formula integration createExcelSchema() .group("regions", (group) => { group.column("amer", { accessor: (row) => row.byRegion.amer ?? 0 }); group.column("apac", { accessor: (row) => row.byRegion.apac ?? 0 }); group.column("emea", { accessor: (row) => row.byRegion.emea ?? 0 }); }) .build(); // v2: runtime-generated scopes use dynamic(), // and formulas aggregate them with refs.dynamic() createExcelSchema({ mode: "excel-table" }) .dynamic("regions", (builder, { ctx }) => { for (const region of ctx.regions) builder.column(region, { header: region, accessor: (row) => row.byRegion[region] ?? 0, totalsRow: { function: "sum" }, }); }) .column("total", { header: "Regional Total", formula: ({ refs, fx }) => fx.sum(refs.dynamic("regions")), totalsRow: { function: "sum" }, }) .column("average", { header: "Regional Avg", formula: ({ refs, fx }) => fx.round(fx.average(refs.dynamic("regions")), 0), totalsRow: { function: "average" }, }) .build(); ``` --- ## autoFilter in report mode v2 adds opt-in autoFilter support in report mode. ```ts .table("sales", { schema, rows, autoFilter: true, }) ``` ::callout{type="info"} Report-mode autoFilter is automatically disabled when merged expanded rows make Excel filtering invalid. :: --- ## Streaming: all new features included All v2 features work in streaming mode via `createWorkbookStream()`. There is no separate feature surface for buffered vs streaming: the same schema model applies. --- ## Constraints to be aware of | Constraint | Details | | ----------------------------------------------------- | ----------------------------------------------------- | | Formula summaries are report-mode only | Excel-table mode uses `totalsRow` instead | | Excel-table mode does not support sub-row expansion | Use report mode if your accessor returns arrays | | Excel-table formulas require unique effective headers | Structured references depend on unique column headers | | Report-mode autoFilter disabled with merged rows | Merged expanded rows make Excel filtering invalid | | `conditionalStyle` cannot set protection flags | Use base `style` for `locked` / `hidden` | --- ## Quick reference | Feature | v1 | v2 | | -------------------- | ------------------------------------- | --------------------------------------------------------------- | | Schema mode | Implicit report mode | Explicit: `"report"` (default) or `"excel-table"` | | Computed columns | JS-only via `accessor` | `accessor` (JS) or `formula` (live Excel) | | Summary rows | JS reducer (`init`/`step`/`finalize`) | JS reducer or `summary.formula(...)` | | Conditional styles | `dynamicStyle` (resolved once) | `dynamicStyle` or `conditionalStyle` (native Excel CF) | | Data validation | Not available | `validation` DSL on columns | | Hyperlinks | Not available | `hyperlink` callback on columns | | Sheet protection | Not available | `protection` on `.sheet()` | | Workbook protection | Not available | `protection` on `createWorkbook()` | | Cell protection | Not available | `style.protection.locked` / `.hidden` | | Table style defaults | Not available | `defaults` on `.table()` | | Native Excel tables | Not available | `{ mode: "excel-table" }` + `totalsRow` / `style` / `name` | | autoFilter | Not available | `autoFilter` on `.table()` (opt-in report, default excel-table) | | Group formulas | Groups generate columns only | `fx.sum(refs.group("id"))`, `fx.sum(refs.dynamic("id"))`, etc. | | Formula row model | Not available | `refs.column()` / `row.series()` / `expansion` | # v2 → typed-xlsx The primary package name is now `typed-xlsx`. If you were still using the original scoped package, there are no API changes in this migration. The only required change is the install command and import path. ```ts // before import { createExcelSchema, createWorkbook, createWorkbookStream, } from "@chronicstone/typed-xlsx"; // after import { createExcelSchema, createWorkbook, createWorkbookStream } from "typed-xlsx"; ``` If you publish templates, examples, or internal packages that depend on the previous package name, update their dependency list and import specifiers at the same time. The previous package name on npm now points users to `typed-xlsx`. # v4 → v5 Version 5 changes the types only. A workbook built with 4.x is built the same way with 5.0: same cells, styles, formulas and files. What changes is what the compiler accepts. Mistakes that used to compile, and were silently ignored or broke the file, are now type errors on the key at fault, with a message that says what would fit. Upgrade, run your type checker, and fix what it reports. Each case below shows the message and the fix. ## Excel table options on a report schema `totalsRow`, `name` and `style` on a table only work with excel-table schemas. A report schema used to accept them and ignore them. ```ts // 'totalsRow' is not a table option. Options: autoFilter, context, defaults, … workbook.sheet("Sales").table("sales", { schema: reportSchema, rows, totalsRow: true }); ``` Remove the option, or build the schema with `createExcelSchema({ mode: "excel-table" })` if you want a native table. Report schemas put totals in [summary rows](https://typed-xlsx.vercel.app/columns/summary-rows). ## Unknown options A misspelled option is an error everywhere: columns, tables, charts, marks and pivots. Chart and pivot specs returned from a callback used to skip this check entirely. ```ts .chart("revenue", ({ refs }) => ({ titel: "Revenue", // 'titel' is not a chart option. Options: at, axes, colors, legend, marks, size, title marks: [refs.table("sales").bar({ x: "month", y: "revenue" })], })) ``` Use the option named in the message. ## Options that don't fit the column kind Each column kind accepts its own options. `format` on a checkbox, `variants` on a plain column or `summary` on an excel-table column are errors now. ```ts // 'format' is not an option of checkbox columns. Options: accessor, autoWidth, condition, … .column("paid", { type: "checkbox", accessor: "paid", format: "0" }) ``` Remove the option, or change the column `type`. ## Accessors must hold a cell value An accessor path must point at a string, number, boolean, `Date`, null or undefined, or an array of those. A path to an object used to write `[object Object]`. Column kinds also check their field: a checkbox needs a boolean. ```ts // 'customer' doesn't hold a cell value (…). Fields that do: customer.name, qty, … .column("customer", { accessor: "customer" }) // 'qty' doesn't hold a boolean. Fields that do: paid .column("paid", { type: "checkbox", accessor: "qty" }) ``` Point at a field inside the object (`"customer.name"`), or use an accessor function. ## Charts and pivots check their columns - A chart `y` must be a numeric column, after its `transform`. - A pivot can only `"count"` a text, date or boolean column; `sum`, `average`, `min` and `max` need numbers. - Grouping a pivot field by year, quarter or month needs a date column that can't be blank. Give a nullable date column a `defaultValue`. - Pivot `rows` must be a non-empty list. Most of these used to produce a chart of zeros or a pivot Excel had to repair. ## `@ts-expect-error` comments may move Errors used to be reported on the whole `.column()` or `.table()` call as "No overload matches this call". They are now reported on the key. A `@ts-expect-error` above the call can become unused if the error now sits on a later line: move the comment to the line just above the key. ## Annotated schema types `build()` returns `ReportSchemaDefinition` or `ExcelTableSchemaDefinition`. If you annotate built schemas, use these types (or let TypeScript infer them). ## `WorkbookTableStream` The streamed table handle has had chart and pivot methods since 4.3. `WorkbookTableStream`, without the other type parameters, accepts any handle of that row type, so code that stores handles in a variable typed this way keeps compiling. ## Type-check time The new checks cost a little at compile time on large schemas: a 150-column schema takes about 0.3 s longer on TypeScript 7 (0.80 s instead of 0.46 s), and grows linearly with the number of columns. Schemas of a few dozen columns are within noise. ## Also new in 5.0 - An [agent skill](https://github.com/ChronicStone/typed-xlsx/blob/main/packages/core/skills/typed-xlsx/SKILL.md){rel=""nofollow""} for coding agents: `npx skills add ChronicStone/typed-xlsx`. - Helpers generic over a schema's row and context type (an export runner that forwards `context`) compile, in both the buffered and streaming builders. # Type-safe Excel (XLSX) reports for TypeScript :landing-blueprint