Schema Modes

Every schema you create belongs to one of two modes. The mode is the most important decision you make when designing a schema — it controls output format, available features, and how formula columns reference cells.

flowchart TD
  A[Choose schema mode] --> B{What kind of worksheet behavior do you need?}
  B -->|Custom report layout\nsub-row expansion\nsummary rows| C[Report mode]
  B -->|Native Excel table\nautoFilter\ntotals row| D[Excel table mode]
  C --> E[Plain worksheet range]
  D --> F[Native Excel table object]

The two modes

import { 
createExcelSchema
 } from "typed-xlsx";

// Report mode (default)
const 
reportSchema
 = 
createExcelSchema
<{ 
name
: string; 
amount
: number }>();

// Excel table mode
const 
tableSchema
 = 
createExcelSchema
<{ 
name
: string; 
amount
: number }>({ 
mode
: "excel-table" });

Report mode

Report mode produces a plain worksheet range. Rows are written directly into the sheet with no native Excel table object. Summary rows can appear below the data, structural groups and dynamic columns are supported, and multi-value columns can expand a single logical row into multiple physical rows.

This is the right choice when you need:

• Summary rows (totals, subtotals, tax lines)
• Sub-row expansion (e.g. one order → multiple line items)
• Full control over post-data layout

Excel table mode

Excel table mode wraps the data in a native Excel <table> object. This enables native sorting and filtering dropdowns, a styled banded-row appearance, an optional totals row with built-in aggregate functions, and structured column references in formula columns.

This is the right choice when you need:

• Native Excel autoFilter / sort dropdowns out of the box
• A styled table (Light, Medium, or Dark presets)
• A totals row with one-click aggregates
• The table to behave like a proper Excel data table for pivot tables or Power Query

Feature comparison

Decision guide

Use excel-table mode when:

• You want native Excel filtering and sorting with no extra configuration
• You want a built-in totals row (sum, average, count, etc.)
• You want the table to have an Excel-native visual style
• Your consumers will use the data in pivot tables or Power Query

Use report mode when:

• You need summary rows (custom totals, VAT lines, running totals)
• You need sub-row expansion (one logical row → many physical rows)
• You need fully custom post-data layout

What the mode controls at schema build time

When you call .build(), the schema is frozen as either a ReportSchemaDefinition or an ExcelTableSchemaDefinition. These are distinct types — the workbook builder uses them to determine which output path to take.

Formula columns in report mode emit A1-style cell references. Formula columns in excel-table mode emit structured references. The formula DSL — refs.column(), refs.group(), refs.dynamic(), fx.round(), arithmetic operators — is identical in both modes; only the emitted string differs.

flowchart LR
  A[Same schema builder API] --> B[Same formula DSL]
  B --> C{Mode}
  C -->|report| D[A1 refs like C2 or D5]
  C -->|excel-table| E[Structured refs like at Qty]

import { 
createExcelSchema
 } from "typed-xlsx";

// Same formula DSL, different emitted strings
const 
reportSchema
 = 
createExcelSchema
<{ 
qty
: number; 
price
: number }>()
  .
column
("qty", { 
accessor
: "qty", 
header
: "Qty" })
  .
column
("price", { 
accessor
: "price", 
header
: "Price" })
  .
column
("total", {
    
header
: "Total",
    
formula
: ({ 
row
, 
refs
 }) => 
refs
.
column
("qty").
mul
(
refs
.
column
("price")),
    // Emits: =A2*B2 for this exact column order (A1 style)
  })
  .
build
();

const 
tableSchema
 = 
createExcelSchema
<{ 
qty
: number; 
price
: number }>({ 
mode
: "excel-table" })
  .
column
("qty", { 
accessor
: "qty", 
header
: "Qty" })
  .
column
("price", { 
accessor
: "price", 
header
: "Price" })
  .
column
("total", {
    
header
: "Total",
    
formula
: ({ 
row
, 
refs
 }) => 
refs
.
column
("qty").
mul
(
refs
.
column
("price")),
    // Emits: =[@[Qty]]*[@[Price]] (structured reference)
  })
  .
build
();

Both schemas use the same .column() API, the same formula DSL, and the same select, context, group, and dynamic-column APIs. The mode changes the output contract, not the authoring experience.

One helpful way to think about the library is that you make two independent decisions:

1. Choose the schema mode: report or excel-table
2. Choose the workbook builder: buffered or streaming