Excel Table Mode

Overview

Native Excel tables — autoFilter, styled banded rows, totals row, and structured formula references.

Excel table mode wraps your data in a native Excel <table> object (xl/tables/table1.xml in the OOXML package). This is the same object you create in Excel when you press Ctrl+T or Format as Table.

The result is a first-class Excel data table: sortable and filterable with native dropdowns, styled with a theme (Light, Medium, or Dark presets), optionally capped with a totals row, and referenced by name in formulas across the workbook.

Creating an excel-table schema

Pass { mode: "excel-table" } to createExcelSchema:

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

const dealSchema
 = createExcelSchema
<{
  dealId
: string;
  account
: string;
  stage
: string;
  amount
: number;
  closeDate
: string;
}>({ mode
: "excel-table" })
  .column
("dealId", { header
: "Deal ID", accessor
: "dealId" })
  .column
("account", { header
: "Account", accessor
: "account" })
  .column
("stage", { header
: "Stage", accessor
: "stage" })
  .column
("amount", {
    header
: "Amount",
    accessor
: "amount",
    style
: { numFmt
: "$#,##0.00" },
  })
  .column
("closeDate", { header
: "Close Date", accessor
: "closeDate" })
  .build
();

The schema is now an ExcelTableSchemaDefinition. Pass it to a workbook builder the same way as a report schema — the builder detects the mode and routes it through the excel-table output path.

Using the schema in a workbook

import { createExcelSchema
, createWorkbook
 } from "@chronicstone/typed-xlsx";

const dealSchema
 = createExcelSchema
<{
  dealId
: string;
  account
: string;
  amount
: number;
}>({ mode
: "excel-table" })
  .column
("dealId", { header
: "Deal ID", accessor
: "dealId" })
  .column
("account", { header
: "Account", accessor
: "account" })
  .column
("amount", { header
: "Amount", accessor
: "amount", style
: { numFmt
: "$#,##0.00" } })
  .build
();

const workbook
 = createWorkbook
();
workbook
.sheet
("Pipeline").table
("deals", {
  schema
: dealSchema
,
  rows
: [
    { dealId
: "D-001", account
: "Acme Corp", amount
: 24000 },
    { dealId
: "D-002", account
: "Globex Inc", amount
: 18500 },
  ],
  name
: "Pipeline", // Excel table name (optional, defaults to the table ID)
  style
: "TableStyleMedium2", // default
  autoFilter
: true, // default for excel-table mode
  totalsRow
: false, // default
});

const buffer
 = workbook
.toBuffer
();

Constraints vs report mode

Excel table mode is intentionally more constrained. These constraints come from the OOXML spec — Excel simply doesn't allow them inside a <table> object:

Feature	Report mode	Excel table mode
Summary rows	Yes	No — use totals row
Sub-row expansion	Yes	No — throws at output time
Merged header cells	Yes	Flat column groups only

If you attempt to use a schema with sub-row expansion (an accessor that returns an array) in excel-table mode, the library throws at output time with a descriptive error.

Formula columns in excel-table mode

Formula columns work exactly as in report mode. The DSL is identical. The only difference is the emitted formula string:

import { createExcelSchema
 } from "@chronicstone/typed-xlsx";

const schema
 = createExcelSchema
<{ qty
: number; unitPrice
: number }>({ mode
: "excel-table" })
  .column
("qty", { header
: "Qty", accessor
: "qty" })
  .column
("unitPrice", { header
: "Unit Price", accessor
: "unitPrice" })
  .column
("lineTotal", {
    header
: "Line Total",
    // Emits: =[@Qty]*[@UnitPrice]  (structured reference, not A1 style)
    formula
: ({ row
 }) => row
.ref
("qty").mul
(row
.ref
("unitPrice")),
    style
: { numFmt
: "$#,##0.00" },
  })
  .build
();

Structured references ([@Qty]) remain correct when users insert rows, sort the table, or move it — making them significantly more robust than A1 references in interactive Excel files.

Column groups in excel-table mode

Column groups (.group()) are supported in excel-table mode with flat headers only. They are useful for runtime-generated columns, grouped selection, and grouped formula aggregations like row.group("id").sum(). Excel table mode still renders a flat single header row — there is no merged/group header band.

One useful mental model is that you make two independent choices:

choose schema mode: report or excel-table
choose workbook builder: buffered or streaming

Streaming support

Excel-table schemas work with createWorkbookStream() the same way as report schemas. Commit row batches with .commit({ rows }) and finalize with .writeToFile() or another output method. The excel-table XML is assembled after the last commit.

Scope and Modes

Formula scope rules, predecessor ordering, group scope, A1 vs structured references, and runtime validation.

Table Styles

Apply native Excel table styles — Light, Medium, and Dark presets — to excel-table mode schemas.