Pivot tables

PivotTables are objects in ws["!pivots"]. A sheet can have several.

Set !pivots on a worksheet to create pivot metadata in a new XLSX workbook. For an existing template workbook, use utils.template_add_pivot. See Template: pivot tables for that call.

Pivot object

Key	Meaning
source	where the data is
fields	one record per source column
rows, cols	arrays of field indexes (or objects). -2 stands for the “Values” block
filters	field indexes shown as page filters
values	summary values
style	display options
props	captions
origin	upper-left cell of the pivot. Filters are drawn from it, and the table starts one row below them

source. { sheet: "Data", ref: "A1:C30" }. You can use sidx for the sheet index. ref is a string or a range object. Or use { name: "mydata" } for a defined name.

fields. { name, t, z?, l? }. name is the header text. t is the type: "s" string, "n" number or "d" date. z is a number format. l is a list of values that sets the sort order and acts as a point filter. name and t are required. The array must have one entry per source column.

rows and cols. Plain numbers, or objects { field, order, key, collapse }. order is "ascending", "descending" or "manual" (keep the order of l). key sorts by a value field. collapse collapses sub-rows.

values. { name, field, op, z? }. op is one of sum, count, average, max, min, product, countNums, stdDev, stdDevp, var or varp.

style. rowhead (default true), colhead (true), rowstripe (false), colstripe (false), lastcol (true).

props. collabel (default “Column Labels”), rowlabel (default “Row Labels”).

const XLSX = require("@agent-sheet/wasm");
const wb0 = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb0, XLSX.utils.aoa_to_sheet([
  ["a", "b", "c"], ["foo", 1, 1], ["foo", 2, 2], ["bar", 1, 3], ["bar", 2, 4], ["baz", 1, 5], ["baz", 2, 6],
]), "Data");
XLSX.utils.book_append_sheet(wb0, XLSX.utils.aoa_to_sheet([[]]), "Pivot");
const wb = XLSX.read(XLSX.write(wb0, { type: "buffer", bookType: "xlsx" }), { template: true });

XLSX.utils.template_add_pivot(wb, "Pivot", {
  source: { sheet: "Data", ref: "A1:C7" },
  fields: [
    { name: "a", z: "General", t: "s", l: ["bar", "foo", "baz"] },
    { name: "b", z: "General", t: "n" },
    { name: "c", z: "General", t: "n" },
  ],
  rows: [{ field: 0, order: "descending" }],
  cols: [-2],
  filters: [1],
  values: [{ name: "Sum of C", field: 2, op: "sum" }],
  style: { rowhead: true, colhead: true, rowstripe: false, colstripe: false, lastcol: true },
  props: { collabel: "Totals" },
  origin: "A1",
});
const out = XLSX.write(wb, { type: "buffer", bookType: "xlsx", template: true });
const files = XLSX.read(out, { bookFiles: true }).files;
console.log(Object.keys(files).filter((k) => /pivot/i.test(k)));

Output

[
  'xl/pivotCache/pivotCacheDefinition1.xml',
  'xl/pivotTables/_rels/pivotTable1.xml.rels',
  'xl/pivotTables/pivotTable1.xml'
]

template_add_pivot returns the index of the new pivot.

Build a new workbook

const XLSX = require("@agent-sheet/wasm");
const wb0 = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb0, XLSX.utils.aoa_to_sheet([
  ["a", "b", "c"], ["foo", 1, 1], ["foo", 2, 2], ["bar", 1, 3], ["bar", 2, 4], ["baz", 1, 5], ["baz", 2, 6],
]), "Data");
XLSX.utils.book_append_sheet(wb0, XLSX.utils.aoa_to_sheet([[]]), "Pivot");
const wb = wb0;

wb.Sheets.Pivot["!pivots"] = [{
  source: { sheet: "Data", ref: "A1:C7" },
  fields: [
    { name: "a", z: "General", t: "s", l: ["bar", "foo", "baz"] },
    { name: "b", z: "General", t: "n" },
    { name: "c", z: "General", t: "n" },
  ],
  rows: [{ field: 0, order: "descending" }],
  cols: [-2],
  filters: [1],
  values: [{ name: "Sum of C", field: 2, op: "sum" }],
  style: { rowhead: true, colhead: true, rowstripe: false, colstripe: false, lastcol: true },
  props: { collabel: "Totals" },
  origin: "A1",
}];
const out = XLSX.write(wb, { type: "buffer", bookType: "xlsx" });
const files = XLSX.read(out, { bookFiles: true }).files;
console.log(Object.keys(files).filter((k) => /pivot/i.test(k)));

Output

[
  'xl/pivotCache/pivotCacheDefinition1.xml',
  'xl/pivotTables/_rels/pivotTable1.xml.rels',
  'xl/pivotTables/pivotTable1.xml'
]

Keep existing pivots

Template mode keeps existing PivotTable parts unchanged when you do not edit them. Prefer it when the original file content must stay untouched. Prefer text source headers: with a numeric header, the pivot field keeps the supplied fields[i].name rather than replacing it with the header text. See Known differences. See Template editing.