29 March 2023 • 4 min read • 272 words
To export data in Excel we will need to use an external library. It can be download at Github or Drive.
To use this library do the following:-
Now we need to create a new LWC component, we will name it excelExportUtility.
/**
* @author Abhishek Prakash
* @filename excelExportUtility.js
* @description Reusable excel worksheet generator
*/
import { LightningElement, api } from "lwc";
import { loadScript } from "lightning/platformResourceLoader";
import workbook from "@salesforce/resourceUrl/xlsjs";
export default class ExportExcelUtility extends LightningElement {
@api headerList; //List of header columns for all the sheets
@api apiNameList; //List of Api name for corresponding to header column
@api filename; //File name of the excel workbook
@api worksheetNameList; //List of all the worksheet names
@api sheetData; //List of data for all the worksheets
librariesLoaded = false;
/**
* @description load external library when component is rendered
*/
renderedCallback() {
if (this.librariesLoaded) return;
this.librariesLoaded = true;
Promise.all([loadScript(this, workbook + "/xlsx.full.min.js")])
.then(() => {
console.log("success");
})
.catch(error => {
console.log("failure");
});
}
/**
* @description this button can be called from parent component to download the file
*/
@api download() {
const XLSX = window.XLSX;
let xlsData = this.sheetData;
let xlsHeader = this.headerList;
let ws_name = this.worksheetNameList;
let createXLSLFormatObj = Array(xlsData.length).fill([]);
/* form header list */
xlsHeader.forEach((item, index) => createXLSLFormatObj[index] = [item])
/* form data key list */
xlsData.forEach((item, selectedRowIndex) => {
let xlsRowKey = this.apiNameList[selectedRowIndex];
item.forEach((value, index) => {
var innerRowData = [];
xlsRowKey.forEach(item => {
innerRowData.push(value[item]);
})
createXLSLFormatObj[selectedRowIndex].push(innerRowData);
})
});
/* creating new Excel */
var wb = XLSX.utils.book_new();
/* creating new worksheet */
var ws = Array(createXLSLFormatObj.length).fill([]);
for (let i = 0; i < ws.length; i++) {
/* converting data to excel format and puhing to worksheet */
let data = XLSX.utils.aoa_to_sheet(createXLSLFormatObj[i]);
ws[i] = [...ws[i], data];
/* Add worksheet to Excel */
XLSX.utils.book_append_sheet(wb, ws[i][0], ws_name[i]);
}
/* Write Excel and Download */
XLSX.writeFile(wb, this.filename);
//Fire file download completion Event
const customEvent = new CustomEvent('downloadcomplete', {});
this.dispatchEvent(customEvent);
}
}
This component contains 5 public variables in and a public method. Lets go through them one by one.
sheetData - This variable is a list of all the data object. Example - Suppose there are two objects
let obj1 = {
'Name' : 'Abhishek',
'Email' : 'abskpr@gmail.com',
'count' : 9
};
let obj2 = {
'phone' : '1234567890',
'address' : '123 Garden Street',
'balance' : 123
}
then we will pass a list of object as [obj1, obj2];
For calling download method we need to put exportExcelUtility component in our parent component.
<!--
/**
* @author Abhishek Prakash
* @filename parentComponent.html
* @description Calling export excel component
*/
-->
<template>
<!-- START: Spinner -->
<div if:true={showSpinner}>
<lightning-spinner class="loadingSpinner" variant="brand" size="medium"
alternative-text="Loading"></lightning-spinner>
</div>
<template for:each={items} for:item="item">
<c-export-excel-utility
header-list={columnHeader}
filename={filename}
worksheet-name-list={workSheetNameList}
sheet-data={xlsData}
api-name-list={apiNameList}
ondownloadcomplete={handleDownloadComplete}
></c-export-excel-utility>
<lightning-button
variant="brand"
label="Export"
title="Brand"
data-id={item.Id}
onclick={exportExcelData}
></lightning-button>
</template>
</template>
/**
* @author Abhishek Prakash
* @filename parentComponent.js
* @description Calling export excel component
*/
import { LightningElement, api, wire } from 'lwc';
export default class ParentComponent extends LightningElement {
workSheetNameList; // store all the sheets name of the the tables
xlsData; // store all tables data
filename;
columnHeader = [['Col 1', 'Col 2', 'Col 3']];
apiNameList = [['Name', 'Email', 'count']];
items = [
{
'Id' : '1',
'Name' : 'Abhishek',
'Email' : 'abskpr@gmail.com',
'count' : 1
},
{
Id : '2',
'Name' : 'Prakash',
'Email' : 'abskpr@yopmail.com',
'count' : 2
}
];
/**
* @description excel hotel data on button click
*/
exportExcelData(event) {
this.showSpinner = true;
let recordId = event.currentTarget.dataset.id;
let parentRecord = this.items.find(item => item.Id === recordId);
if (parentRecord) {
this.filename = parentRecord.Name + '.xlsx';
this.xlsData = [parentRecord];
this.workSheetNameList = [this.filename];
//Call download method on exportExcelUtility
setTimeout(() => {
this.template.querySelector("c-export-excel-utility").download();
}, 100);
}
}
/**
* @description handle file download completion;
*/
handleDownloadComplete() {
this.showSpinner = false;
}
}
Here timeout is needed while calling download method so that values are populated in exportExcelUtility component.
References :-
is a Salesforce Developer since 2017, living in Bengaluru, India. Know more about him here.