javascriptnode.jsexceljs

Removing and filtering out rows containing images issue in exceljs

I made a worksheet with an image in each row. When I filter or remove a row, the image of that row remains. tested on v4.4.0 / v4.3.0

I tried different way of adding images, but none of them worked , my first assumption was maybe since there is no functionally for Place in cell

I suspect that there might be an issue, or I could be approaching it differently. Any assistance would be greatly appreciated.

const workbook = new ExcelJS.Workbook()

  const worksheet = workbook.addWorksheet('test')

  worksheet.autoFilter = 'A1:D1'
  worksheet.views = [{ state: 'frozen', xSplit: 0, ySplit: 1, topLeftCell: 'A2', activeCell: 'A2' }]

  worksheet.columns = [
    { header: 'column 1', key: 'col1' },
    { header: 'column 2', key: 'col2' },
    { header: 'column 3', key: 'col3' },
    { header: 'column 4', key: 'col4' },
  ]

  const buffer = Deno.readFileSync('assets/logo.png')
  for (let i = 0; i <= 3; i++) {
    const row = worksheet.addRow([i + 1, i + 2, i + 3, ''])
    const imageId = workbook.addImage({
      buffer,
      extension: 'png',
    })
    const imageHeight = 50
    const rowHeight = Math.floor(imageHeight * 0.75)
    worksheet.getRow(row.number).height = rowHeight

    worksheet.addImage(imageId, {
      tl: { col: 3, row: row.number - 1 },
      ext: { width: 100, height: imageHeight },
      editAs: undefined, // tested also with 'oneCell'
    })
  }

  return await workbook.xlsx.writeBuffer()

this is the result

enter image description here

After removing a row

enter image description here

Solution

  • After comparing a functioning file with the one generated by exceljs, I observed some discrepancies in the XML. With this workaround, it now works for me. I will attempt to submit it as a pull request if I have the time.

    Currently the library doesn't support twoCell, However, the code below may be a hack, so if you are able to bypass it, it will function as intended.

     worksheet.addImage(imageId, {
    tl: {
      col: 1,
      row: row.number - 1,
      nativeCol: 1,
      nativeColOff: 36000,
      nativeRow: row.number - 1,
      nativeRowOff: 36000,
    },
    br: {
      col: 1,
      row: row.number - 1,
      nativeCol: 1,
      nativeColOff: 1332000,
      nativeRow: row.number - 1,
      nativeRowOff: 504000,
    },
    editAs: "twoCell",  
  });

    https://github.com/exceljs/exceljs/discussions/2648