Charts and Pivots

Pivot Tables

Native Excel pivot tables and pivot charts over the tables you declare, with fields and aggregations checked by TypeScript.

A pivot table summarizes a table: one row per region, one column per month, the sum of revenue in each cell. Readers can then filter it, drag fields around or switch the sum to an average in Excel.

refs.table(id).pivot(...) describes a pivot over a table declared earlier in the chain, and .pivot(id, ...) places it on a sheet.

createWorkbook
()
  .sheet
("Sales")
  .table
("sales", { schema
: sales
, rows
 })
  .sheet
("Analysis")
  .pivot
("byRegion", ({ refs
 }) =>
    refs
      .sheet
("Sales")
      .table
("sales")
      .pivot
({
        rows
: ["region"],
        columns
: ["month"],
        values
: { revenue
: "sum" },
        filters
: ["channel"],
      }),
  );

Options

Option	Effect
`rows`	Fields listed down the left, outermost first, or a date grouped by year, quarter or month. Inner items are indented under their outer item, which shows the subtotal.
`columns`	One field spread across the top, one column per item. It can be a grouped date.
`values`	What to aggregate, keyed by column, as an amount or a percentage of a total.
`filters`	Fields shown above the pivot, which readers use to filter it.
`sort`	`"ascending"` (default), `"descending"`, or `"source"` for the order rows first appear in.
`totals`	Grand totals. `false` hides both, or `{ row: false }` hides the bottom row, `{ column: false }` the right column.
`style`	Built-in pivot style such as `"PivotStyleMedium9"`.
`theme`	A theme whose pivot slots style the pivot. Defaults to the source table's theme.
`banded`	Banded rows (`true`), or `{ rows, columns }`.
`grandTotalLabel`	Label of the grand totals. Defaults to `"Grand Total"`.
`at`	Top-left cell, filters included. Defaults to below the sheet's tables and pivots, keeping two rows free above the pivot as Excel does. An `at` that overlaps a table or another pivot throws.

Values

Each key of values is a column; its value is the aggregation. Use an object to name or format it, and a list to aggregate a column several ways.

createWorkbook
()
  .sheet
("Sales")
  .table
("sales", { schema
: sales
, rows
: [] })
  .pivot
("summary", ({ refs
 }) =>
    refs
.table
("sales").pivot
({
      rows
: ["region", "product"],
      values
: {
        revenue
: ["sum", { aggregate
: "average", name
: "Average sale", format
: "#,##0.00" }],
        orders
: { aggregate
: "max", name
: "Biggest order" },
        product
: "count",
      },
    }),
  );

The aggregations are sum, average, min, max and count. Only count works on text, dates or booleans. Values are named "Sum of Revenue", "Count of Product", and so on, unless you pass name. Sums, averages, minimums and maximums use the source column's number format unless you pass format.

Percentages

show turns a value into its share of a total. The pivot keeps the amounts underneath, so the percentages follow when readers filter it.

`show`	Each cell divided by	Adds up to 100%
`"percentOfTotal"`	the grand total	the whole pivot
`"percentOfRow"`	its row's grand total	each row
`"percentOfColumn"`	its column's grand total	each column

createWorkbook
()
  .sheet
("Sales")
  .table
("sales", { schema
: sales
, rows
: [] })
  .pivot
("channelMix", ({ refs
 }) =>
    refs
.table
("sales").pivot
({
      rows
: ["region"],
      columns
: ["channel"],
      values
: {
        // The amount, then each channel's share of the region's revenue.
        revenue
: ["sum", { aggregate
: "sum", show
: "percentOfRow" }],
      },
    }),
  );

Percentages are formatted "0.0%" unless you pass format, and named "Sum of Revenue (% of row)" unless you pass name. Without a column field every row is 100%, so "percentOfRow" needs one: TypeScript says ✗ "percentOfRow" needs a column field.

Dates

A date column can be grouped by "year", "quarter" or "month". Rows can nest several units, from the largest: ["year", "quarter", "month"] lists years, the quarters of each year, then their months. Readers can collapse a year or a quarter in Excel.

createWorkbook
()
  .sheet
("Sales")
  .table
("sales", { schema
: sales
, rows
: [] })
  .sheet
("Analysis")
  .pivot
("byMonth", ({ refs
 }) =>
    refs
      .sheet
("Sales")
      .table
("sales")
      .pivot
({
        rows
: [{ field
: "orderedAt", by
: ["year", "month"] }],
        columns
: ["region"],
        values
: { revenue
: "sum" },
      }),
  )
  .pivot
("byYear", ({ refs
 }) =>
    refs
      .sheet
("Sales")
      .table
("sales")
      .pivot
({
        rows
: ["region"],
        columns
: [{ field
: "orderedAt", by
: "year" }],
        values
: { revenue
: { aggregate
: "sum", show
: "percentOfColumn" } },
      }),
  );

A column or a filter takes one unit. Rows take one or several.
by: "month" alone puts January of every year together. Add "year" to keep years apart.
Groups follow the calendar whatever sort says, and dates are grouped in UTC, as they are written.
Excel names the groups in the reader's language when it refreshes the pivot: "Qtr1" and "Jan" become "Trimestre1" and "janv" in French.
Every row needs a date. With a single blank cell, Excel silently drops the grouping and lists each date. So only date columns that cannot be blank can be grouped, such as a required date or one with a defaultValue, and a blank found while writing throws an error.

Styles and themes

A pivot picks its look in this order:

style: one of Excel's built-in pivot styles, such as "PivotStyleMedium9".
theme: a spreadsheet theme.
The theme of its source table, from the table or its schema.
The classic theme, which tables without a theme use too.

So a pivot always matches the tables around it without extra code, and style is there when you want one of Excel's built-in looks instead. A theme styles pivots through six slots, each bound by default to a table slot of the same theme:

Slot	Styles	Bound by default to
`pivotBody`	Every cell. Its border is drawn around each cell.	`cellBase`
`pivotFrame`	The border around the whole pivot.	nothing: the body's border
`pivotHeader`	Header rows and filter labels: colours and fonts.	`header`, without its border
`pivotSubheading`	Rows that hold a subtotal, such as a year above its quarters.	bold text
`pivotTotal`	The grand total row and column.	`summary`
`pivotStripe`	Banded rows or columns, when the pivot sets `banded`.	the `stripeFill` token, a light grey

Override a slot like any other, with extend. The override sits on the binding, so pivotHeader: { font: { italic: true } } keeps the header's fill and still follows it.

const navy
 = { style
: "medium" as const
, color
: { rgb
: "0B1220" } };
const line
 = { style
: "thin" as const
, color
: { rgb
: "CBD5E1" } };

const reportTheme
 = spreadsheetThemes
.slate
.extend
({
  slots
: {
    pivotFrame
: { border
: { top
: navy
, right
: navy
, bottom
: navy
, left
: navy
 } },
    pivotBody
: { border
: { top
: line
, right
: line
, bottom
: line
, left
: line
 } },
    pivotTotal
: { fill
: { color
: { rgb
: "C7D2FE" } } },
  },
});

createWorkbook
()
  .sheet
("Sales")
  // The pivot below follows this table's theme...
  .table
("sales", { schema
: sales
, rows
: [], theme
: spreadsheetThemes
.slate
 })
  .pivot
("byRegion", ({ refs
 }) =>
    refs
      .table
("sales")
      .pivot
({ rows
: ["region"], columns
: ["product"], values
: { revenue
: "sum" } }),
  )
  // ...this one uses its own, with banded rows.
  .pivot
("framed", ({ refs
 }) =>
    refs
.table
("sales").pivot
({
      rows
: ["region"],
      columns
: ["product"],
      values
: { revenue
: "sum" },
      theme
: reportTheme
,
      banded
: true,
    }),
  );

The theme becomes a custom pivot style in the file, so readers see it in Excel's pivot style gallery and keep it when they refresh or rearrange the pivot.

What a pivot style can change is narrower than a cell style, and the types follow it:

Pivot slots take font colour, bold, italic, underline and strike, fills and borders. Font names and sizes, number formats and alignment are not part of a pivot style: numbers keep their format per value.
pivotHeader takes no border. Excel draws a header border on the first header row only, so header lines come from pivotBody and pivotFrame.
Along the pivot's edges, the frame wins over the totals' and subheadings' own borders.
Excel restyles the pivot when it refreshes it. The cells written into the file carry the same colours, so apps that do not refresh pivots show them too.
Columns keep the widths written with the pivot, wide enough for its headers: Excel does not narrow them when it refreshes.

What TypeScript checks

The fields are checked against the source table after its select is applied:

createWorkbook
()
  .sheet
("Sales")
  .table
("sales", { schema
: sales
, rows
: [] })
  .pivot
("bad", ({ refs
 }) =>
    // "region" holds text, so it can only be counted
    refs
.table
("sales").pivot
({ rows
: ["region"], values
: { region: "sum" } }),
Type '"sum"' is not assignable to type 'PivotValueEntry<"count"> | readonly PivotValueEntry<"count">[] | undefined'.
  );

The compiler also rejects an unknown column, a value with no aggregation (values: { revenue: [] }), a field used twice across rows, columns and filters (✗ field "region" is used twice), more than one column field, a pivot without values, and a pivot id already used on the sheet. For dates, it rejects grouping a column that is not a date or may be blank, units out of order such as ["month", "year"], and several units on a column or a filter. It also rejects a pivot with both style and theme (✗ a pivot takes a built-in style or a theme, not both), and pivot slots given a number format, a font size or, for pivotHeader, a border.

Pivot charts

refs.pivot(id) gives the marks of a pivot declared earlier. A pivot chart plots the whole pivot: row items on the x axis, one series per column item and value. Excel keeps it in sync with the pivot, so filtering the pivot updates the chart.

createWorkbook
()
  .sheet
("Sales")
  .table
("sales", { schema
: sales
, rows
: [] })
  .sheet
("Analysis")
  .pivot
("byRegion", ({ refs
 }) =>
    refs
      .sheet
("Sales")
      .table
("sales")
      .pivot
({
        rows
: ["region"],
        columns
: ["month"],
        values
: { revenue
: "sum" },
        sort
: "source",
      }),
  )
  .chart
("byRegionChart", ({ refs
 }) => ({
    title
: "Revenue by region",
    marks
: [refs
.pivot
("byRegion").bar
({ stack
: true })],
  }));

Pivot marks are bar, line, area, pie and doughnut, with the same styling options as table marks. A pivot mark is the only mark of its chart, and a pie needs a pivot without columns and with a single value: both are checked by TypeScript.

How a pivot is written

Excel rebuilds the pivot from its source table when the file opens, so the source stays the single truth and readers can refresh after editing it.
The library also writes the totals into the cells, so the file reads correctly before that refresh, for example in previews and apps that do not refresh pivots.
Items keep the order from sort. Items that appear after a refresh in Excel are added at the end.
In report mode, formula columns are computed by Excel: they can be values (their cells stay empty until the refresh), not rows, columns or filters.
A pivot over a native Excel table points at the table by name, so it follows rows added in Excel.
Tables that expand rows into sub-rows cannot be pivoted.

Streaming

A streamed table makes pivots with sales.pivot(...), and sheet.pivot(id, ...) returns the pivot's marks. Rows are tallied as they are committed and are not kept, so declare the pivot before the first commit.

const workbook
 = createWorkbookStream
();
const sales
 = await workbook
.sheet
("Sales").table
("sales", { schema
 });

const analysis
 = workbook
.sheet
("Analysis");
const byRegion
 = analysis
.pivot
(
  "byRegion",
  sales
.pivot
({ rows
: ["region"], columns
: ["month"], values
: { revenue
: "sum" } }),
);
analysis
.chart
("byRegionChart", { marks
: [byRegion
.bar
({ stack
: true })] });

for await (const rows
 of batches
) {
  await sales
.commit
({ rows
 });
}

await workbook
.writeToFile
("./sales.xlsx");

Axes and Styling

Titles, axes, a secondary axis, legends, colors and data labels.

v0 → v1

Every breaking change from v0.x to v1.0 with before/after examples.