Calling a async function with await inside worksheet.eachRow() in exceljs package

Question

const Excel = require("exceljs");
const fs = require("fs");
const createConnection = require("./db.connection");
const SEGMENT = require("./models/segment");

const fileName = "test.xlsx";

createConnection(); //Db connection

const readExcel = async () => {
  try {
    let workbook = new Excel.Workbook();

    await workbook.xlsx.readFile(fileName);

    readRows(workbook);
  } catch (error) {
    console.log("error", error);
  }
};

readExcel();

readRows = (workbook, headers) => {
  workbook.eachSheet((worksheet, sheetId) => {
    // console.log(worksheet);

    worksheet.eachRow({ includeEmpty: false }, async (row, rowNumber) => {
      console.log("Entered ROW at LOC 116", rowNumber);
      const segment = new SEGMENT({ versionName: row.getCell(1).text.trim() });
      const saveSegment = await segment.save(); //Insert into db
      console.log("Entered ROW at LOC 119 Save Segment", saveSegment); //Further code after this line
    });
  });
};

I just want to wait till document is saved in db before doing further processing inside workbook.eachRow loop. The output I am getting is

Entered ROW at LOC 116 1
Entered ROW at LOC 116 2
Entered ROW at LOC 116 3
Entered ROW at LOC 116 4
Entered ROW at LOC 116 5
Entered ROW at LOC 116 6
Entered ROW at LOC 116 7
Entered ROW at LOC 116 8
Entered ROW at LOC 116 9
Entered ROW at LOC 116 10
Entered ROW at LOC 116 11
Entered ROW at LOC 116 12
Entered ROW at LOC 116 13
Entered ROW at LOC 116 14
Entered ROW at LOC 116 15
Entered ROW at LOC 116 16
Entered ROW at LOC 116 17
Entered ROW at LOC 116 18
Entered ROW at LOC 116 19
Entered ROW at LOC 116 20
Entered ROW at LOC 116 21
Entered ROW at LOC 116 22
Entered ROW at LOC 116 23
Entered ROW at LOC 116 24
Entered ROW at LOC 116 25
Entered ROW at LOC 116 26
Entered ROW at LOC 116 27
Entered ROW at LOC 116 28
Entered ROW at LOC 116 29
Entered ROW at LOC 116 30
Entered ROW at LOC 116 31
Entered ROW at LOC 116 32
Entered ROW at LOC 116 33
Entered ROW at LOC 116 34
Entered ROW at LOC 119 Save Segment {
  isDeleted: false,
  isActive: true,
  _id: 62d714775d9c0d736f0d2f92,
  versionName: 'versionName',
  createdAt: 2022-07-19T20:30:47.583Z,
  updatedAt: 2022-07-19T20:30:47.583Z
}
Entered ROW at LOC 119 Save Segment {
  isDeleted: false,
  isActive: true,
  _id: 62d714775d9c0d736f0d2f93,
  versionName: 'versionName',
  createdAt: 2022-07-19T20:30:47.583Z,
  updatedAt: 2022-07-19T20:30:47.583Z
}

which means that each row is getting executed till before I am calling await inside loop. While what I want is entire processing for each row should be completed before moving to second row since workbook.eachrow is a synchronous function. Basicalling output should be like this.

Entered ROW at LOC 116 1
Entered ROW at LOC 119 Save Segment {
  isDeleted: false,
  isActive: true,
  _id: 62d714775d9c0d736f0d2f92,
  versionName: 'versionName',
  createdAt: 2022-07-19T20:30:47.583Z,
  updatedAt: 2022-07-19T20:30:47.583Z
}
Entered ROW at LOC 116 2
Entered ROW at LOC 119 Save Segment {
  isDeleted: false,
  isActive: true,
  _id: 62d714775d9c0d736f0d2f93,
  versionName: 'versionName',
  createdAt: 2022-07-19T20:30:47.583Z,
  updatedAt: 2022-07-19T20:30:47.583Z
}

Is this the same issue that we have while using async/await inside forEach loops?Is there a workaround for it?

I would assume yes, this is the same problem as forEach, and yes, surely there's *a* workaround, but it won't be as simple as the forEach solution — Kevin B, Jul 19 '22 at 20:53
I do not, but if you can request an array of results, and then for loop over them rather than using the builtin each, that'd be a solution. — Kevin B, Jul 19 '22 at 20:54
Cannot. Basically each row must be inserted in same sequence in db before next row is processed. This is just a small part of a complex code. If i change this I will have make a lot of changes in the entire code. — drake1994, Jul 19 '22 at 20:55
worksheet.getRows seems to be a thing that exists. If that returns an array, there's no reason you couldn't use a for loop and mimic the forEach async solution. — Kevin B, Jul 19 '22 at 20:59
Hmm yes. You are right. I forgot worksheet.getRows. Let me check. If I can get an array of arrays then yes I can work with it. Let me try. Thank you — drake1994, Jul 19 '22 at 21:01

Назар Мельник · Answer 1 · 2022-07-28T09:06:40.343

5

One way you can achieve it is to use for loop:

for (let i = 1; i <= worksheet.rowCount; i++) {
  const row = worksheet.getRow(i)
}

Another way could be wrapping eachRow call with a promise:

await new Promise((resolve) => {
  worksheet.eachRow(async (row, i) => {
    const result = await someAsyncFn()

    if (i === worksheet.rowCount) {
      resolve()
    }
  })
}

edited Jul 28 '22 at 09:06

answered Jul 28 '22 at 08:41

Назар Мельник

123
1
4

I believe this answer may not be accurate, as waiting until the end doesn't necessarily ensure that prior calls have been executed. – Ievgen Aug 23 '23 at 13:50
1

as long as you've awaited an async function that processes your row data - in the end, you should be good. But still, I personally prefer `fori` loop option here – Назар Мельник Aug 25 '23 at 07:06
I don't think it's a case for IO operations where different execution time occurred. eachRow callback is not awaited, so sequality of the executions is not established. – Ievgen Aug 26 '23 at 10:02
@Ievgen in this case please use `fori` loop option – Назар Мельник Aug 27 '23 at 18:51

score 0 · Answer 2 · answered Aug 23 '23 at 13:56

You can use await Promise.all(promises) to wait for all rows to be properly proccesed.

const someAsyncFn = async(value) => {
  console.log('executing: ' + value);
  return value;
};

var promises = [];
// same as worksheet.eachRow
[1, 2, 3, 4].forEach(p => {
  promises.push(someAsyncFn(p).then(p => {
    console.log('finished call:' + p);
  }));
});

Promise.all(promises).then(p => console.log('all rows are finished'));

Calling a async function with await inside worksheet.eachRow() in exceljs package

2 Answers2