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