4

I am trying to render an excel file in Table format with Exceljs but I am getting this warning before opening the file:

We found a problem with some content in ’test.xlsx’. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

If I click yes it 'recovers' the file and everything is perfect, but I always get that warning before opening.

This only happens when I do more than one tab, for a single one works fine.

import Excel from 'exceljs'

const tabs = {
  'FIRST TAB': [
    { URL: 'https://google.com', FOO: 10 },
    { URL: 'https://apple.com', FOO: 12.5 }
  ],
  'SECOND TAB': [
    { URL: 'https://google.com', FOO: 10 },
    { URL: 'https://apple.com', FOO: 22.5 }
  ]
}

;(async () => {

  const workbook = new Excel.Workbook()
  let worksheet = {}

  for (const [label, tab] of Object.entries(tabs))  {

    worksheet = workbook.addWorksheet(label)
    worksheet.state = 'visible'

    const columns = Object.keys(tab[0]).map((items) => ({
      name: items,
      filterButton: true
    }))

    const rows = tab.map((entry) => Object.values(entry))

    workbook.getWorksheet(label).addTable({
      name: label,
      ref: 'A1',
      headerRow: true,
      columns,
      rows
    })
  }

  // Write to excel
  await workbook.xlsx.writeFile(`test.xlsx`)
})()
Álvaro
  • 2,255
  • 1
  • 22
  • 48

2 Answers2

2

The problem is caused by the space in the table name.

One way to fix it would be to replace the space with an underscore, that's actually what Excel does when it 'fixes' the file.

    workbook.getWorksheet(label).addTable({
      name: label.replace(' ', '_'),
      ref: 'A1',
      headerRow: true,
      columns,
      rows
    })
norie
  • 9,609
  • 2
  • 11
  • 18
1

For me, this is caused by having a cell whose content is a string with length >=32768. I have to trim the string down to 32767 bytes.

Kevin
  • 2,775
  • 4
  • 16
  • 27