ExcelJS报错：Cannot merge already merged cells 问题求助

我使用ExcelJS生成Excel表格，每个Order包含一个或多个orderItems数组元素。为了让订单自身的信息不重复显示，我采用单元格合并的方式，但持续报错：Error: Cannot merge already merged cells。我尝试过用Set()去重订单，但问题依旧，相关代码如下：

async createExcelReport(orders: OrderDocument[], filename: string) {
    try {
      const workbook = new ExcelJS.Workbook();
      const worksheet = workbook.addWorksheet('Report');
      const partnersResponse = await lastValueFrom(
        this.partnerClient.send({ cmd: 'GET_ALL_PARTNERS' }, {}),
      );
  
      if (!partnersResponse) {
        throw new Error('Failed to get partners');
      }
      const partners = partnersResponse.data;
  
      const getPartner = (partnerId: string) =>
        partners.find((partner) => partner.partnerId === partnerId) || {
          partnerName: 'Not available',
        };
  
      // Define headers
      worksheet.columns = [
        { header: 'DATE', key: 'DATE', width: 15 },
        { header: 'Customer NAME', key: 'username', width: 25 },
        { header: 'Member ID', key: 'memberId', width: 25 },
        { header: 'Pharmarun User ID', key: 'userId', width: 25 },
        { header: 'Order Number', key: 'orderNumber', width: 20 },
        { header: 'Pharmacy Name', key: 'pharmacyName', width: 25 },
        { header: 'Customer Type', key: 'customerType', width: 20 },
        { header: 'Partner Name', key: 'businessAlias', width: 25 },
        { header: 'Medication', key: 'medication', width: 25 },
        { header: 'Quantity', key: 'quantity', width: 15 },
        { header: 'Cost Price', key: 'costPrice', width: 15 },
        { header: 'Selling Price', key: 'sellingPrice', width: 15 },
        { header: 'Delivery Fee', key: 'deliveryFee', width: 15 },
        { header: 'TAT', key: 'TAT', width: 15 },
        { header: 'Profitability', key: 'profitability', width: 15 },
        { header: 'Dispatched Type', key: 'dispatch', width: 20 },
        { header: 'Dispatched Approved by', key: 'dispatchApprovedBy', width: 25 },
        { header: 'State', key: 'state', width: 20 },
        { header: 'City', key: 'city', width: 20 },
        { header: 'Landmark', key: 'addressId', width: 25 },
      ];
  
      const mergeAndSetCellValue = (
        startRow: number,
        endRow: number,
        column: string,
        value: any,
      ) => {
        if (startRow !== endRow) {
          worksheet.mergeCells(`${column}${startRow}:${column}${endRow}`);
        }
        worksheet.getCell(`${column}${startRow}`).value = value;
      };
  
      // Add data rows
      let currentRow = 2; // Start from the second row (first row is for headers)
      orders.forEach((order) => {
        const startRow = currentRow;
        order.orderItems.forEach((item, index) => {
          const partner = getPartner(item.pharmacyId);
          worksheet.addRow({
            pharmacyName: partner.partnerName,
            medication: item.drugName,
            quantity: item.quantity,
            costPrice: item.costPrice,
            sellingPrice: item.unitPrice,
            profitability: item.unitPrice - item.costPrice + order.deliveryFee,
          });
          currentRow++;
        });
  
        const endRow = currentRow - 1;
        const deliveryAddress = order.deliveryAddress as DeliveryAddressDocument;
        const dispatch = getPartner(order.dispatch);
        const tat = formatMilliSecondsToTime((order as any).tat?.totalTAT || 0) || '-';
  
        mergeAndSetCellValue(startRow, endRow, 'A', order.createdAt);
        mergeAndSetCellValue(startRow, endRow, 'B', order.username);
        mergeAndSetCellValue(startRow, endRow, 'C', order.memberId);
        mergeAndSetCellValue(startRow, endRow, 'D', order.userId);
        mergeAndSetCellValue(startRow, endRow, 'E', order.orderNumber);
        mergeAndSetCellValue(startRow, endRow, 'G', order.customerSource);
        mergeAndSetCellValue(startRow, endRow, 'M', order.deliveryFee);
        mergeAndSetCellValue(startRow, endRow, 'N', tat);
        mergeAndSetCellValue(startRow, endRow, 'P', order.dispatch ? dispatch.partnerName : 'Not available');
        mergeAndSetCellValue(startRow, endRow, 'R', deliveryAddress ? deliveryAddress.state : null);
        mergeAndSetCellValue(startRow, endRow, 'S', deliveryAddress ? deliveryAddress.city : null);
        mergeAndSetCellValue(startRow, endRow, 'T', deliveryAddress ? deliveryAddress.landmark : null);
      });
  
      await workbook.xlsx.writeFile(filename);
    } catch (error) {
      console.log('excel error ', error);
    }
  }

原因分析 #

1. 订单重复处理：Set()无法直接对对象数组去重（对象是引用类型），如果orders数组中有重复订单，会导致同一行范围被重复合并，触发报错。
2. addRow隐式创建单元格：worksheet.addRow()会自动创建该行所有列的单元格，可能导致后续合并操作时，某些单元格被意外标记为已合并（或与之前的合并范围冲突）。

解决方案 #

方案1：正确对订单去重 #

用Map根据订单唯一标识（如orderNumber）去重，避免重复处理同一行：

// 处理订单前添加去重逻辑
const uniqueOrders = Array.from(
  new Map(orders.map(order => [order.orderNumber, order])).values()
);

之后遍历uniqueOrders而非原始orders数组。

方案2：替换addRow为手动创建行 #

避免addRow隐式创建所有单元格，改为手动设置需要的单元格值，精准控制单元格创建：

// 替换原orderItems.forEach循环
order.orderItems.forEach((item, index) => {
  const row = worksheet.getRow(currentRow);
  const partner = getPartner(item.pharmacyId);
  // 手动设置需要的单元格值
  row.getCell('F').value = partner.partnerName; // Pharmacy Name
  row.getCell('I').value = item.drugName; // Medication
  row.getCell('J').value = item.quantity; // Quantity
  row.getCell('K').value = item.costPrice; // Cost Price
  row.getCell('L').value = item.unitPrice; // Selling Price
  row.getCell('O').value = item.unitPrice - item.costPrice + order.deliveryFee; // Profitability
  row.commit(); // 提交行变更
  currentRow++;
});

完整修复后的代码 #

async createExcelReport(orders: OrderDocument[], filename: string) {
  try {
    const workbook = new ExcelJS.Workbook();
    const worksheet = workbook.addWorksheet('Report');
    const partnersResponse = await lastValueFrom(
      this.partnerClient.send({ cmd: 'GET_ALL_PARTNERS' }, {}),
    );

    if (!partnersResponse) {
      throw new Error('Failed to get partners');
    }
    const partners = partnersResponse.data;

    const getPartner = (partnerId: string) =>
      partners.find((partner) => partner.partnerId === partnerId) || {
        partnerName: 'Not available',
      };

    // Define headers
    worksheet.columns = [
      { header: 'DATE', key: 'DATE', width: 15 },
      { header: 'Customer NAME', key: 'username', width: 25 },
      { header: 'Member ID', key: 'memberId', width: 25 },
      { header: 'Pharmarun User ID', key: 'userId', width: 25 },
      { header: 'Order Number', key: 'orderNumber', width: 20 },
      { header: 'Pharmacy Name', key: 'pharmacyName', width: 25 },
      { header: 'Customer Type', key: 'customerType', width: 20 },
      { header: 'Partner Name', key: 'businessAlias', width: 25 },
      { header: 'Medication', key: 'medication', width: 25 },
      { header: 'Quantity', key: 'quantity', width: 15 },
      { header: 'Cost Price', key: 'costPrice', width: 15 },
      { header: 'Selling Price', key: 'sellingPrice', width: 15 },
      { header: 'Delivery Fee', key: 'deliveryFee', width: 15 },
      { header: 'TAT', key: 'TAT', width: 15 },
      { header: 'Profitability', key: 'profitability', width: 15 },
      { header: 'Dispatched Type', key: 'dispatch', width: 20 },
      { header: 'Dispatched Approved by', key: 'dispatchApprovedBy', width: 25 },
      { header: 'State', key: 'state', width: 20 },
      { header: 'City', key: 'city', width: 20 },
      { header: 'Landmark', key: 'addressId', width: 25 },
    ];

    const mergeAndSetCellValue = (
      startRow: number,
      endRow: number,
      column: string,
      value: any,
    ) => {
      if (startRow !== endRow) {
        // 检查单元格是否已合并，避免重复合并报错
        const startCell = worksheet.getCell(`${column}${startRow}`);
        if (!startCell.isMerged) {
          worksheet.mergeCells(`${column}${startRow}:${column}${endRow}`);
        }
      }
      worksheet.getCell(`${column}${startRow}`).value = value;
    };

    // 订单去重
    const uniqueOrders = Array.from(
      new Map(orders.map(order => [order.orderNumber, order])).values()
    );

    // Add data rows
    let currentRow = 2; // Start from the second row (first row is for headers)
    uniqueOrders.forEach((order) => {
      const startRow = currentRow;
      // 手动创建行，替换addRow
      order.orderItems.forEach((item, index) => {
        const row = worksheet.getRow(currentRow);
        const partner = getPartner(item.pharmacyId);
        row.getCell('F').value = partner.partnerName;
        row.getCell('I').value = item.drugName;
        row.getCell('J').value = item.quantity;
        row.getCell('K').value = item.costPrice;
        row.getCell('L').value = item.unitPrice;
        row.getCell('O').value = item.unitPrice - item.costPrice + order.deliveryFee;
        row.commit();
        currentRow++;
      });

      const endRow = currentRow - 1;
      const deliveryAddress = order.deliveryAddress as DeliveryAddressDocument;
      const dispatch = getPartner(order.dispatch);
      const tat = formatMilliSecondsToTime((order as any).tat?.totalTAT || 0) || '-';

      mergeAndSetCellValue(startRow, endRow, 'A', order.createdAt);
      mergeAndSetCellValue(startRow, endRow, 'B', order.username);
      mergeAndSetCellValue(startRow, endRow, 'C', order.memberId);
      mergeAndSetCellValue(startRow, endRow, 'D', order.userId);
      mergeAndSetCellValue(startRow, endRow, 'E', order.orderNumber);
      mergeAndSetCellValue(startRow, endRow, 'G', order.customerSource);
      mergeAndSetCellValue(startRow, endRow, 'M', order.deliveryFee);
      mergeAndSetCellValue(startRow, endRow, 'N', tat);
      mergeAndSetCellValue(startRow, endRow, 'P', order.dispatch ? dispatch.partnerName : 'Not available');
      mergeAndSetCellValue(startRow, endRow, 'R', deliveryAddress ? deliveryAddress.state : null);
      mergeAndSetCellValue(startRow, endRow, 'S', deliveryAddress ? deliveryAddress.city : null);
      mergeAndSetCellValue(startRow, endRow, 'T', deliveryAddress ? deliveryAddress.landmark : null);
    });

    await workbook.xlsx.writeFile(filename);
  } catch (error) {
    console.log('excel error ', error);
  }
}

内容的提问来源于stack exchange，提问作者Akinniyi Akinbode Bolade