More

    How to generate Excel file in Aura Component of Salesforce

    So, I had a requirement couple of weeks ago from my client in which I had to export some record data from Salesforce records to an Excel file. I started working on a lightning component hoping that there would be a straightforward way to generate Excel file in Aura component. Although, I did find a simple solution for exporting files in CSV format there wasn’t any default feature of both Salesforce and Javascript that could help me export data as Excel in lightning component. Ultimately, I did come across a proper working solution which I am going to guide to about.

    Also Read: How to use Lookup field in Web To Lead form in Salesforce

    In order to generate an Excel file in the Aura component, you have to perform the following steps.

    Make an Aura Component

    Our first step is to make an Aura component. We will call it ExportData. At the same time, also make an Apex controller ExportDataController that will be used to query required data from Salesforce records. Once the setup is done, we will head to downloading the library that will help us generate Excel file in Aura component.

    Download SheetJS

    Now, you have to download the SheetJS library and use it as a Static Resource in your component. It should be noted that we cannot use the CDN of any JS library in Salesforce as it is restricted.

    Setup the Excel Workbook

    Once, the SheetJS is downloaded and accessed by our Aura component, it is now time to use it to general the Excel file. An Excel file is essentially a workbook that contains multiple worksheets (the sheets/tabs that you see below in the file). We can create a multi-sheet Excel file in the Aura component by using SheetJS. At this point our code will look this:

    <aura:component implements="force:lightningQuickActionWithoutHeader,force:hasRecordId" controller="ExportRecordFieldsController">
           <ltng:require scripts="{!$Resource.SheetJS}" afterScriptsLoaded="{!c.doInit}"/>
            <p class="status">Please wait while the file is getting ready...</p>
    </aura:component>
    
    ({
    	doInit: function(cmp, event, helper)  {
               helper.createAndDownloadExcel(cmp);
    	}
    })
    
    ({
        createAndDownloadExcel: function(cmp)  {
            const action = cmp.get('c.getFieldsData');
            const params = {
                recordId: cmp.get('v.recordId')
            }
            action.setParams(params);
            action.setCallback(this, (res) => {
                if(res.getState() === "SUCCESS") {
                
                    var responseMap =  JSON.parse(res.getReturnValue());
                    var oppData = responseMap["Opportunity"];
                    var accData = responseMap["Account"];
        
                    // Preparing keys for data mapping
                	var oppKeys = [''Id', 'StageName', 'CustomFeld__c''];
                    var accKeys = [''Id', 'FirstName', 'CustomFeld__c''];
                
                    // Parsing and Converting the data in required format (Array of Arrays) 
                    var ws1Data = this.convertResponseToWorksheetData(oppKeys, oppData);	
                    var ws2Data = this.convertResponseToWorksheetData(accKeys, accData);;
                
                    // Creating Worksheets for Excel Workbook
                    var ws1 = XLSX.utils.aoa_to_sheet(ws1Data);
                    var ws2 = XLSX.utils.aoa_to_sheet(ws2Data);
                    
                    // Creating Excel Workbook
                    var wb = XLSX.utils.book_new();
                    XLSX.utils.book_append_sheet(wb, ws1, "Opportunity");      // (workbook, worksheet, sheetTitle)
                    XLSX.utils.book_append_sheet(wb, ws2, "Account");
                    
                    // Downloading and Closing
                    XLSX.writeFile(wb, 'Output.xls');
                    $A.get("e.force:closeQuickAction").fire();
                
            	} else {
                    toastEvent.setParams({
                		title : 'Error',
                		message:'Error generating file.',
                		duration:' 5000',
                		key: 'info_alt',
                		type: 'error',
                		mode: 'pester'
            		});
            		toastEvent.fire();
            		$A.get("e.force:closeQuickAction").fire();
                } 
            });
            $A.enqueueAction(action);  
        },
            
        // This methods extract values from data and then return a 2d array including keys and values   
        convertResponseToWorksheetData: function(keys, data) {
            var values = [];
            for (let i=0; i < keys.length; i++) {
                var key = keys[i];
                values.push(data[key]);
            }
            var wsData = [keys, values];
            return wsData;
        }
    })
    

    Now that we have set up the Javascript side to properly parse, format the response data, and convert it into Excel Workbook. It’s time to get data from Apex.

    Get required data from Apex

    It’s up to you that how you set up your business and data logic depending upon your requirements. In our case, we need records fields of an Opportunity record and the Account record associated with it. Do note that the keys that you used above should be the names of the fields that you want your query.

    public class ExportRecordFieldsController {
    
        @AuraEnabled
        public static String getFieldsData(String recordId) {
    
            Opportunity opp = [Select Id, StageName, CustomFeld__c from Opportunity where id =: recordId];
            Account acc = [Select Id, StageName, CustomFeld__c from Account where id =: opp.AccountId]; 
            
            Map&amp;lt;String, sObject&amp;gt; responseMap = new Map&amp;lt;String, sObject&amp;gt;();
            responseMap.put('Opportunity', opp);
            responseMap.put('Account', acc);
            return JSON.serialize(responseMap);
        }
    }
    

    Finally, you would have to write a test class for this controller to complete the whole package.

    We hope that this will help you export data as an Excel file in salesforce. If you have found this article useful, please share it with other Salesforce Developers.

    Talha Saqib
    Talha Saqib
    Founder of Retrology. Been actively following the gaming and media industry for the past 15 years. Also, a full-time software engineer. My day-to-day tasks include writing, editing, strategizing content, managing my team, and handling the complete back-end.
    Subscribe
    Notify of
    guest
    0 Comments
    Inline Feedbacks
    View all comments