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 there would be a straightforward way to generate an 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 the lightning component. Ultimately, I did come across a proper working solution which I am going to guide to about.
In order to generate an Excel file in the Aura component, you must 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:
ExportData.cmp
<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>
ExportDataController.js
({
doInit: function(cmp, event, helper) {
helper.createAndDownloadExcel(cmp);
}
})
ExportDataHelper.js
({
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 &lt; 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 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 for your query.
ExportDataController.apxc
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<String, sObject> responseMap = new Map<String, sObject>();
responseMap.put('Opportunity', opp);
responseMap.put('Account', acc);
return JSON.serialize(responseMap);
}
}
Finally, you would have to write a test class for this controller so that it. can be deployed to other orgs.
I hope 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.
Also Read
- How to find Salesforce org type via Apex code?
- The correct format of DateTime field in Web-To-Lead form in Salesforce
