Multiple worksheets in excel using Lightning web component
Tags: lwc, salesforce, webcomponents, tutorial
Published: 2020-01-13 13:24:28

---
title: Multiple worksheets in excel using Lightning web component
published: true
description: This tutorial will help you to download Multiple worksheets in excel using Salesforce lightning web component
tags: #lwc, #salesforce, #webcomponents, #tutorial
---

The original article is written on [Salesforce Lightning Web](https://salesforcelightningweb.com/#multiple-worksheets-in-excel-using-lwc)

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](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.
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/37i1mp8vrvfmi7moucg3.png)

## 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.

```javascript
<!--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
```html
<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.

```javascript
//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
![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/2q56b3uvdl0rlpwxumkn.png)

## 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

![Alt Text](https://thepracticaldev.s3.amazonaws.com/i/jskpjfrxinerq37gfg3f.png)

* Point 1 in the above image represents the file name
* Point 2 in the above image represents the worksheets name