Building Workbooks
A workbook holds sheets, a sheet holds one or more tables, and each table is a schema plus rows.
import { createExcelSchema, createWorkbook } from "typed-xlsx";
type User = { name: string; email: string; role: string };
const users = createExcelSchema<User>()
.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.
Several sheets
Each .sheet() call adds a tab. Sheets are independent: their own schemas, row types and options.
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:
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 }] });
- 1
A1:B4table q1 - 2
A6:B8table q2, after one empty row (tableRowGap)
To place them side by side instead, set tablesPerRow on the sheet. Tables then fill each row of the layout left to right:
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 }],
});
- 1
A1:B3table revenue - 2
D1:E3table churn, one empty column to the right (tableColumnGap)
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 |
context | the schema's context, required when the schema declares one |
theme | a theme 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 |
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.
import { createExcelSchema, createWorkbook } from "typed-xlsx";
type Renewal = { account: string; currentArr: number; targetArr: number };
const renewals = createExcelSchema<Renewal>()
.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<void> | runtimes with a filesystem |
The streaming builder has more outputs, including Node and Web streams; see Output Targets.