ExcelJS charts disappearing after a read and write

You load a template that has charts, change one cell, save it — and every chart is gone. No error, no warning.

Reproducing it

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

async function main () {
  // a template with a chart in it, standing in for yours
  const wb = new ExcelJS.Workbook()
  const ws = wb.addWorksheet('Data')
  ws.addRow(['Month', 'Sales'])
  ws.addRow(['Jan', 120])
  ws.addRow(['Feb', 150])
  const template = await addChart(await wb.xlsx.writeBuffer(), {
    type: 'bar',
    categories: "'Data'!$A$2:$A$3",
    series: [{ ref: "'Data'!$B$2:$B$3" }],
  })
  fs.writeFileSync('template.xlsx', template)

  // open it, change one number, write it back — the ordinary thing
  const edit = new ExcelJS.Workbook()
  await edit.xlsx.load(template)
  edit.getWorksheet('Data').getCell('B2').value = 999
  const rewritten = await edit.xlsx.writeBuffer()
  fs.writeFileSync('out.xlsx', rewritten)

  console.log('charts before:', chartCount(await captureCharts(template)))  // 1
  console.log('charts after: ', chartCount(await captureCharts(rewritten))) // 0

  // out.xlsx opens without complaint. It has your edit. It has no chart.
}

main()

This is not a bug you can configure away. It has been reported on ExcelJS in 2020 ("charts are disappearing from the worklist"), in 2021 ("read excel file with chart and write back but the chart is lost") and in 2023 ("accept and preserve files with charts"). All three are still open. The project's last release was October 2023 and its last commit January 2024, with 143 pull requests waiting.

Keeping the charts

Take a copy of the chart parts before ExcelJS touches the file, then put them back on the output:

const fs = require('fs')
const ExcelJS = require('exceljs')
const { addChart, preserveCharts, chartCount, captureCharts } = require('chartsheet')

async function main () {
  const wb = new ExcelJS.Workbook()
  const ws = wb.addWorksheet('Data')
  ws.addRow(['Month', 'Sales'])
  ws.addRow(['Jan', 120])
  ws.addRow(['Feb', 150])
  const original = await addChart(await wb.xlsx.writeBuffer(), {
    type: 'bar',
    categories: "'Data'!$A$2:$A$3",
    series: [{ ref: "'Data'!$B$2:$B$3" }],
  })

  const edit = new ExcelJS.Workbook()
  await edit.xlsx.load(original)
  edit.getWorksheet('Data').getCell('B2').value = 999
  const rewritten = await edit.xlsx.writeBuffer()

  const output = await preserveCharts(original, rewritten)   // charts put back
  fs.writeFileSync('out.xlsx', output)

  console.log('charts restored:', chartCount(await captureCharts(output)))  // 1
}

main()

If the edit happens somewhere else — another process, a queue, a different machine — split it into the two halves:

const { captureCharts, restoreCharts } = require('chartsheet')

const record = await captureCharts(original)   // plain data, serialisable
// ... later, elsewhere ...
const output = await restoreCharts(rewritten, record)

What it does carefully

• Matches sheets by name, not position, so charts return to the right sheet even if sheet order changed.
• Follows each chart's own relationships, so the colour and style parts Excel writes alongside a chart come across too — not just chart1.xml.
• Renumbers parts so restored charts never collide with charts added afterwards.
• Skips rather than guesses. If a sheet was renamed or removed, or the writer produced a drawing of its own, that sheet is left untouched instead of being corrupted.

Checking it worked

const { chartCount, captureCharts, validate } = require('chartsheet')

console.log(chartCount(await captureCharts(output)))   // 3
console.log(await validate(output))                    // { valid: true, errors: [] }

Does SheetJS have the same problem?

Yes, in its community build. Charts are not part of what it reads or writes, so a round trip drops them in the same way. The same capture-and-restore approach applies.

Questions