Multiple worksheets in excel using Lightning web component

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

https://gist.githubusercontent.com/karkranikhil/5d9db87139cac952c6b37a6828d855bc/raw/35b846a516456614830e284f05523f0696bc6845/xlsx.full.min.js

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 the getContactLists && getAccountLists and using promise resolved them.
We have stored the contact list headers in this.contactHeader and data in this.contactData
Similarly, Store the account list headers in this.accountHeader and data in this.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 the xlsHeader, filename, workSheetNameList and xlsData 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 the loadScript to upload the xlsx.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

Blog