how to generate an excel file in salesforce?

Question

I have a requirement to download 4-5 excel file on click of a button from the record detail page, each having different data, along with an image embedded inside the file. Also to create form like structure in the excel file. similar to below -

we are free to use any method - it could either be VisualForce, Aura, LWC. But I would prefer to use LWC. Data will be fetched from different objects.

After searching lot on the internet, I have found 2 ways -

1. https://www.apexhours.com/export-to-csv-xls-using-lightning-web-component/
2. https://learn.habilelabs.io/salesforce-export-to-excel-with-lightning-web-component-875b3f4b7e53

1st Method

with the first method, I can create an excel sheet, in the desired structure, as data is prepared in HTML format and can be forced to download as follows -

    var element = 'data:application/vnd.ms-excel,'+encodeURIComponent(doc);
    let downloadElement = document.createElement('a');
    downloadElement.href = element;
    downloadElement.target = '_self';
    downloadElement.download = 'Contact Data.xls';
    document.body.appendChild(downloadElement);
    downloadElement.click();

where doc is the variable in which all html data is concatenated. and we can repeat the above line of code with different html variable to download mutliple files.

But the problem is that, it doesn't support image embedding. I had tried to use it as -

    doc += '<tr>';
    doc += '<td><img src="https://cdn1.vectorstock.com/i/1000x1000/45/00/car-blueprint-vector-7784500.jpg"></td>';
    doc += '</tr>';
    doc += '</table>';

So this is just a workaround. Excel sheet shows the image when the system is connected to internet. not when offline.

Also one major issue with this method is that - it throws warning everytime file is opened.

Also there is a security threat associated with the xls format (which is wrapped around html) ----- https://social.technet.microsoft.com/Forums/office/en-US/4a15e472-a265-4058-b439-7d79c2670817/xls-file-will-not-open-in-excel-2016-only-gray-view?forum=excel

---

---

2nd Method

Now going with the second method (mentioned in the second article link) which uses JavaScript library as a static resource, I can create and download multiple excel sheet in the (.xlsx) format, without any warning, as follows -

async download() {
    let _self = this;
    // When passing `objects` and `schema`.
    await writeXlsxFile(_self.objectsData, {
        schema: _self.schemaObj,
        fileName: 'file.xlsx'
    })

    await writeXlsxFile(_self.objectsData, {
        schema: _self.schemaObj,
        fileName: 'file2.xlsx'
    })
}

But it doesn't support image in any way. Also I can't find a way to create form like structure through this way.

Can you all please help me to this sorted-out. Thanks !!

Answer 1

I generated Excel file in Salesforce using an Aura Component. Main work was done by SheetJS library.

Here's the controller method in Aura that does the main generation work:

    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);  
},

The following method is to convert the JSON response in the appropriate data which in my case was Array of Arrays. You can get more info in the SheetJS documentation

convertResponseToWorksheetData

I have explained in detail this solution in one of my blogs. I can't share the whole code as that will be irrelevant and make things complex.

Source