How to add a chart with SheetJS

The community build of SheetJS writes sheets, not charts. Charts sit in the paid tier. Here is how to get one into the file without it.

Example

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

async function main () {
  const wb = XLSX.utils.book_new()
  const ws = XLSX.utils.aoa_to_sheet([
    ['Month', 'Sales', 'Costs'],
    ['Jan', 120, 90],
    ['Feb', 150, 95],
    ['Mar', 180, 110],
    ['Apr', 140, 105],
  ])
  XLSX.utils.book_append_sheet(wb, ws, 'Data')

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

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

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

main()

Nothing about this is SheetJS-specific. Charts are added to finished .xlsx bytes, so the same call works on a workbook from ExcelJS, from SheetJS, or from anything else that emits a valid file.

Chart types and options

Bar, column, line, pie, doughnut, area, scatter and radar, with titles, axis titles, multiple series, stacking, data labels, series colours, number formats and legend placement. The API reference lists everything.

Charts on a round trip

Reading an .xlsx with SheetJS and writing it back drops any charts it contained, because charts are not part of what the community build models. Capture and restore handles that, the same way it does for ExcelJS.

Questions

Pivot tables too

SheetJS has no pivot table API either — not in Community, and pivot creation is not among the Pro builds. The same approach writes a real pivot table into a workbook SheetJS produced.