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.
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();
{
customer: "Acme",
monthlyAmounts: [100, 120, 140],
}- 1
A2:A4customer has one value, so it is merged across the three rows - 2
B2:B4monthlyAmounts has three values, one per physical row
Sub-row expansion is report-mode only. In excel-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.
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();
- 1
C2:C4one formula per physical row, each pointing at its own row
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.
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();
- 1
B2:B4row.series("monthlyAmount") covers the logical row's span - 2
C2:C4one result for the logical row, merged (expansion: "single")
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.
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();
- 1
C2:C4a merged value is referenced through its first row, C2 - 2
D2:D4computed per physical row - 3
E2:E4aggregates those results once per logical row
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 |
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();
- 1
B2:B4single - 2
C2:C4expand
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 columncolumn.rows()resolves one value per logical row first, then aggregates those
With two customers where Acme expanded into three rows, the two averages differ:
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();
- 1
B6column.cells(): (100 + 120 + 140 + 300) / 4 = 165 - 2
B7column.rows(): (120 + 300) / 2 = 210, each customer counts once
See 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 covers the full DSL, and Scope & References covers which columns a formula may reference.
Schema Modes
Report mode writes a laid-out range with titles and summary rows; excel-table mode writes a native Excel table with filters and a totals row. How to choose.
Schema Context
Pass export-time inputs such as regions, locale or feature flags into a schema, and use them in columns, formulas, styles and labels.