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. Let see how to Export to CSV/XLS using Lightning Web Component.
Export to CSV/XLS using Lightning Web Component
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
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.
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
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’;
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
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.
Did you receive any workaround on this?
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.
Did you receive any workaround on this?
I am facing the same issue , did you resolved the file format issue, and once i open the file all other cells in excel getting witout rows and colums . Getting plane .
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.
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?
Hello,
Can you tell me the limits on number of record that we can export?
This code is not working for csv file
What error you are getting
HTML table not support for csv file..getting all records in one row….can we use ‘ , ‘ and ‘ \n’ instead of html table tags…..Thank you for your valuable response
How to export in SpreadSheet/google sheet because it is displaying data in one row with HTML tags on google sheet.
How do I make sure csv file download is from the same domain & there is no URL change?
exportContactData(){
let doc;
// Add the data coloums
this.columnHeader.forEach(element => {
doc += element +’,’
});
doc += ‘\n’;
// Add the data rows
this.conatctData.forEach(record => {
// doc += ”;
doc += record.Id+’,’;
doc += record.FirstName+’,’;
doc += record.LastName+’,’;
doc += record.Email+’,’;
doc += ‘\n’;
});
var element = ‘data:text/csv;charset=utf-8,’ + 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.csv’;
document.body.appendChild(downloadElement);
downloadElement.click();
}
for .csv use the above code.
Csv worked!!
undefined FirstName LastName Email
003DR00000mag0vYAA Firstgugd ud last jhjkhkjhk [email protected]
003DR00000maW0bYAE adasdsad adsad [email protected]
I am trying extract Account Data in csv .All the Account fields data is populating however data for the reference field it is getting stuck. The button stops working
doc += record.Acuvue_of_Total_CL_manual__c+’,’;
doc += record.Invoice_Sales2__r[0].Prior_Year__c+’,’;
doc += record.Invoice_Sales2__r[0].Year_To_Date__c+’,’;
doc += record.Invoice_Sales2__r[0].Prior_Year_To_Date__c+’,’;
doc += record.Invoice_Sales2__r[0].Growth_YTD__c+’,’;
doc += record.Invoice_Sales2__r[0].Per_Growth__c+’,’;
doc += record.Comments__c+’,’;
Please help .
There is no as such hard limit, as code is pulling data from Salesforce object so whatever max num records it will return that many can be records will be there in your file.
Use the code I pasted in comments. It will downdload as csv.
Yes we need to use .xls which is older extension. I am looking for workaround for .xlsx. will update here.
For .xlsx. use this library
https://yourblogcoach.com/export-html-table-to-excel-using-javascript/
Thanks for this code. My requirement is that is there any way to download excel with multiple worksheet. Please help.
Hello Mahesh, Thanks for this code. My requirement is that i want to download excel with multiple worksheet in the same file. How it can be done
Hi Manish
Thanks for this code.
I am getting value “Undefined” on the excel, if there is no field value. How to handle this scenario. Kindly help.
Hi Mahesh, How can we get the look up field names instead of id?
I am getting value “Undefined” in the excel, if there is no field value. Kindly suggest how to handle this scenario.
Hi. Thank you for the code. How to handled special characters in the downloaded excel? It is not in readable format.
let doc = ”;
Just define the doc variable as an empty string. It would solve your issue.
Keep up the good work Apex hours team
I hope this helped you
I am trying extract Account Data in csv .All the Account fields data is populating however data for the reference field it is getting stuck. The button stops working
doc += record.Acuvue_of_Total_CL_manual__c+’,’;
doc += record.Invoice_Sales2__r[0].Prior_Year__c+’,’;
doc += record.Invoice_Sales2__r[0].Year_To_Date__c+’,’;
doc += record.Invoice_Sales2__r[0].Prior_Year_To_Date__c+’,’;
doc += record.Invoice_Sales2__r[0].Growth_YTD__c+’,’;
doc += record.Invoice_Sales2__r[0].Per_Growth__c+’,’;
doc += record.Comments__c+’,’;
Please help .
how to create two sheets in the excel file for contacts and account and download it. Can you help on this
Can we create Two sheets in one CSV File?
Hi Mahesh,
I used the similar kinda code but I am getting an error while opening the file.
Error: The file format and extension of ‘#########.xls’ don’t match. The file could be corrupted or unsafe. Unless you trust its source, don’t open it. Do you want to open it anyway?
Code used:
doc += ”;
var element = ‘data:application/vnd.ms-excel,’ + encodeURIComponent(doc);
let downloadElement = document.createElement(‘a’);
downloadElement.href = element;
downloadElement.target = ‘_self’;
downloadElement.download = ‘sample.xls’;
document.body.appendChild(downloadElement);
downloadElement.click();
this.isLoading = false;
Can we export Data along with Hyperlink , Is this possible
how to give error msg on lightning datatable cell
What if I do not what the xls file to have bold font? how do I remove that ?
Hi Team,
How we can export data in multiple sheets in Excel. Like 1 object data in Sheet 1, object 2 data set in Sheet 2 etc.
I only want to know how to add tabs to downloaded excel sheet.
Thank you,
Sadhu
I solved this by just replacing Undefined with empty string like so: doc=doc.replaceAll(‘undefined’,”);
Thanks for sharing