Building a Lightning Web Component for Multiple Excel Sheets Download in Salesforce

Posted bysalesforceforfresherPosted inLightning Web Component, LWC, Salesforce, Top Post

Salesforce Lightning Web Components (LWC) empower developers to create dynamic and responsive user interfaces within the Salesforce platform. In this blog post, we’ll explore the development of a custom LWC designed to enhance the user experience by facilitating the download of data in multiple Excel sheets. The component utilizes the powerful exceljs library to dynamically create Excel sheets and provide users with a consolidated, downloadable file.

Understanding the Component Structure

HTML Structure

The HTML template outlines the component’s layout, consisting of a Lightning card with a title and a download button. The button triggers the exportExcelData method, initiating the Excel sheet creation and download process.

<template>
<lightning-card>
<div class="slds-grid slds-wrap slds-m-top_xx-large slds-m-left_large">
<div class="slds-col slds-large-size_3-of-12 slds-medium-size_12-of-12 slds-size_12-of-12">
<span><b>Download Data in Multiple Excel Sheet</b></span>
</div>
<div class="slds-col slds-large-size_6-of-12 slds-medium-size_12-of-12 slds-size_12-of-12">
<button class="slds-button slds-button_neutral" onclick={exportExcelData} style="background-color: #102a89;">
<span class="slds-truncate" style="color: #ffffff;">Download</span>&nbsp;&nbsp;
<lightning-icon icon-name="action:download" size="x-small" style="background-color: inherit;"></lightning-icon>
</button>
</div>
</div>
</lightning-card>
</template>
view raw MultipleSheet.html hosted with ❤ by GitHub
JavaScript (JS) Structure

The JavaScript file orchestrates the component’s functionality, including the loading of external libraries and the logic for creating and downloading multiple Excel sheets.

import { LightningElement } from 'lwc';
import { loadScript } from 'lightning/platformResourceLoader';
import workbook from '@salesforce/resourceUrl/JSExcel';
import { ShowToastEvent } from 'lightning/platformShowToastEvent';
export default class FullProfileDetailTab extends LightningElement {
connectedCallback() {
// Load the exceljs library as a static resource
Promise.all([loadScript(this, workbook + "/exceljs/dist/exceljs.js")])
.then(() => {
console.log('Library loaded successfully');
}).catch(error => {
console.error('Error loading library: ' + JSON.stringify(error));
});
}
// ... (Other helper methods, if any)
exportExcelData() {
// Create a new Excel workbook
const workbook = new ExcelJS.Workbook();
// ... (Sheet creation logic for 'Index', 'Sheet 1', 'Sheet 2', 'Sheet 3')
// Write the workbook to a buffer and initiate download
workbook.xlsx.writeBuffer().then(buffer => {
var link = document.createElement("a");
link.href = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,' + buffer.toString('base64');
link.download = 'FullProfileDetails.xls';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
});
// Show success message
const evt = new ShowToastEvent({
title: 'Download Successful',
message: 'Multiple Sheet Data downloaded successfully',
variant: 'success',
mode: 'dismissable'
});
this.dispatchEvent(evt);
}
}
view raw multipleSheetDemo.js hosted with ❤ by GitHub

Understanding the Excel Export Logic

Importing Necessary Libraries and Resources

The ‘import‘ statements bring in the essential libraries and resources required for the component.

  • import { LightningElement } from 'lwc';: Imports the LightningElement base class from the LWC framework.
  • import { loadScript } from 'lightning/platformResourceLoader';: Enables loading of external scripts using the Lightning platform’s utility function.
  • import workbook from '@salesforce/resourceUrl/JSExcel';: Imports the static resource named JSExcel, which is assumed to contain the exceljs library.
  • import { ShowToastEvent } from 'lightning/platformShowToastEvent';: Imports the class responsible for triggering toast messages to provide user feedback.
Loading External Library

The ‘connectedCallback‘ lifecycle hook loads the ‘exceljs' library when the component is connected to the DOM.

Download exceljs : https://github.com/exceljs/exceljs

connectedCallback() {
Promise.all([loadScript(this, workbook + "/exceljs/dist/exceljs.js")])
.then(() => {
console.log('Library loaded successfully');
}).catch(error => {
console.error('Error loading library: ' + JSON.stringify(error));
});
}
view raw connectedCallbackMultipleSheet hosted with ❤ by GitHub

Creating Excel Sheets and Data

The ‘exportExcelData‘ method orchestrates the creation of Excel sheets and initiates the download process.

exportExcelData() {
// Create a new Excel workbook
const workbook = new ExcelJS.Workbook();
// ... (Sheet creation logic for 'Index', 'Sheet 1', 'Sheet 2', 'Sheet 3')
// Write the workbook to a buffer and initiate download
workbook.xlsx.writeBuffer().then(buffer => {
var link = document.createElement("a");
link.href = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,' + buffer.toString('base64');
link.download = 'FullProfileDetails.xls';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
});
// Show success message
const evt = new ShowToastEvent({
title: 'Download Successful',
message: 'Multiple Sheet Data downloaded successfully',
variant: 'success',
mode: 'dismissable'
});
this.dispatchEvent(evt);
}
view raw exportExcelDataMultipleSheet hosted with ❤ by GitHub


How to Customize for Your Use Case

Adjusting Sheet Structure:
Modify the sheet creation logic in the ‘exportExcelData’ method to match the structure and content you need for each sheet.

Loading Different Libraries or Resources:
If you need to use a different library or resource, update the import statements and adjust the loading logic in the ‘connectedCallback’ method.

Customizing the Download Filename:
Change the ‘link.download’ attribute to set a custom filename for the downloaded Excel file.

Enhancing Error Handling:
Extend the error handling in the connectedCallback method to provide more informative messages if the library fails to load.

Complete Code

Here’s the complete Apex code for your reference:

HTML :

<template>
<lightning-card>
<div class="slds-grid slds-wrap slds-m-top_xx-large slds-m-left_large">
<div class="slds-col slds-large-size_3-of-12 slds-medium-size_12-of-12 slds-size_12-of-12">
<span><b>Download Data in Multiple Excel Sheet</b></span>
</div>
<div class="slds-col slds-large-size_6-of-12 slds-medium-size_12-of-12 slds-size_12-of-12">
<button class="slds-button slds-button_neutral" onclick={exportExcelData} style="background-color: #102a89;">
<span class="slds-truncate" style="color: #ffffff;">Download</span>&nbsp;&nbsp;
<lightning-icon icon-name="action:download" size="x-small" style="background-color: inherit;"></lightning-icon>
</button>
</div>
</div>
</lightning-card>
</template>
view raw multipleSheetHTML hosted with ❤ by GitHub

JS:

import { LightningElement } from 'lwc';
import { loadScript } from 'lightning/platformResourceLoader';
import workbook from '@salesforce/resourceUrl/JSExcel'; // Assuming you have the xlsx static resource
import { ShowToastEvent } from 'lightning/platformShowToastEvent';
export default class FullProfileDetailTab extends LightningElement {
connectedCallback() {
Promise.all([loadScript(this, workbook + "/exceljs/dist/exceljs.js")])
.then(() => {
console.log('result loaded ->> ', this);
}).catch(error => {
console.log('apex eeror: ' + JSON.stringify(error));
});
}
// Error Toast Msg
toastMsg(title, message, variant) {
const evt = new ShowToastEvent({
title: title,
message: message,
variant: variant,
mode: 'sticky'
});
this.dispatchEvent(evt);
}
exportExcelData() {
const workbook = new ExcelJS.Workbook();
const headerrows = [
['Multiple Sheet'],
['']
];
const Tab = [
['Index'],
['Sheet 1'],
['Sheet 2'],
['Sheet 3']
];
for (let i = 0; i < Tab.length; i++) {
const worksheet = workbook.addWorksheet(`${Tab[i]}`);
const profileOverviewHeader = {
font: { bold: true, color: { argb: 'FFFFFF' } },
alignment: { wrapText: true },
border: {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' }
}
};
const profileOverviewHeader1 = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: '1a4876' },
};
const ProfileOverViewData = {
font: { bold: true, color: { argb: '000000' } },
alignment: { wrapText: true, horizontal: 'left' },
border: {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' }
}
};
const ProfileOverViewData1 = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: 'c6c6c6' },
};
const headerCellStyle = {
font: { bold: true, color: { argb: 'FFFFFF' } },
alignment: {
vertical: 'middle',
horizontal: 'center',
wrapText: true
},
border: {
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' }
},
fill: {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: '1a4876' }
}
};
if (Tab[i][0] === 'Index') {
worksheet.getColumn('A').width = 50;
worksheet.getColumn('B').width = 50;
worksheet.getColumn('C').width = 50;
worksheet.getColumn('D').width = 50;
headerrows.forEach(rowData => {
worksheet.addRow(rowData);
});
worksheet.mergeCells('A1:B2');
const mergedCell = worksheet.getCell('A1');
mergedCell.style = headerCellStyle;
worksheet.getCell('A5').style = profileOverviewHeader;
worksheet.getCell('A5').fill = profileOverviewHeader1;
worksheet.getCell('A6').style = profileOverviewHeader;
worksheet.getCell('A6').fill = profileOverviewHeader1;
worksheet.getCell('A7').style = profileOverviewHeader;
worksheet.getCell('A7').fill = profileOverviewHeader1;
worksheet.getCell('A4').style = profileOverviewHeader;
worksheet.getCell('A4').fill = profileOverviewHeader1;
worksheet.getCell('B4').style = profileOverviewHeader;
worksheet.getCell('B4').fill = profileOverviewHeader1;
worksheet.getCell('B5').style = ProfileOverViewData;
worksheet.getCell('B5').fill = ProfileOverViewData1;
worksheet.getCell('B6').style = ProfileOverViewData;
worksheet.getCell('B6').fill = ProfileOverViewData1;
worksheet.getCell('B7').style = ProfileOverViewData;
worksheet.getCell('B7').fill = ProfileOverViewData1;
worksheet.getCell('A4').value = 'Click on it and go to that particular sheet';
worksheet.getCell('B4').value = 'Description';
worksheet.getCell('B5').value = '<-- Click on Sheet 1';
worksheet.getCell('B6').value = '<-- Click on Sheet 2';
worksheet.getCell('B7').value = '<-- Click on Sheet 3';
for (let rowIndex = 5; rowIndex <= 7; rowIndex++) {
const sheetName = Tab[rowIndex - 4][0]; // Adjust the index to start from 0
const cell = worksheet.getCell(`A${rowIndex}`);
const hyperlinkFormula = `HYPERLINK("#'${sheetName}'!A1", "${sheetName}")`;
// Add the hyperlink to the cell
cell.style = { ...cell.style, font: { color: { argb: 'FFFFFF' } } }; // Optional: Change the font color to blue
// Add the hyperlink formula to the cell
cell.value = {
formula: hyperlinkFormula,
result: sheetName
};
}
}
if (Tab[i][0] === 'Sheet 1') {
worksheet.getColumn('A').width = 50;
worksheet.getColumn('B').width = 50;
worksheet.getColumn('C').width = 50;
headerrows.forEach(rowData => {
worksheet.addRow(rowData);
});
worksheet.mergeCells('A1:C2');
const mergedCell = worksheet.getCell('A1');
mergedCell.style = headerCellStyle;
worksheet.getCell('A4').style = profileOverviewHeader;
worksheet.getCell('A4').fill = profileOverviewHeader1;
worksheet.getCell('B4').style = profileOverviewHeader;
worksheet.getCell('B4').fill = profileOverviewHeader1;
worksheet.getCell('C4').style = profileOverviewHeader;
worksheet.getCell('C4').fill = profileOverviewHeader1;
worksheet.getCell('A1').value = 'Sheet 1';
worksheet.getCell('A4').value = 'Name';
worksheet.getCell('B4').value = 'Phone';
worksheet.getCell('C4').value = 'Email';
worksheet.getCell('A5').value = 'Yash';
worksheet.getCell('A6').value = 'Shubham';
worksheet.getCell('A7').value = 'Rakesh';
worksheet.getCell('A8').value = 'Kunj';
worksheet.getCell('A9').value = 'Sumit';
worksheet.getCell('B5').value = '123456789';
worksheet.getCell('B6').value = '876541234';
worksheet.getCell('B7').value = '87238234';
worksheet.getCell('B8').value = '876523478';
worksheet.getCell('B9').value = '123493456';
worksheet.getCell('C5').value = 'yash@gmail.com';
worksheet.getCell('C6').value = 'shubham@gmail.com';
worksheet.getCell('C7').value = 'rakesh@gmail.com';
worksheet.getCell('C8').value = 'kunj@gmail.com';
worksheet.getCell('C9').value = 'sumit@gmail.com';
}
if (Tab[i][0] === 'Sheet 2') {
worksheet.getColumn('A').width = 50;
worksheet.getColumn('B').width = 50;
worksheet.getColumn('C').width = 50;
headerrows.forEach(rowData => {
worksheet.addRow(rowData);
});
worksheet.mergeCells('A1:B2');
const mergedCell = worksheet.getCell('A1');
mergedCell.style = headerCellStyle;
worksheet.getCell('A4').style = profileOverviewHeader;
worksheet.getCell('A4').fill = profileOverviewHeader1;
worksheet.getCell('B4').style = profileOverviewHeader;
worksheet.getCell('B4').fill = profileOverviewHeader1;
worksheet.getCell('A1').value = 'Sheet 2';
worksheet.getCell('A4').value = 'Name';
worksheet.getCell('B4').value = 'City';
worksheet.getCell('A5').value = 'Yash';
worksheet.getCell('A6').value = 'Shubham';
worksheet.getCell('A7').value = 'Rakesh';
worksheet.getCell('A8').value = 'Kunj';
worksheet.getCell('A9').value = 'Sumit';
worksheet.getCell('B5').value = 'Mumbai';
worksheet.getCell('B6').value = 'Delhi';
worksheet.getCell('B7').value = 'Delhi';
worksheet.getCell('B8').value = 'Mumbai';
worksheet.getCell('B9').value = 'Delhi';
}
if (Tab[i][0] === 'Sheet 3') {
worksheet.getColumn('A').width = 20;
worksheet.getColumn('B').width = 80;
worksheet.getColumn('C').width = 50;
headerrows.forEach(rowData => {
worksheet.addRow(rowData);
});
worksheet.mergeCells('A1:B2');
const mergedCell = worksheet.getCell('A1');
mergedCell.style = headerCellStyle;
worksheet.getCell('A4').style = profileOverviewHeader;
worksheet.getCell('A4').fill = profileOverviewHeader1;
worksheet.getCell('B4').style = profileOverviewHeader;
worksheet.getCell('B4').fill = profileOverviewHeader1;
worksheet.getCell('A1').value = 'Sheet 3';
worksheet.getCell('A4').value = 'Name';
worksheet.getCell('B4').value = 'Address';
worksheet.getCell('A5').value = 'Yash';
worksheet.getCell('A6').value = 'Shubham';
worksheet.getCell('A7').value = 'Rakesh';
worksheet.getCell('A8').value = 'Kunj';
worksheet.getCell('A9').value = 'Sumit';
worksheet.getCell('B5').value = '14/15/16 A, Pravasi Indl Estate, Vishweshwar Road, Nr Aarey Milk Colony, Goregaon (e)';
worksheet.getCell('B6').value = '301, Plot No 32, Prashant Bldg, Swastik Soc,ns Rd No 2, Opp Dinbandhu Collection ,juhu, Vile Parle ';
worksheet.getCell('B7').value = '1997, Katra Lachhu Singh, Sona Bazar, Chandni Chowk';
worksheet.getCell('B8').value = 'Sunderwade, Parsi Panchayat Rd, Nr.sona Udyog,h.b.bhatt Compound, Andheri (west)';
worksheet.getCell('B9').value = 'C 44, Arya Samaj Road, Uttam Nagar';
}
}
workbook.xlsx.writeBuffer().then(buffer => {
var link = document.createElement("a");
link.href = 'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,' + buffer.toString('base64');
link.download = 'FullProfileDetails.xls';
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
});
this.progress = 100; // make progress bar complete
this.processStatus = 'Completed'; // to make progress status completed
//For test message
const evt = new ShowToastEvent({
title: 'Download Successful',
message: 'Multiple Sheet Data downloaded successfully',
variant: 'success',
mode: 'dismissable'
});
this.dispatchEvent(evt);
}
}
view raw multiplesheetjs hosted with ❤ by GitHub

On Click of Download Button :

And our excel sheet :

By understanding these components of the code, users can adapt it to their specific use cases and create Lightning Web Components that export data to multiple sheets in an Excel file within the Salesforce environment. This comprehensive LWC provides a foundation for building robust and customized solutions for data export functionalities. Feel free to leverage and enhance this component to meet your specific requirements and enhance your Salesforce applications. Happy coding!

Leave a comment