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.
import { createExcelSchema } from "typed-xlsx";
type Deal = { dealId: string; contractValue: number; discountPct: number };
const deals = createExcelSchema<Deal>()
.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:
- 1
D2:D3net revenue, from Contract and Discount on the same row - 2
E2:E3the tier, from the net revenue formula next to it
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) |
ctx | the 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:
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.
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:
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():
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:
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:
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, fx.sum(refs.dynamic("regions")) covers however many region columns the export generates.
Formulas in Excel tables
The same schema in excel-table mode 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.