node.jstypescriptnestjsexceljs

ExcelJs error Error: Cannot merge already merged cells


I am trying to generate an excel sheet using excelJs. Order has array order items (could be one or more). I want that some information in the order itself are not repeated while iterating over each items in the order item. However, i keep getting excel error Error: Cannot merge already merged cells.

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

I have tried using Set() to keep it unique but still got into this error


Solution

  • I was able to fix this. What i did differently was to eradicate the use of merge cells and focus on leaving them empty where i do not want it to show more than once.

    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 },
          ];
    
          // Add data rows
          orders.forEach((order) => {
            const deliveryAddress =
              order.deliveryAddress as DeliveryAddressDocument;
            const dispatch = getPartner(order.dispatch);
            const tat =
              formatMilliSecondsToTime((order as any).tat?.totalTAT || 0) || '-';
    
            order.orderItems.forEach((item, index) => {
              const partner = getPartner(item.pharmacyId);
    
              worksheet.addRow({
                DATE: index === 0 ? order.createdAt : '',
                username: index === 0 ? order.username : '',
                memberId: index === 0 ? order.memberId : '',
                userId: index === 0 ? order.userId : '',
                orderNumber: index === 0 ? order.orderNumber : '',
                customerType: index === 0 ? order.customerSource : '',
                deliveryFee: index === 0 ? order.deliveryFee : '',
                TAT: index === 0 ? tat : '',
                dispatch:
                  index === 0
                    ? order.dispatch
                      ? dispatch.partnerName
                      : 'Not available'
                    : '',
                state:
                  index === 0 ? (deliveryAddress ? deliveryAddress.state : '') : '',
                city:
                  index === 0 ? (deliveryAddress ? deliveryAddress.city : '') : '',
                addressId:
                  index === 0
                    ? deliveryAddress
                      ? deliveryAddress.landmark
                      : ''
                    : '',
                pharmacyName: partner.partnerName,
                medication: item.drugName,
                quantity: item.quantity,
                costPrice: item.costPrice,
                sellingPrice: item.unitPrice,
                profitability:
                  item.unitPrice -
                  item.costPrice +
                  (index === 0 ? order.deliveryFee : 0),
              });
            });
          });
    
          await workbook.xlsx.writeFile(filename);
        } catch (error) {
          console.log('excel error ', error);
        }
      }