9

Export to CSV/XLS using Lightning Web Component

In many of the requirements, there is one most common requirement is the exporting orgs data in the form of .csv or .xls. (excel formate). Most of the Salesforce developer may have come across such requirement. Previously using VF pages this was very straight forward to fulfill.

Here we will be discussing the yet another way to export the information in tabular form either as .csv or .xls file using the Lightning Web component (LWC). This is very simple technique without using any external Java Script library we can export any kind of data. Following example illustrate the way we can easily export the information as Steps.

Step1: Create the back end Apex controller which will fetch the required data and pass that to the LWC component. Like in below sample code ExportDataController.cls.

/**
 * @description       : Query the 10 Contacts and return the results
**/
public with sharing class ExportDataController {
    
    @AuraEnabled(cacheable=true)
    public static List<Contact> getAccountDataToExport(){
        List<Contact> lstContacts = [SELECT Id, FirstName, LastName, Email 
FROM Contact LIMIT 10];
        return lstContacts;
    }
}

Step 2: Create the LWC component as below and import the Apex controller.
Step 3: Prepare the HTML table in the JavaScript as shown in below example check the exportContactData() function.
Step 4: Create the anchor tag a element using document.createElement() function.

Step 5: Append the anchor element and fire a click event using the click() function of the newly created element.

Note: the usage of

var element = 'data:application/vnd.ms-excel,' + encodeURIComponent(doc);

this is the most important here. vnd.ms-excel can be changed for csv. This line itself decides which type of file you can export data to. LWC : ExportDataAsCsvOrXls

ExportDataAsCsvOrXls.js

import { LightningElement, track, wire } from 'lwc';
import getAccountDataToExport from '@salesforce/apex/ExportDataController.getAccountDataToExport';

export default class ExportDataAsCsvOrXls extends LightningElement {

    @track conatctData = {}

    columnHeader = ['ID', 'FirstName', 'LastName', 'Email' ]

    @wire(getAccountDataToExport)
    wiredData({ error, data }) {
        if (data) {
            console.log('Data', data);
            this.conatctData = data;
        } else if (error) {
            console.error('Error:', error);
        }
    }

    exportContactData(){
        // Prepare a html table
        let doc = '<table>';
        // Add styles for the table
        doc += '<style>';
        doc += 'table, th, td {';
        doc += '    border: 1px solid black;';
        doc += '    border-collapse: collapse;';
        doc += '}';          
        doc += '</style>';
        // Add all the Table Headers
        doc += '<tr>';
        this.columnHeader.forEach(element => {            
            doc += '<th>'+ element +'</th>'           
        });
        doc += '</tr>';
        // Add the data rows
        this.conatctData.forEach(record => {
            doc += '<tr>';
            doc += '<th>'+record.Id+'</th>'; 
            doc += '<th>'+record.FirstName+'</th>'; 
            doc += '<th>'+record.LastName+'</th>';
            doc += '<th>'+record.Email+'</th>'; 
            doc += '</tr>';
        });
        doc += '</table>';
        var element = 'data:application/vnd.ms-excel,' + encodeURIComponent(doc);
        let downloadElement = document.createElement('a');
        downloadElement.href = element;
        downloadElement.target = '_self';
        // use .csv as extension on below line if you want to export data as csv
        downloadElement.download = 'Contact Data.xls';
        document.body.appendChild(downloadElement);
        downloadElement.click();
    }
}

Step 6: Add either link or button in the html filed of the LWC component like shown in the below sample. And hook up the respective defined JavaScript function to it.
ExportDataAsCsvOrXls.html

<template>
    <div class="slds-box slds-theme--default">
        Click on the link Below to Export data as csv/xls.
        <p class="slds-m-top--large">
            <!-- <a onclick={exportContactData}>Export Contact Data</a> -->
            <lightning-button variant="brand" label="Export Contact data" title="Export Contact Data" onclick={exportContactData}></lightning-button>
        </p>
    </div>
</template>

Output

Export to CSV/XLS using Lightning Web Component

Conclusion

Using LWC it becomes very easy to export the data in tabular for even you can export the large data. Stay connect we will be coming with another blog where we will be exporting the data by applying some formatting like text background, foreground colors etc.

Comments(9)

  1. Reply
    vishal says:

    Hi Mahesh,

    Thank you for the simple example however when I am using it for csv file as you have explained in two places it needs to be replaced.
    var element = ‘data:application/csv,’ + encodeURIComponent(doc);
    and in file name
    downloadElement.download = ‘Contact Data.csv’;
    but above changes are not working , could you please help me .

    Thanks in advance

    • Reply
      Mahesh says:

      Replace
      var element = ‘data:application/vnd.ms-excel,’ + encodeURIComponent(doc); to
      var element = ‘data:text/csv;charset=utf-8,’ + encodeURIComponent(doc);
      And
      downloadElement.download = ‘Contact Data.xls’; to
      downloadElement.download = ‘Contact Data.csv’;

  2. Reply
    Ajith says:

    Hi Mahesh,
    How should we change var element = ‘data:application/vnd.ms-excel,’ + encodeURIComponent(doc); to download excel file in .xlsx format instead of .xls.

    Thanks in advance

  3. Reply
    Ajith says:

    Hi Mahesh,
    How should we change
    var element = ‘data:application/vnd.ms-excel,’ + encodeURIComponent(doc);
    to download excel file in .xlsx format instead of .xls

    Thanks in advance.

    • Reply
      Ayan Sarkar says:

      Did you receive any workaround on this?

  4. Reply
    Rimali says:

    Hello Mahesh
    Code works as expected but only problem is once we click to open the downloaded file I am getting below error:excel cannot open the file because the file format or file extension is not valid. verify that the format is correct. Can you please help.

    • Reply
      Ayan Sarkar says:

      Did you receive any workaround on this?

  5. Reply
    Vitalii says:

    Hi,
    Thanks for the example ,
    and it works pretty good.
    One issue which I am having.
    If I have a lot of data it cuts my csv file.
    Is there any workarounds for this.

  6. Reply
    Aryan says:

    How do you keep the leading zeros in excel when you exported? So say a field like the one above (Id) has leading zeros. How to retain?

Post a comment