How to add a pivot table with SheetJS

There is no pivot table function in XLSX.utils, and there is no paid build that adds one. The parts can be written into the finished file instead.

A pivot table from a sheet of data

const fs = require('fs')
const XLSX = require('xlsx')
const { addPivotTable } = require('chartsheet')

async function main () {
  const rows = [
    ['Region', 'Product', 'Sales'],
    ['East', 'Alpha', 100],
    ['East', 'Beta', 150],
    ['West', 'Alpha', 200],
    ['West', 'Beta', 120],
    ['North', 'Alpha', 90],
    ['North', 'Beta', 175],
  ]

  const wb = XLSX.utils.book_new()
  XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet(rows), 'Data')
  XLSX.utils.book_append_sheet(wb, XLSX.utils.aoa_to_sheet([[]]), 'Report')

  let buffer = XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' })

  buffer = await addPivotTable(buffer, {
    sourceSheet: 'Data',
    sourceRef: 'A1:C7',        // include the header row
    targetSheet: 'Report',
    anchor: 'A3',
    rows: ['Region'],
    columns: ['Product'],
    values: [{ field: 'Sales', fn: 'sum' }],
  })

  fs.writeFileSync('report.xlsx', buffer)
}

main()

The Report sheet has to exist before the pivot table is written to it, which is what the empty aoa_to_sheet([[]]) is for. Open the file and the table is live: click it and the field list appears, drag fields around, refresh it.

The field names come from your header row

rows, columns, filters and each values[].field are matched against the first row of sourceRef. Name one that is not there and the error lists the names that are, rather than producing a broken file:

Error: field "Revenue" is not a column of the source range
       (found: Region, Product, Sales)

Aggregates

fn defaults to sum. The full set is sum, count, average, max, min, product, countNums, stdDev, stdDevp, var and varp — the same list Excel offers in the value field settings.

values: [
  { field: 'Sales', fn: 'sum' },
  { field: 'Sales', fn: 'average', name: 'Average order' },
]

The same field can appear more than once with different aggregates, as it can in Excel.

Charts too

SheetJS keeps chart writing in its paid tier. Charts and pivot tables are separate parts and do not collide, so both can go into the same file:

const { addChart, addPivotTable } = require('chartsheet')

buffer = await addPivotTable(buffer, pivotSpec)
buffer = await addChart(buffer, chartSpec)

Adding a chart with SheetJS covers the chart side.

Which version of SheetJS

Any of them. The pivot parts are written into the finished bytes, so it does not matter which build produced them.

Worth knowing if you install from npm: xlsx there stops at 0.18.5, published in March 2022. SheetJS moved distribution to its own CDN and the npm package carries no deprecation notice, so nothing tells you. npm audit reports that version as high severity with fixAvailable: false. This library works the same on output from 0.18.5, from the current CDN builds, and from the community republishes.

Checking the result

const { validate } = require('chartsheet')

const result = await validate(buffer)
if (!result.valid) console.error(result.errors)

Pivot wiring fails silently: an unbound cacheId, a table part with no relationship to its cache, a recordCount that disagrees with the records written. Excel reports all three as “we found a problem with some content” and nothing more. There is a page that checks a file in the browser.