Exporting data to an Excel sheet or CSV is a common requirement in Salesforce Applications. We can export data to XLS/CSV format in Lightning Web Component using MIME type but this will not work for XLSX format. The file will be saved but it will not open. To export data in XLSX format we can use an external JS library write-excel-file or SheetJs. This post will provide step by step guide to export data from Lightning Web Component to Excel Sheet using the JS library write-excel-file.
This library also supports formatting while exporting data in Excel sheets. We can style headers or columns in Excel sheets as well.
Difference between XLS and XLSX format
XLS is an older format from Excel 97 to Excel 2003 while XLSX is the default file format for Excel 2007 and later. XLSX is designed for not only Windows but also MacOS, iOS, and Android. It contains calculation, pivot tables, graphing tools, as well as a macro programming language known as Visual Basic for apps.
Exporting to XLS/CSV Format:
Below JS code can be used to export data into CS/XLS format. Refer to our other post Extract list of all fields from the Page Layout for reference.
Exporting to XLSX Format
The above code will not work for the XLSX format. Although there will be no error while saving the file but file not be opened. We will get an unsupported file extension error. You can try with MIME type application/vnd.openxmlformats-officedocument.spreadsheetml.sheet for saving files as XSLX extension.
We will use an external JS library write-excel-file for saving data with the XSLS extension. By default, this JS library can not be used in Salesforce as it is blocked by the locker service. You can download the JS library from GitHub and upload it in the static resource with the name writeexcelfile.
Apex Code
Let us add the apex class code to retrieve data from the Account object. This is required to make a dynamic Excel sheet based on our requirements. This is a very simple apex code to retrieve data, you can create an apex class based on your requirement.
LWC Code
This LWC code is using an external JS library WriteExcelFile using Static Resource. As mentioned put static resource name as writeexcelfile. If you want to give a different name, then change in LWC code as well in the import section.
For exporting into an Excel sheet, we need the below info
- Column Header
- Columns
- Records
We can style all of them using a custom color code like it is in the demo code. Check out GitHub-ReadMe document for all supported styles and formatting.
Test Page
Based on the above code it will generate an Excel sheet like the below image. As mentioned in the code, the header has a blue background with white text color and the Column email is red color.
Reference:
Write-Excel-File Documentation
Related Posts:
Extract list of all fields from Page Layout
Reusable Custom Calendar LWC using FullCalendar Js Library
Custom Image Slider in Lightning Web Component
7 Comments
What if I wanted to download two different tables in different sheets of Excel !! please let me know
Hello Shubham,
It can be done, check help documentation for Multiple Sheets
Thank You,
Dhanik
I want table styling in the sheet as well. How can I do that?
Hello Aryan,
You can check document link in reference section. You will get all styling detail in that document.
Thank You,
Dhanik
Hi Dhanik,
I want to add some fields in header with red color with Star symbol ,Is it possible? Please suggest
Hello Veer,
You can check the documentation at https://gitlab.com/catamphetamine/write-excel-file#readme for your request. Header color can be done. About Star symbol not sure,but you can try adding with encoded format for star. If not working, please connect with thier support team.
Thank You,
Dhanik
Pingback: How to Export Data in Excel with SheetJS in LWC