Abhishek Prakash

Export data in Excel(xlsx) Format in Lightning Web Component (LWC)

29 March 20234 min read272 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:-

  1. Download the js file provided in the link.
  2. Put it in a zip file and provide any name
  3. Upload this zip file in static resources named xlsjs.

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.

  1. headerList - This variable is a list of list of all the columns in a worksheet. If there are multiple sheet we can supply the list of columns in this variable. Example - [['Col 1', 'Col 2', 'Col 3'], ['Col A', 'Col B', 'Col C']];
  2. apiNameList - This variable is a list of list of all the keys in js object corresponding to the column name provided in previous variable. Example - [['Name', 'Email', 'count'], ['phone', 'address', 'balance']]. Here Name will be mapped to Col 1, Email will be mapped to Col 2 and so on.
  3. filename - Name of the workbook.
  4. worksheetNameList - This variable is a list of all the worksheet names. Example ['Basic', 'Advanced']
  5. 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 :-

  1. Nikhil Karkra
  2. Salesforce Discussion Forum
Tagged with salesforce, lwc, excel, export

avatarAbhishek Prakash is a Salesforce Developer since 2017, living in Bengaluru, India. Know more about him here.