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.
// v1 — implicit report mode (still works in v2)
createExcelSchema<Row>().column(...).build()
// v2 — explicit excel-table mode
createExcelSchema<Row>({ 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 <table> 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.
// v1 — precompute in JS
.column("net", {
accessor: (row) => row.contractValue * (1 - row.discountPct),
style: { numFmt: '"$"#,##0.00' },
})
// v2 — live Excel formula
.column("net", {
formula: ({ row, 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.
// 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) |
totalsRow on columns instead.Excel-table mode — new column and table options
If you adopt { mode: "excel-table" }, these options become available:
On columns:
.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:
.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.
// 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: ({ row, refs, fx }) => fx.safeDiv(refs.column("amount"), refs.column("quota")),
style: { numFmt: "0.0%" },
conditionalStyle: (conditional) =>
conditional
.when(({ row, refs }) => refs.column("attainment").lt(0.5), {
fill: { color: { rgb: "FEE2E2" } },
font: { color: { rgb: "991B1B" }, bold: true },
})
.when(({ row, refs, fx }) =>
fx.and(refs.column("attainment").gte(1), refs.column("status").eq("won")),
{
fill: { color: { rgb: "DCFCE7" } },
font: { color: { rgb: "166534" }, bold: true },
},
),
})
conditionalStyle cannot toggle protection flags (locked / hidden). Use base style for those.Data validation DSL
v2 introduces native Excel data validation on columns.
.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:
.column("score", {
accessor: "score",
validation: (v) =>
v.custom(({ row, refs }) => refs.column("score").gte(refs.column("amount"))),
})
Hyperlinks
v2 adds native hyperlink support. Link metadata is separate from the rendered cell value.
.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:
createWorkbook({
protection: {
password: "workbook-secret",
structure: true,
},
});
Sheet protection:
.sheet("Protected", {
protection: {
password: "sheet-secret",
selectUnlockedCells: true,
selectLockedCells: false,
},
})
Cell-level protection via style:
// Editable input cell
.column("input", {
accessor: "input",
style: { protection: { locked: false } },
})
// Hidden formula cell
.column("formulaValue", {
formula: ({ row, 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.
.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.
// v1 — groups generate columns, no formula integration
createExcelSchema<Row>()
.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<Row, { regions: string[] }>({ 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.
.table("sales", {
schema,
rows,
autoFilter: true,
})
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 |