2

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 -

enter image description here

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.

enter image description here

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 !!

6
  • I never tried to do such thing as add an image inside an excel file. Basically the complexity is that images are binary data whereas other data are only text. The library doesn't have the option to add images and internet seems to agree that it is impossible in JS (probably no one has ever tried hard enough also). A workaround I can suggest is to generate a public link to your image (if it is hosted on Salesforce) and then put the link into the file. Apr 4, 2022 at 12:26
  • @Bartheleway thanks for your suggestion. I have already tried to add the image as mentioned in above code. that works. But only with public URL. And in 1st method only, in which I get warning while opening the file. can you help with other requirements of mine ? Apr 4, 2022 at 15:43
  • If it would be me, I would use the 2nd method. If adding the link is good enough for you, then you just have to solve the way to get it. If your audience is having a SF account then it's pretty simple, you just have to generate a link with the id of the file. After login in, they would be able to see the file in Salesforce. If your audience is outside SF, then you need to generate a public link (through creation of a ContentDistribution). For the styling of your excel document, you can check the documentation of the lib (gitlab.com/catamphetamine/write-excel-file#readme). Apr 4, 2022 at 16:32
  • @Bartheleway we do not have any option to add image in 2nd method. If you know, can you please let me know. Apr 5, 2022 at 6:22
  • Have you considered using a document generation tool like Conga? If it meets your requirements, it would be a lot easier to work with. Apr 5, 2022 at 15:47

1 Answer 1

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

3
  • 1
    Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.
    – identigral
    Apr 18, 2023 at 20:51
  • 1
    Noted. I have updated the answer with more information. Apr 19, 2023 at 8:47
  • Thank you, upvoted.
    – identigral
    Apr 19, 2023 at 16:11

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .