How to add a chart in ExcelJS

ExcelJS builds the sheet. It cannot draw the chart. Here is how to get one into the file anyway.

A bar chart

const ExcelJS = require('exceljs')
const { addChart } = require('chartsheet')
const fs = require('fs')

async function main () {
  const wb = new ExcelJS.Workbook()
  const ws = wb.addWorksheet('Data')
  ws.addRow(['Month', 'Sales', 'Costs'])
  ws.addRow(['Jan', 120, 90])
  ws.addRow(['Feb', 150, 95])
  ws.addRow(['Mar', 180, 110])
  ws.addRow(['Apr', 140, 105])
  ws.getRow(1).font = { bold: true }

  let buffer = await wb.xlsx.writeBuffer()

  buffer = await addChart(buffer, {
    type: 'bar',
    title: 'Quarterly performance',
    xTitle: 'Month',
    yTitle: 'INR',
    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()

The snippets from here on are fragments — they assume they sit inside an async function, like the complete example above. await cannot go at the top level of a CommonJS file: Node reparses the file as an ES module and require then stops working.

A line chart

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

A pie chart

buffer = await addChart(buffer, {
  type: 'pie',
  title: 'Share of costs',
  legend: 'b',
  dataLabels: true,
  categories: "'Data'!$A$2:$A$5",
  series: [{ ref: "'Data'!$C$2:$C$5" }],
  anchor: { col: 13, row: 1 },
})

Several charts at once

const { addCharts } = require('chartsheet')

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

A worksheet holds exactly one drawing part and every chart on it is an anchor inside that part. Getting this wrong is the usual reason a hand-built file opens with only the first chart, or not at all. It is handled for you.

Putting a chart on a specific sheet

await addChart(buffer, { sheet: 'Summary', /* ... */ })

Without sheet, the chart goes on the first worksheet. An unknown name throws rather than silently placing the chart somewhere unexpected.

Styling

If Excel refuses to open the result

Excel says only "we found a problem with some content". Ask the validator what is actually wrong:

const { validate } = require('chartsheet')
console.log(await validate(buffer))

Questions