In one my previous blog I had written about parsing CSV data and that was very well received.

Today let us use the popular SheetJS library to parse data from Excel to JSON/CSV.

Import the library in Visual Builder

Since we need to use a third-party library, we need to import it first.
It can be either a CDN-based import, or we can place it in our resources/js folder.

Configure requirejs paths

At the Application level, open the JSON file and add a new entry for the library as shown.

  "requirejs": {
    "paths": {
      "xlsx": "https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min"
    }
  },

**OR ** (if the library is downloaded and placed under resources/js)

 "requirejs": {
    "paths": {
      "xlsx": "resources/js/xlsx.full.min"
    }
  },

Use it in the application

So far, we have only configured from where we need to load our library. Now we will load and use it. In our page module define block, we need to make an entry for the library

define(['xlsx'], (XLSX) => { ... })

To process the file we need to use JS promise since it's asynchronous.

ParseData(file) {
      return new Promise(function (resolve, reject) {
        let fileReader = new FileReader();
        fileReader.readAsBinaryString(file);
        fileReader.onload = (event) => {
          let data = event.target.result;
          let workbook = XLSX.read(data, { type: "binary" });
          workbook.SheetNames.forEach(sheet => {
            if (sheet == 'Sheet1') {
              let rowObject = XLSX.utils.sheet_to_json(workbook.Sheets[sheet]);
              console.log(rowObject);
              resolve(rowObject);
            }
          });
        };
      });
    }

• In this code snippet we are looking for a sheet named Sheet1. The logic can be modified as required

• sheet_to_json - To convert to JSON

• sheet_to_csv - To convert to CSV

Next, we just have to invoke this function from our action chain by passing in the File Picker's File and consume the response of this function.

The complete application code is GIT here. Just download the ZIP and import it.