Generating custom Excel files in Salesforce
Introduction
We know there are several ways of generating excel files in Salesforce. The basic one is exporting the excel file directly from the report or scheduled data export. But in this article, we will cover some of the ways which require more complex scenarios, which include using Visualforce, JavaScript in LWC, and using third-party libraries.
The Need to generate an Excel file
Salesforce is the world’s leading enterprise software company that provides CRM services to over 150,000 businesses globally. With such a huge number of businesses, comes a huge number of use cases to export excel files which may require very complex business logic. Some of the examples are mentioned below.
- Need to generate monthly reports on sales with aggregated amounts using complex logic.
- Send Product lines to customers with different pricings, calculations, discounts, additional discounts, etc.
- Monitor claims made on a specific account with all the approved, rejected, and pending requests with all the aggregated amounts.
- These are just a very few basic use cases. But there can be thousands of such requirements.
How to generate an excel file?
After doing some research, I have found a few ways to achieve this. In which I will go through these three ways to generate Excel files.
To begin with, let’s see how we can create an excel file using the Visualforce page.
Visualforce page can generate XML content for an XLS spreadsheet. It gives us the flexibility to create multiple sheets in the same excel file. On top of that, we can also customize the column's appearance by applying custom CSS to the table. Also, we can utilize the capabilities of Visualforce in combination to get the desired results. Let’s see an example of a Visualforce page rendering the data.
Visualforce
<apex:page controller="ExcelDataHandler" contentType="{!"txt/xml#"+fileName+".xls"}" cache="false" >
<apex:outputText value="{!xlsHeader}"/>
<Workbook
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40”">
<Styles>
<Style ss:ID="s1">
<Alignment/>
<Borders/>
<Font ss:Bold="1"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet 1">
<Table x:FullColumns="1" x:FullRows="1">
<Column ss:Width="150"/>
<Column ss:Width="150"/>
<Row>
<Cell ss:StyleID="s1">
<Data ss:Type="String" >Name</Data>
</Cell>
<Cell ss:StyleID="s1">
<Data ss:Type="String" >Amount</Data>
</Cell>
</Row>
<apex:repeat value="{!data}" var="wrapper">
<Row>
<Cell>
<Data ss:Type="String">{!wrapper.Name}</Data>
</Cell>
<Cell>
<Data ss:Type="String">{!wrapper.Amount}</Data>
</Cell>
</Row>
</apex:repeat>
</Table>
</Worksheet>
</Workbook>
</apex:page>Controller
public class ExcelDataHandler {
public String xlsHeader {
get;
set;
}
public String fileName {
get;
set;
}
public List < DataWrapper > data {
get;
set;
}
public ExcelDataHandler() {
try {
data = new List < DataWrapper > ();
xlsHeader = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>';
fileName = 'ExcelReport';
for (Account acc: [Select Id, Name, Amount from Account Where < your condition here > ]) {
Decimal amount = acc.Amount;
/*........
calculation logic here on amount
..........*/
data.add(new DataWrapper(acc.Name, amount));
}
} catch (exception ex) {
//Exception handling
}
}
public class DataWrapper {
public String Name {
get;
set;
}
public Decimal Amount {
get;
set;
}
public DataWrapper(String Name, Decimal Amount) {
this.Name = Name;
this.Amount = Amount;
}
}
}Here we can see that we can build any custom logic in the controller and render the same on the page.
Next, we will see how we can generate an excel file in LWC
This method uses the capabilities of JavaScript in the LWC file to generate the file. The steps to generate excel in JavaScript are as follows
Step 1: Generate the HTML table in JavaScript of LWC
Step 2: Create the anchor tag <a> element using document.createElement() function
Step 3: Append the anchor element and fire a click event using the click() function
Step 4: Just call this method from the UI
Let’s see an example method to generate an XLS file in LWC
demoExcelExport.html
<template>
<lightning-button title="Generate" label="Generate" onclick= {exportData}></lightning-button>
</template>demoExcelExport.js
import {
LightningElement
} from 'lwc';export default class DemoExcelExport extends LightningElement {columnHeader = ["First Name", "Last Name"]
data = [{
FirstName: "row1",
LastName: "test1"
},
{
FirstName: "row2",
LastName: "test2"
}
];
exportData() {
let doc = '<table>';
doc += '<style>';
doc += 'table, th, td {';
doc += ' border: 1px solid black;';
doc += '}';
doc += '</style>';
doc += '<tr>';
this.columnHeader.forEach(element => {
doc += '<th>' + element + '</th>'
});
doc += '</tr>';
this.data.forEach(record => {
doc += '<tr>';
doc += '<th>' + record.FirstName + '</th>';
doc += '<th>' + record.LastName + '</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';
downloadElement.download = 'Data.xls';
document.body.appendChild(downloadElement);
downloadElement.click();
}
}
The only drawback of using LWC is that it does not give you the flexibility to use HTML or any form of markup configurations to define the schema of the excel file. Rather we need to do some string processing to get the desired result.
At last, we can use any third-party library to generate the excel sheet.
There are several libraries available. However, I will just post a link to one of the library write-excel-file.
The steps to generate excel in JavaScript are as follows
Step 1: You can upload the library in static resource in your Salesforce Org
Step 2: Load this JS library in your LWC component on either of connectedCallback or renderedCallback methods.
Step 3: Define your schema in the JavaScript file.
Step 4: Create a button on the UI to call the method to generate the file.
Step 5: Implement methods defined in the library to generate the file.
Let’s see an example method in your LWC JS file to generate excel.
async exportData() {
let _self = this;
await writeXlsxFile(_self.objectsData, {
schema: _self.schemaObj,
fileName: 'file.xlsx'
})
}Conclusion
Each of the above methods has its pros and cons. Using the Visualforce method gives a lot of flexibility but it is not the latest technology in Salesforce, and the solution may not be future-proof.
Using LWC without using any library, you need to define your schema in JS itself which makes it more complex to implement.
And finally using the JS library has its benefits like it can save time, these are pre-tested codes and are modular. However, one should not reinvent the wheel when it can be achieved using Salesforce’s capabilities. Also, there is low support, overuse, and security issues in using the third-party libraries.
References: www.apexhours.com, learn.habilelabs.io
