Using SheetJS with Lightning Components

Using SheetJS with Lightning Components

SheetJS is a very useful tool and you can access it here for free. (although there is a Pro/Paid version as well)

In this post I will show you how to use the library and how to fix the problems I encountered by creating a very simple component which parses an .xlsx file into JSON Objects and then prints them in the browser’s console. I will assume that you have some basic Salesforce knowledge beforehand, if not, please write in the comments bellow what you find unclear and I will either write a new post about that topic or direct you to a trailhead, if it covers your question.

Let’s start from the beginning. In order to use the SheetJS library, you need to access it somehow. I usually save JS libraries that I am planning to use in code in Static Resources. This is where I encountered my first problem: the lightning locker service encloses each library in a proxy object, so that the library lives in its own context. This means that you cannot simply refer to an XLSX object like any other global variable. In order to fix this, we have to add this line of code:

window.XLSX = XLSX;

in the library after the line:

var XLSX = {};

This lets you reference the XLSX object to a global context. Thank this guy for the solution: Alex Fisher.

https://www.angular-ui-tools.com/full-list-of-components/

Good! Now that the library is fixed, we can create an aura bundle having the following code in the component:

<aura:component implements="flexipage:availableForAllPageTypes" access="global">   
    <ltng:require scripts="{!$Resource.XLSX}"/>   
    <aura:attribute name="FileList" type="Object"/>   
   <lightning:input aura:id="file-input" type="file" files="{!v.FileList}" label="Files" 
                 name="file" multiple="true" onchange="{!c.upload}"/> 
</aura:component>

This is a very simple way of uploading a file. It will look like any upload button from Salesforce. It’s not that important at the moment.

Now that we have a way of uploading files, we have to write some code in the JavaScript controller in order to manipulate the information extracted from the uploaded file (an .xlsx in our case) and print it in the console as JSON Objects (each row will be the equivalent of an Object).

upload: function(component, event, helper) {      
        var file = component.get("v.FileList")[0];
        var reader = new FileReader();       
        reader.onload = function (e) {          
            var binary = "";
            var bytes = new Uint8Array(e.target.result);
            var length = bytes.byteLength;
            for (var i = 0; i < length; i++) {
                binary += String.fromCharCode(bytes[i]);
            }           
            var workbook = XLSX.read(binary, { type: 'binary' }); 
            var sheet_name_list = workbook.SheetNames;
         console.log(XLSX.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]))
        };
        reader.readAsArrayBuffer(file);
}

The .xlsx file I used as example is the one given in this trailhead. They’re just a series of Leads. This is the message the console prints (with 44 JSON Objects):

There are many applications this JS library can have, but the main goal of this post was to show you how you can make it work for Salesforce.

 

More Tools:

For more powerful components that seamlessly integrate with your Angular applications, check out our site and get started with a free trial.

This article is by Vali Coteanu from sfdcbro.com.

Using SheetJS with Lightning Components
Scroll to top