API reference

Six functions. Every one takes and returns .xlsx bytes, so anything that produces a valid workbook can be used with it.

addChart(workbook, spec)

Adds one chart. workbook is a Buffer, Uint8Array or ArrayBuffer. Returns a Promise<Buffer>.

The short snippets below are fragments. They assume they are inside an async function, because await cannot sit at the top level of a CommonJS file — Node reparses the file as an ES module and then require stops working. The full examples on this page are complete and run as they are.

const buffer = await addChart(input, {
  type: 'bar',
  title: 'Quarterly performance',
  categories: "'Data'!$A$2:$A$5",
  series: [{ nameRef: "'Data'!$B$1", ref: "'Data'!$B$2:$B$5" }],
})

Spec

Series

addCharts(workbook, specs)

Adds several charts in one call. Equivalent to calling addChart repeatedly.

const buffer = await addCharts(input, [barSpec, lineSpec, pieSpec])

addPivotTable(workbook, spec)

Writes a native pivot table into a finished workbook. ExcelJS has no pivot table API in any released version — why that is.

const buffer = await addPivotTable(input, {
  sourceSheet: 'Data',
  sourceRef: 'A1:C500',
  targetSheet: 'Report',
  anchor: 'A3',
  rows: ['Region'],
  columns: ['Product'],
  values: [{ field: 'Sales', fn: 'sum' }],
})

Spec

At least one of rows or columns is required. fn is one of sum, count, average, max, min, product, countNums, stdDev, stdDevp, var, varp; it defaults to sum.

Aggregation is left to Excel. The cache is written with refreshOnLoad, so Excel computes rows, columns and totals from the cached records when the file opens.

addPivotTables(workbook, specs)

Adds several pivot tables in one call.

const buffer = await addPivotTables(input, [byRegion, byQuarter])

captureCharts(workbook)

Reads every chart on every sheet, following each chart's own relationships so colour and style parts come with it. Returns a plain serialisable record.

restoreCharts(workbook, record)

Writes captured charts back onto a workbook, matching sheets by name. Sheets that were renamed, removed, or already carry a drawing are skipped rather than guessed at.

preserveCharts(original, rewritten)

Convenience wrapper for the pair above — carry the charts from original onto rewritten. Why you need this.

capturePivotTables(workbook)

Reads every pivot table on every sheet, with the cache definition and records behind each one. A table is found through its worksheet's relationships, not through the sheet XML, and it reaches its cache the same way. Returns a plain serialisable record.

restorePivotTables(workbook, record)

Writes captured pivot tables back, matching sheets by name, renumbering the cache parts so nothing collides and re-declaring each cache in <pivotCaches> with a fresh id. Restored caches are marked refreshOnLoad, so Excel rebuilds every total from the sheet as the file opens rather than showing figures captured before your edit. Sheets that were renamed, removed, or already carry a pivot are skipped.

preservePivotTables(original, rewritten)

Convenience wrapper for the pair above. Answers ExcelJS #261, open since 2017.

preserveAll(original, rewritten)

Charts and pivot tables in one call. ExcelJS drops both, and most real templates have both.

const output = await preserveAll(original, rewritten)

validate(workbook)

Checks the package for the defects that make Excel refuse a file. Returns { valid, errors, warnings }. Works on any .xlsx.

const { valid, errors } = await validate(buffer)

It reports:

• relationships pointing at parts that are not in the package
• missing or wrong content-type overrides — including the case the generic xml default hides
• more than one <drawing> on a worksheet, or one placed before </sheetData>
• drawing parts that are related to a sheet but never referenced, whose charts silently do not render
• duplicate cNvPr shape ids within a drawing
• chart parts with no series, or with unprefixed elements

pivotTableCount(record)

Number of pivot tables in a record from capturePivotTables. Useful in tests.

chartCount(record)

Number of charts in a record from captureCharts. Useful in tests.

Errors

Bad input throws rather than producing a file Excel will reject later:

• chart spec needs at least one entry in series
• unknown chart type "…"
• series[0] needs a ref, e.g. "'Sheet1'!$B$2:$B$10"
• no worksheet named "…" (found: …)
• a scatter chart needs categories or an xRef on every series