Template: pivot tables

template_add_pivot(wb, sheet, pivot) adds a PivotTable and returns its index. The pivot object is described in Pivot tables.

Steps:

1. Read the workbook with template: true.
2. Make sure the sheet that will hold the pivot exists. It can be empty.
3. Call template_add_pivot.
4. Write with template: true.

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 mode preserves PivotTable parts you do not edit. A normal write can rebuild represented metadata, but does not promise untouched file parts.