Exceljs中eachRow循环内async/await同步执行问题及解决办法

问题描述 #

使用ExcelJS读取Excel文件时，在eachRow循环内执行异步数据库保存操作，期望逐行完成保存后再处理下一行，但实际所有行的前置日志先批量打印，之后才批量输出保存成功的日志。

原代码 #

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) => {
    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
    });
  });
};

实际输出 #

Entered ROW at LOC 116 1
Entered ROW at LOC 116 2
...（省略中间行）
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
}

期望输出 #

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
}

解答 #

问题本质 #

是的，这和forEach循环中使用async/await的问题完全一致。eachRow方法内部是同步遍历所有行，它不会等待传入的异步回调函数中的await操作完成，而是立即触发所有行的回调，导致所有前置日志先被打印，之后所有异步保存操作批量完成后才输出后续日志。

解决办法 #

放弃使用eachRow，改用for循环遍历工作表的行，通过await确保每一行的异步操作完成后再处理下一行。修改后的代码如下：

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执行完成
    await readRows(workbook);
  } catch (error) {
    console.log("error", error);
  }
};

readExcel();

// 将readRows改为异步函数
const readRows = async (workbook) => {
  // 遍历每个工作表
  for (const worksheet of workbook.worksheets) {
    // 筛选非空行，对应原代码的includeEmpty: false
    const nonEmptyRows = worksheet.filter(row => !row.isEmpty);
    // 逐行遍历并等待异步操作完成
    for (let i = 0; i < nonEmptyRows.length; i++) {
      const row = nonEmptyRows[i];
      const rowNumber = i + 1; // 行号从1开始，和原eachRow的rowNumber保持一致
      console.log("Entered ROW at LOC 116", rowNumber);
      const segment = new SEGMENT({ versionName: row.getCell(1).text.trim() });
      const saveSegment = await segment.save(); // 等待当前行保存完成
      console.log("Entered ROW at LOC 119 Save Segment", saveSegment);
    }
  }
};

关键修改点 #

1. 将readRows改为异步函数，并在readExcel中用await调用，确保整个读取保存流程可控。
2. 使用for...of遍历工作表、普通for循环遍历行，替代eachSheet和eachRow，以此控制异步流程的执行顺序。
3. 通过worksheet.filter(row => !row.isEmpty)筛选非空行，匹配原代码中eachRow的includeEmpty: false配置。

修改后即可实现逐行完成数据库保存后再进入下一行的效果，输出符合期望的顺序。

内容的提问来源于stack exchange，提问作者drake1994