How to Export Data in Excel with SheetJS in LWC

Excel exports CRM data in a familiar and flexible format that allows for data manipulation and analysis. CRM systems contain vast amounts of data about customers, leads, sales, and interactions that users need to analyze, transform, or share for decision-making.  Excel sheets offer flexibility when filtering, pivoting, and visualizing data. This helps marketing and sales teams to analyze patterns and make informed decisions. Exporting lead data can help marketers study trends in conversion, segment prospects, or plan targeted campaigns. JS libraries like SheetJS, ExcelJS, PapasParse, FileSaver, and JSXLSX can export data in Excel sheets. This post will provide an implementation of SheetJS in LWC (Lightning Web Component) to export data in Excel Sheets. Most of the steps will be common to other JS library implementations.

Sometimes, we get the error “Excel cannot open the file because the file format or extension is not valid” when creating an Excel sheet using another method or JS library. Excel sheets created using this library will not cause any such issue.

Use Case:

Businesses need Lead information in an Excel sheet. The Phone and Email columns should be highlighted in red colour.

Steps for SheetJS Integration in Lightning Web Component

1. Create Apex Class to fetch data
2. Add SheetJS Library in Salesforce
3. Create a LWC Component to Export Data
4. Test the Excel sheet generation

1. Create Apex Class to fetch Data

We need to export lead data in an Excel sheet. To do this, we will create an Apex class to get lead information from the Salesforce organization.

2. Add SheetJS Library in Salesforce

The SheetJs library can be downloaded from the Github repository. However, we need a pro version to style records in an Excel sheet. The pro version will also support implementation.

A free version with style support is also available from the repository. I have used this one to handle my use case, but you can use whatever version will handle your use case. Add this library as SheetJS in Static Resource so we can use it in Lightning Web Component.

3. Create a LWC Component to Export Data

Create a Lighting Web Component LeadDataExport, which will use the above uploaded static resource to create an Excel sheet.

This LWC will load Lead Object Data in the Data table. Once the export button is hit, an Excel sheet with the required data will be created.

Sheet JS library method usage

1. json_to_sheet – This will convert the lead data to a worksheet
2. book_new – It will create a new workbook
3. book_append_sheet – It will append the sheet to the workbook
4. writeFile – It will generate an Excel file with the workbook.

We have to refer to the Excel cell to format Excel sheet data and then apply the style. For example, if we want to make B2 cell data bold, then we will apply the code below.

ws[`B2''].s = { font: { bold: true }} 

We have used the same approach in LWC for each cell of the Email and Mobile column.

4. Test the Excel sheet generation

Once LWC is created, we can add this component to the lightning page in the required Salesforce Org.

References

• SheetJs Library
• Export Data from Lightning Web Component to Excel Sheet
• Download excel cannot open the file because the file format or file extension

Other Important Posts

• Ultimate Guide to URL Accessibility in LWC
• Dynamically Instantiate Components in LWC
• Generic Notification Component in LWC
• Reusable Custom Calendar LWC using FullCalendar Js Library
• Custom Toast with custom duration In LWC
• Dynamic Interaction Between Two LWCs
• Sending Wrapper object to Apex from LWC

Need Help?

If you need help implementing this feature, connect with me on my LinkedIn profile, Dhanik Lal Sahni.