Core Concepts

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.

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

import { createExcelSchema
 } from "typed-xlsx";

type Order
 = { product
: string; qty
: number; price
: number };

const report
 = createExcelSchema
<Order
>();
const table
 = createExcelSchema
<Order
>({ mode
: "excel-table" });

The column API is the same in both modes. Here is one schema written both ways:

Orders

Product

Qty

Price

Total

Desk

320

=(B3*C3)

Chair

140

=(B4*C4)

Lamp

=(B5*C5)

Total

=SUM(B3:B5)

=SUM(D3:D5)

Report mode

1D3:D5formulas use A1 references
2A6:D6a summary row, written by typed-xlsx below the data

Product

Qty

Price

Total

Desk

320

=([@Qty]*[@Price])

Chair

140

=([@Qty]*[@Price])

Lamp

=([@Qty]*[@Price])

Total

=SUBTOTAL(109,[Qty])

=SUBTOTAL(109,[Total])

Excel table mode

1A1:D1a native table: filter and sort buttons on every header
2D2:D4structured references, as Excel displays them
3A5:D5Excel's totals row, which recalculates when the table is filtered

Compare the modes

	report	excel-table
Output	a plain range	a native Excel table object
Rows below the data	summary rows, any number	one 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	an Excel table style, 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:

import { createExcelSchema
 } from "typed-xlsx";

type Order
 = { product
: string; qty
: number; price
: number };

const reportSchema
 = createExcelSchema
<Order
>()
  .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
<Order
>({ 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.

Comparison

How typed-xlsx differs from SheetJS and ExcelJS, and when each one is the better choice.

Row Model

How one input row becomes several worksheet rows, and how refs.column(), row.series(), expansion and summaries behave on expanded reports.