ExcelJS can't write to workbook

Question

I am attempting to fetch data from database and create an excel sheet for each user's data. The sheets get created but the data doesn't get written for some reason.

I know NodeJS being async, sometimes you end up sending the file before finishing writing the data to it. However, I console.log each action and they seem to execute in the correct order.

Here is my code and its output. Why does the resulting Excel workbook have empty sheets?

const createWorkbook = () => {
  return new Promise(resolve => {
    resolve(new excel.Workbook());
  });
}

const getAssemblers = () => {
  return new Promise(async resolve => {
    resolve(await db.models.assembler.findAll())
  });
}

const createWorksheet = (workbook, name) => {
  return new Promise(resolve => {
    resolve(workbook.addWorksheet(name));
  });
};

const getAssemblerData = (id) => {
  return new Promise(async resolve => {
    const assemblyTimes = await db.models.assemblyTime.findAll({
      where: {
        assemblerID: id,
      },
      include: [
        db.models.assembler,
        db.models.item,
      ],
    });

    let assemblyData = [];

    assemblyTimes.forEach((assembly) => {
      assemblyData.push({
        assembler: assembly.assembler.name,
        item: assembly.item.name,
        start: assembly.start,
        end: assembly.end,
        duration: assembly.duration
      });
    });

    resolve(assemblyData);
  })
}

const writeDataToSheet = (worksheet, data) => {
  return new Promise(async resolve => {
    worksheet.columns = [
      { header: 'Assembler', key: 'assembler', width: 30 },
      { header: 'Item', key: 'item', width: 30 },
      { header: 'Start', key: 'start', width: 10 },
      { header: 'End', key: 'end', width: 20 },
      { header: 'Duration', key: 'duration', width: 20 },
    ];
    worksheet.addRows(data)
    resolve();
  });
};

app.get('/download', async (req, res) => {
  try {
    const workbook = await createWorkbook();
    console.log('Created workbook');
    const assemblers = await getAssemblers();
    console.log('Fetched Assemblers');
    console.log(assemblers);

    assemblers.forEach(async (assembler) => {
      const worksheet = await createWorksheet(workbook, assembler.name);
      console.log("Created assembler's worksheet");
      const assemblyData = await getAssemblerData(assembler.id);
      console.log("Fetched assembler's data");
      console.log(assemblyData)
      await writeDataToSheet(worksheet, assemblyData);
      console.log('Written!')
    });

    res.setHeader(
      'Content-Type',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    );
    res.setHeader(
      'Content-Disposition',
      'attachment; filename=assemblyData.xlsx',
    );

    return workbook.xlsx.write(res).then(() => {
      res.status(200).end();
      console.log('Sent!')
    });


  } catch (err) {
    console.error(err);
    res.sendStatus(500);
  }
});

Console output:

Created workbook
Fetched Assemblers
[
  assembler {
    dataValues: { id: 2, name: 'User 1' },
    _previousDataValues: { id: 2, name: 'User 1' },
    uniqno: 1,
    _changed: Set(0) {},
    _options: {
      isNewRecord: false,
      _schema: null,
      _schemaDelimiter: '',
      raw: true,
      attributes: [Array]
    },
    isNewRecord: false
  },
  assembler {
    dataValues: { id: 3, name: 'User 2' },
    _previousDataValues: { id: 3, name: 'User 2' },
    uniqno: 1,
    _changed: Set(0) {},
    _options: {
      isNewRecord: false,
      _schema: null,
      _schemaDelimiter: '',
      raw: true,
      attributes: [Array]
    },
    isNewRecord: false
  }
]
Created assembler's worksheet
Created assembler's worksheet
Fetched assembler's data
[
  {
    assembler: 'User 1',
    item: 'Chair',
    start: 2022-02-10T19:18:12.000Z,
    end: 2022-02-10T19:18:14.000Z,
    duration: 2
  },
  {
    assembler: 'User 1',
    item: 'Down Time',
    start: 2022-02-10T19:18:14.000Z,
    end: 2022-02-10T19:18:16.000Z,
    duration: 2
  },
  {
    assembler: 'User 1',
    item: 'Chair',
    start: 2022-02-10T19:18:16.000Z,
    end: 2022-02-10T19:18:20.000Z,
    duration: 4
  },
  {
    assembler: 'User 1',
    item: 'Down Time',
    start: 2022-02-10T19:18:20.000Z,
    end: 2022-02-10T19:18:24.000Z,
    duration: 4
  },
  {
    assembler: 'User 1',
    item: 'Chair',
    start: 2022-02-10T19:18:24.000Z,
    end: 2022-02-10T19:18:29.000Z,
    duration: 5
  }
]
Written!
Fetched assembler's data
[
  {
    assembler: 'User 2',
    item: 'Down Time',
    start: 2022-02-10T19:18:29.000Z,
    end: 2022-02-10T19:18:31.000Z,
    duration: 2
  },
  {
    assembler: 'User 2',
    item: 'Chair',
    start: 2022-02-10T19:18:31.000Z,
    end: 2022-02-10T19:18:35.000Z,
    duration: 4
  },
  {
    assembler: 'User 2',
    item: 'Down Time',
    start: 2022-02-10T19:18:36.000Z,
    end: 2022-02-10T19:18:39.000Z,
    duration: 3
  },
  {
    assembler: 'User 2',
    item: 'Chair',
    start: 2022-02-10T19:18:39.000Z,
    end: 2022-02-10T19:18:46.000Z,
    duration: 7
  },
  {
    assembler: 'User 2',
    item: 'Down Time',
    start: 2022-02-10T19:18:46.000Z,
    end: 2022-02-10T19:18:53.000Z,
    duration: 7
  },
  {
    assembler: 'User 2',
    item: 'Chair',
    start: 2022-02-10T19:18:53.000Z,
    end: 2022-02-10T19:18:58.000Z,
    duration: 5
  }
]
Written!
Sent!

Answer 1

try using for..of instead of .forEach:

for (const assembler of assemblers) {
    const worksheet = await createWorksheet(workbook, assembler.name);
    console.log("Created assembler's worksheet");
    const assemblyData = await getAssemblerData(assembler.id);
    console.log("Fetched assembler's data");
    console.log(assemblyData)
    await writeDataToSheet(worksheet, assemblyData);
    console.log('Written!')
}

See more here: Using async/await with a forEach loop