The original article is written on Salesforce Lightning Web
Downloading the table data in Excel sheet is easy but downloading the multiple table data in multiple worksheets of Excel is quite challenging. To achieve this in LWC we need to follow the following steps
Download the xlsx.js
I have done some modifications in the xlsx.js to make it compatible with salesforce lightning. So, use the following link and download
Add the xlsx.js to static resources
Create a folder with name xlsx
and put the filexlsx.full.min.js in it. Zip the folder and upload it to the Static resources as shown below.
Create lightning web component - xlsxDemo
Create a LWC with name xlsxDemo and add the following code to the xlsxDemo.js file.
- In the below code we have used the two apex methodgetContactLists && getAccountLists to get the data from contact and account table respectively.
- Within the
connectedCallback
lifecycle hook we have called thegetContactLists
&& getAccountLists and using promise resolved them. - We have stored the contact list headers in
this.contactHeader
and data inthis.contactData
- Similarly, Store the account list headers in
this.accountHeader
and data inthis.accountData
- Using this.xlsFormatter method to create the array of tables data and array of header. Also, using the
this.workSheetName
List which contains the list of worksheet names.
<!--xlsxDemo.js-->
import { LightningElement, track } from "lwc";
import getContactLists from "@salesforce/apex/ContactController.getContactLists";
import getAccountLists from "@salesforce/apex/AccountController.getAccountLists";
export default class XlsxDemo extends LightningElement {
@track xlsHeader = []; // store all the headers of the the tables
@track workSheetNameList = []; // store all the sheets name of the the tables
@track xlsData = []; // store all tables data
@track filename = "nikhil_demo.xlsx"; // Name of the file
@track accountData = []; // used only for storing account table
@track contactData = []; // used only for storing contact table
connectedCallback() {
//apex call for bringing the contact data
getContactLists()
.then(result => {
console.log(result);
this.contactHeader = Object.keys(result[0]);
this.contactData = [...this.contactData, ...result];
this.xlsFormatter(result, "Contacts");
})
.catch(error => {
console.error(error);
});
//apex call for bringing the account data
getAccountLists()
.then(result => {
console.log(result);
this.accountHeader = Object.keys(result[0]);
this.accountData = [...this.accountData, ...result];
this.xlsFormatter(result, "Accounts");
})
.catch(error => {
console.error(error);
});
}
// formating the data to send as input to xlsxMain component
xlsFormatter(data, sheetName) {
let Header = Object.keys(data[0]);
this.xlsHeader.push(Header);
this.workSheetNameList.push(sheetName);
this.xlsData.push(data);
}
// calling the download function from xlsxMain.js
download() {
this.template.querySelector("c-xlsx-main").download();
}
}
Replace the following code in xlsxDemo.html file
- In
xlsxDemo.html
we are calling the xlsxMain component. In c-xlsx-main component, we are passing thexlsHeader
,filename
,workSheetNameList
andxlsData
which is the header of the excel sheet, name of the excel sheet, worksheet names and excel sheet data respectively. - Created a download button on click of which downloading of excel sheet will trigger
- Created the Account and Contact table using
for:each
loop
<template>
<div class="margin-bottom-2rem">
<lightning-card title="XLSX multi worksheet" icon-name="custom:custom14">
<div class="slds-m-around_medium">
<button onclick={download} class="btn success">Download</button>
<!--xlsxMain component calling-->
<c-xlsx-main
header-list={xlsHeader}
filename={filename}
worksheet-name-list={workSheetNameList}
sheet-data={xlsData}
></c-xlsx-main>
</div>
<div class="container">
<!--Account Table-->
<div class="child-container">
<h1>Account Table</h1>
<table>
<tr>
<template for:each={accountHeader} for:item="header">
<th key={header}>{header}</th>
</template>
</tr>
<template for:each={accountData} for:item="item">
<tr key={item.Id}>
<th>{item.Id}</th>
<th>{item.Name}</th>
<th>{item.Phone}</th>
<th>{item.NumberOfEmployees}</th>
</tr>
</template>
</table>
</div>
<!--Contact Table-->
<div class="child-container">
<h1>Contact Table</h1>
<table>
<tr>
<template for:each={contactHeader} for:item="header">
<th key={header}>{header}</th>
</template>
</tr>
<template for:each={contactData} for:item="item">
<tr key={item.Id}>
<th>{item.Name}</th>
<th>{item.Phone}</th>
<th>{item.Email}</th>
<th>{item.Title}</th>
<th>{item.Id}</th>
</tr>
</template>
</table>
</div>
</div>
</lightning-card>
</div>
</template>
Replace the following code in xlsxDemo.js-meta.xml file.
<?xml version="1.0" encoding="UTF-8"?>
<LightningComponentBundle xmlns="http://soap.sforce.com/2006/04/metadata" fqn="xlsxDemo">
<apiVersion>46.0</apiVersion>
<isExposed>true</isExposed>
<targets>
<target>lightning__AppPage</target>
<target>lightning__RecordPage</target>
<target>lightning__HomePage</target>
</targets>
</LightningComponentBundle>
Create lightning web component - xlsxMain
- In this component we will write our logic for downloading the excel with multiple worksheet.
- In this component first will include the file
xlsx.full.min.js
from the static resources *We have used theloadScript
to upload thexlsx.full.min.js
to the component using the renderedCallback lifecycle hook. - In this component, we need to replace
xlsxMain.js
code with the following code rest all file remain as it is. -
@api
download method get called once the download button clicked on the screen.
//xlsxMain.js
import { LightningElement, api } from "lwc";
import { loadScript } from "lightning/platformResourceLoader";
import workbook from "@salesforce/resourceUrl/xlsx";
export default class XlsxMain extends LightningElement {
@api headerList;
@api filename;
@api worksheetNameList;
@api sheetData;
librariesLoaded = false;
renderedCallback() {
console.log("renderedCallback xlsx");
if (this.librariesLoaded) return;
this.librariesLoaded = true;
Promise.all([loadScript(this, workbook + "/xlsx/xlsx.full.min.js")])
.then(() => {
console.log("success");
})
.catch(error => {
console.log("failure");
});
}
@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([]);
//let xlsRowsKeys = [];
/* form header list */
xlsHeader.forEach((item, index) => createXLSLFormatObj[index] = [item])
/* form data key list */
xlsData.forEach((item, selectedRowIndex)=> {
let xlsRowKey = Object.keys(item[0]);
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);
}
}
Run your code
- Deploy both the component to your org.
- Edit your page where you want to show this component and it will take you to the app builder.
- drag the
xlsxDemo
component listed under custom heading to the page and save it - Go back to the page and you will see the following output
Download the excel sheet with multiple worksheets
- Once you click on the download button, excel sheet with name
nikhil_demo.xlsx
get's download - If you open the excel sheet you will see the following output
- Point 1 in the above image represents the file name
- Point 2 in the above image represents the worksheets name
Top comments (6)
Hi Nikhil, I have a requirement where I need to export data with some conditional styling of cells on export. I tried the above tutorial and was able to create multiple sheets. Could you guide on how to go about with conditional formatting such as colors, fonts, cell merge etc either using the same library or any other library you know would be good to be integrated with LWC. Any kind of help would be appreciated.
Hi Ankit,
did you find any solution related to conditional formatting.
Hi Anish, did you find the solution for merge cell?
Hi Anish,
Wanted to discuss with you on this query. How do I connect with you?
Best,
Nikita
Hello NIkhil
Thanks for sharing Multi Excel download excel file.
I have a question, Is there any possibility to display image on Top up of the Table.
For branding purpose we can show the logo on excel Sheet.
can you please share the information, how we can logo.
Hi Nikhil. I have a requirement where I need to export data with some conditional styling of cells on export. Any kind of help would be appreciated.